*** (1) TRANSACTION:
TRANSACTION 5223, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 172, OS thread handle 281471652687808, query id 11099 192.168.0.5 root update
INSERT INTO `solid_queue_ready_executions` (`job_id`, `queue_name`, `priority`, `created_at`) VALUES (469, 'default', 0, '2024-05-21 01:15:11.201125')
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 4 n bits 264 index PRIMARY of table `handson`.`solid_queue_ready_executions` trx id 5223 lock_mode X locks rec but not gap
Record lock, heap no 144 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 6 n bits 264 index index_solid_queue_poll_all of table `handson`.`solid_queue_ready_executions` trx id 5223 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 5227, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1128, 23 row lock(s), undo log entries 10
MySQL thread id 177, OS thread handle 281471649517504, query id 11103 192.168.0.4 root updating
DELETE FROM `solid_queue_ready_executions` WHERE `solid_queue_ready_executions`.`job_id` IN (464, 465, 466, 467, 468)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 6 n bits 264 index index_solid_queue_poll_all of table `handson`.`solid_queue_ready_executions` trx id 5227 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 4 n bits 264 index PRIMARY of table `handson`.`solid_queue_ready_executions` trx id 5227 lock_mode X waiting
Record lock, heap no 144 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
This is another take on https://github.com/rails/solid_queue/pull/229, that tries to solve a deadlock like this: