Deadlock found when trying to get lock; try restarting transaction

[cesarho]

Hi,

I scheduled a few recurring job to run every hours. This job will trigger around 800 active jobs. here is the pseudo code:

records.each do |record|
  ExtractDataJob.perform_later
end

I encountered the deadlock on the database. The MysQL is purely used for solid queue. MySQL version is 8.4. Is there precaution on using solid queue to prevent deadline?

Type | SolidQueue::Job::EnqueueError
ActiveRecord::Deadlocked: Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction

/usr/local/bundle/ruby/3.3.0/gems/mysql2-0.5.6/lib/mysql2/client.rb:151:in `_query'
/usr/local/bundle/ruby/3.3.0/gems/mysql2-0.5.6/lib/mysql2/client.rb:151:in `block in query'
/usr/local/bundle/ruby/3.3.0/gems/mysql2-0.5.6/lib/mysql2/client.rb:150:in `handle_interrupt'
/usr/local/bundle/ruby/3.3.0/gems/mysql2-0.5.6/lib/mysql2/client.rb:150:in `query'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/mysql2/database_statements.rb:54:in `block in perform_query'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/concurrency/share_lock.rb:186:in `yield_shares'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/dependencies/interlock.rb:41:in `permit_concurrent_loads'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/mysql2/database_statements.rb:53:in `perform_query'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:556:in `block (2 levels) in raw_execute'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:1011:in `block in with_raw_connection'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/concurrency/null_lock.rb:9:in `synchronize'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:983:in `with_raw_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:555:in `block in raw_execute'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/notifications/instrumenter.rb:58:in `instrument'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:1129:in `log'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:554:in `raw_execute'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:591:in `internal_execute'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:547:in `internal_exec_query'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:159:in `exec_insert'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:197:in `insert'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/query_cache.rb:27:in `insert'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:257:in `_insert_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:926:in `block in _create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:412:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_handling.rb:310:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:923:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/counter_cache.rb:201:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/locking/optimistic.rb:84:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/encryption/encryptable_record.rb:184:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/attribute_methods/dirty.rb:240:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/callbacks.rb:445:in `block in _create_record'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:109:in `run_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:912:in `_run_create_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/callbacks.rb:445:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/timestamp.rb:116:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:894:in `create_or_update'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/callbacks.rb:441:in `block in create_or_update'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:109:in `run_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:912:in `_run_save_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/callbacks.rb:441:in `create_or_update'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/timestamp.rb:127:in `create_or_update'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:424:in `save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/validations.rb:54:in `save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:366:in `block in save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:418:in `block (2 levels) in with_transaction_returning_status'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:357:in `transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:414:in `block in with_transaction_returning_status'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:412:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_handling.rb:310:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:410:in `with_transaction_returning_status'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:366:in `save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/suppressor.rb:56:in `save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:55:in `create!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation.rb:1362:in `_create!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation.rb:174:in `block in create!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation.rb:1373:in `_scoping'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation.rb:548:in `scoping'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation.rb:174:in `create!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation.rb:290:in `block (2 levels) in create_or_find_by!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:626:in `block in within_new_transaction'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/concurrency/null_lock.rb:9:in `synchronize'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:623:in `within_new_transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:367:in `within_new_transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:359:in `transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:234:in `block in transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:412:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_handling.rb:310:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:233:in `transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation/delegation.rb:106:in `transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation.rb:290:in `block in create_or_find_by!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:412:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_handling.rb:310:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation/delegation.rb:106:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/relation.rb:289:in `create_or_find_by!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/querying.rb:24:in `create_or_find_by!'
/usr/local/bundle/ruby/3.3.0/gems/solid_queue-1.1.3/app/models/solid_queue/job/executable.rb:104:in `ready'
/usr/local/bundle/ruby/3.3.0/gems/solid_queue-1.1.3/app/models/solid_queue/job/executable.rb:68:in `dispatch'
/usr/local/bundle/ruby/3.3.0/gems/solid_queue-1.1.3/app/models/solid_queue/job/executable.rb:61:in `prepare_for_execution'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:361:in `block in make_lambda'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:207:in `call'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:562:in `block in invoke_after'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:562:in `each'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:562:in `invoke_after'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:110:in `run_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:912:in `_run_create_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/callbacks.rb:445:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/timestamp.rb:116:in `_create_record'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:894:in `create_or_update'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/callbacks.rb:441:in `block in create_or_update'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:100:in `run_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:912:in `_run_save_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/callbacks.rb:441:in `create_or_update'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/timestamp.rb:127:in `create_or_update'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:424:in `save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/validations.rb:54:in `save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:366:in `block in save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:418:in `block (2 levels) in with_transaction_returning_status'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:626:in `block in within_new_transaction'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/concurrency/null_lock.rb:9:in `synchronize'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/transaction.rb:623:in `within_new_transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:367:in `within_new_transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:359:in `transaction'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:414:in `block in with_transaction_returning_status'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:412:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/connection_handling.rb:310:in `with_connection'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:410:in `with_transaction_returning_status'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/transactions.rb:366:in `save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/suppressor.rb:56:in `save!'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/persistence.rb:55:in `create!'
/usr/local/bundle/ruby/3.3.0/gems/solid_queue-1.1.3/app/models/solid_queue/job.rb:41:in `create_from_active_job'
/usr/local/bundle/ruby/3.3.0/gems/solid_queue-1.1.3/app/models/solid_queue/job.rb:31:in `enqueue'
/usr/local/bundle/ruby/3.3.0/gems/solid_queue-1.1.3/lib/active_job/queue_adapters/solid_queue_adapter.rb:16:in `enqueue'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/enqueuing.rb:132:in `raw_enqueue'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/enqueue_after_transaction_commit.rb:40:in `raw_enqueue'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/enqueuing.rb:117:in `block in enqueue'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:120:in `block in run_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/instrumentation.rb:40:in `block in instrument'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/notifications.rb:210:in `block in instrument'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/notifications/instrumenter.rb:58:in `instrument'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/notifications.rb:210:in `instrument'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/instrumentation.rb:39:in `instrument'
/usr/local/bundle/ruby/3.3.0/gems/activerecord-8.0.1/lib/active_record/railties/job_runtime.rb:18:in `instrument'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/instrumentation.rb:21:in `block (2 levels) in <module:Instrumentation>'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:129:in `instance_exec'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:129:in `block in run_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/tagged_logging.rb:143:in `block in tagged'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/tagged_logging.rb:38:in `tagged'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/tagged_logging.rb:143:in `tagged'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/broadcast_logger.rb:241:in `method_missing'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/logging.rb:39:in `tag_logger'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/logging.rb:28:in `block (2 levels) in <module:Logging>'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:129:in `instance_exec'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:129:in `block in run_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activesupport-8.0.1/lib/active_support/callbacks.rb:140:in `run_callbacks'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/enqueuing.rb:116:in `enqueue'
/usr/local/bundle/ruby/3.3.0/gems/activejob-8.0.1/lib/active_job/enqueuing.rb:83:in `perform_later'`

[rosa]

Hey @cesarho, sorry about that! Could you share the LATEST DETECTED DEADLOCK section if you run SHOW ENGINE INNODB STATUS in your MySQL server?

[cesarho]

@rosa Really appreciated your help and creating solid queue. We are migrating the workload from Lambda to SolidQueue.

To give you more context, we have set 3 scheduler jobs at 30 min interval.

• Each SchedulerJob triggers around 800 CalcForShopJobs in total
• Each CalcForShopJob triggers around 1 - 5 CalcForLocationJobs

Here is the log I extract this morning.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-03-11 03:29:16 130136580032064
*** (1) TRANSACTION:
TRANSACTION 15555905, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 3
MySQL thread id 71, OS thread handle 130136353502784, query id 47494 5.78.133.65 root update
INSERT INTO `solid_queue_ready_executions` (`job_id`, `queue_name`, `priority`, `created_at`) VALUES (689205, 'calc_queue', 0, '2025-03-11 03:29:16.891611')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 19 page no 4 n bits 112 index PRIMARY of table `ci_production_queue`.`solid_queue_ready_executions` trx id 15555905 lock_mode X locks rec but not gap
Record lock, heap no 29 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 80000000000a9c2a; asc        *;;
 1: len 6; hex 000000ed5d41; asc     ]A;;
 2: len 7; hex 81000001030132; asc       2;;
 3: len 8; hex 80000000000a8435; asc        5;;
 4: len 11; hex 63616c635f62756e646c65; asc calc_queue;;
 5: len 4; hex 80000000; asc     ;;
 6: len 8; hex 99b61637500d9adb; asc    7P   ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 6 n bits 112 index index_solid_queue_poll_all of table `ci_production_queue`.`solid_queue_ready_executions` trx id 15555905 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 15555931, ACTIVE 0 sec
mysql tables in use 1, locked 1
LOCK WAIT 18 lock struct(s), heap size 1128, 72 row lock(s), undo log entries 24
MySQL thread id 124, OS thread handle 130136229770816, query id 47551 5.78.133.65 root updating
DELETE FROM `solid_queue_ready_executions` WHERE `solid_queue_ready_executions`.`id` IN (695327, 695316, 695323, 695330, 695326, 695324, 695328, 695329, 695331, 695332, 695334, 695333)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 19 page no 6 n bits 112 index index_solid_queue_poll_all of table `ci_production_queue`.`solid_queue_ready_executions` trx id 15555931 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a842a; asc        *;;
 2: len 8; hex 80000000000a9c1c; asc         ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a841b; asc         ;;
 2: len 8; hex 80000000000a9c14; asc         ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a842e; asc        .;;
 2: len 8; hex 80000000000a9c23; asc        #;;

Record lock, heap no 15 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a842d; asc        -;;
 2: len 8; hex 80000000000a9c21; asc        !;;

Record lock, heap no 21 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a842c; asc        ,;;
 2: len 8; hex 80000000000a9c20; asc         ;;

Record lock, heap no 25 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a8427; asc        ';;
 2: len 8; hex 80000000000a9c1b; asc         ;;

Record lock, heap no 30 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a8429; asc        );;
 2: len 8; hex 80000000000a9c1e; asc         ;;

Record lock, heap no 31 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a8339; asc        9;;
 2: len 8; hex 80000000000a9c15; asc         ;;

Record lock, heap no 32 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a8406; asc         ;;
 2: len 8; hex 80000000000a9bfe; asc         ;;

Record lock, heap no 33 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a8430; asc        0;;
 2: len 8; hex 80000000000a9c24; asc        $;;

Record lock, heap no 34 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a83d4; asc         ;;
 2: len 8; hex 80000000000a9c1f; asc         ;;

Record lock, heap no 35 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a8428; asc        (;;
 2: len 8; hex 80000000000a9c22; asc        ";;

Record lock, heap no 36 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a8432; asc        2;;
 2: len 8; hex 80000000000a9c25; asc        %;;

Record lock, heap no 37 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000000; asc     ;;
 1: len 8; hex 80000000000a8431; asc        1;;
 2: len 8; hex 80000000000a9c26; asc        &;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 4 n bits 112 index PRIMARY of table `ci_production_queue`.`solid_queue_ready_executions` trx id 15555931 lock_mode X waiting
Record lock, heap no 29 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 80000000000a9c2a; asc        *;;
 1: len 6; hex 000000ed5d41; asc     ]A;;
 2: len 7; hex 81000001030132; asc       2;;
 3: len 8; hex 80000000000a8435; asc        5;;
 4: len 11; hex 63616c635f62756e646c65; asc calc_queue;;
 5: len 4; hex 80000000; asc     ;;
 6: len 8; hex 99b61637500d9adb; asc    7P   ;;

*** WE ROLL BACK TRANSACTION (1)

case 2

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-03-11 04:01:59 130136580032064
*** (1) TRANSACTION:
TRANSACTION 16142751, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 331, OS thread handle 130136176932416, query id 2288767 5.78.133.65 root update
INSERT INTO `solid_queue_semaphores` (`key`, `value`, `expires_at`, `created_at`, `updated_at`) VALUES ('CalcForShopJob/a000001', 0, '2025-03-11 04:02:59.046805', '2025-03-11 04:01:59.046926', '2025-03-11 04:01:59.046926')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 15 page no 160 n bits 200 index index_solid_queue_semaphores_on_key of table `ci_production_queue`.`solid_queue_semaphores` trx id 16142751 lock mode S locks gap before rec
Record lock, heap no 117 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 43616c6342756e646c654d6173746572466f7253686f704a6f622f6d6178; asc CalcForShopJob/max; (total 52 bytes);
 1: len 8; hex 8000000000088565; asc        e;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 160 n bits 200 index index_solid_queue_semaphores_on_key of table `ci_production_queue`.`solid_queue_semaphores` trx id 16142751 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 117 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 43616c6342756e646c654d6173746572466f7253686f704a6f622f6d6178; asc CalcForShopJob/max; (total 52 bytes);
 1: len 8; hex 8000000000088565; asc        e;;


*** (2) TRANSACTION:
TRANSACTION 16142752, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 409, OS thread handle 130136356673088, query id 2288785 5.78.133.65 root update
INSERT INTO `solid_queue_semaphores` (`key`, `value`, `expires_at`, `created_at`, `updated_at`) VALUES ('CalcForShopJob/a000001', 0, '2025-03-11 04:02:59.051472', '2025-03-11 04:01:59.051817', '2025-03-11 04:01:59.051817')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15 page no 160 n bits 200 index index_solid_queue_semaphores_on_key of table `ci_production_queue`.`solid_queue_semaphores` trx id 16142752 lock mode S locks gap before rec
Record lock, heap no 117 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 43616c6342756e646c654d6173746572466f7253686f704a6f622f6d6178; asc CalcForShopJob/max; (total 52 bytes);
 1: len 8; hex 8000000000088565; asc        e;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15 page no 160 n bits 200 index index_solid_queue_semaphores_on_key of table `ci_production_queue`.`solid_queue_semaphores` trx id 16142752 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 117 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 43616c6342756e646c654d6173746572466f7253686f704a6f622f6d6178; asc CalcForShopJob/max; (total 52 bytes);
 1: len 8; hex 8000000000088565; asc        e;;

*** WE ROLL BACK TRANSACTION (2)

[iJackUA]

I think I have faced similar issues and resolved it by using ActiveJob.perform_all_later
#162 (comment)
Also using it with MySQL and Deadlocks still occasionally happens

[cesarho]

Thanks @iJackUA

I also encountered deadlock when using ActiveJob.perform_all_later.. Then, I switch back to perform_later. The deadlocks occasionally happened as well.

[rosa]

Hey @cesarho, sorry for the delay in replying. Could you let me know your queue.yml configuration? I'm interested mostly in the threads value. I wonder if it'd be possible to tweak that to reduce the number of deadlocks. I can see the DELETE query of transaction 2 is trying to delete quite a few records at once, which suggests a high threads value in one of your queues.

DELETE FROM `solid_queue_ready_executions` WHERE `solid_queue_ready_executions`.`id` IN (695327, 695316, 695323, 695330, 695326, 695324, 695328, 695329, 695331, 695332, 695334, 695333)

[rosa]

Also, could you let me know the concurrency limits configuration for CalcForShopJob?

[cesarho]

@rosa No worry. Thanks for getting back. Is there any hints how should the worker thread and job currency setup on using solid queue?

JOB_CONCURRENCY: 20
WORKER_THREADS: 20

default: &default
  dispatchers:
    - polling_interval: 1
      batch_size: 500
      concurrency_maintenance_interval: 60
  workers:
    - queues: "*"
      threads: <%= ENV.fetch("WORKER_THREADS", 3) %>
      processes: <%= ENV.fetch("JOB_CONCURRENCY", 1) %>
      polling_interval: 0.1

The concurrency limit setting for CalcForShopJob

  limits_concurrency to: 1, key: ->(shopify_domain, *) { shopify_domain }, duration: 1.minutes