Avoid deadlock when creating ready execution

[jk-es335]

I got deadlock when running multiple workers/dispatchers with docker compose.

• ruby 3.2.4
• mysql 8.0.31
• solid_queue 0.3.1

I found @rosa PR and fixed it using a similar approach. It does not completely eliminate deadlock but mitigates deadlock I think.

The deadlock error is following:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-05-21 01:15:11 281472305999808
*** (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
 0: len 8; hex 80000000000001d5; asc         ;;
 1: len 6; hex 000000001467; asc      g;;
 2: len 7; hex 810000010e0121; asc       !;;
 3: len 8; hex 80000000000001d5; asc         ;;
 4: len 7; hex 64656661756c74; asc default;;
 5: len 4; hex 80000000; asc     ;;
 6: len 8; hex 99b36a13cb0311a5; asc   j     ;;

*** (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
 0: len 8; hex 73757072656d756d; asc supremum;;

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

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

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

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

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

*** (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
 0: len 8; hex 80000000000001d5; asc         ;;
 1: len 6; hex 000000001467; asc      g;;
 2: len 7; hex 810000010e0121; asc       !;;
 3: len 8; hex 80000000000001d5; asc         ;;
 4: len 7; hex 64656661756c74; asc default;;
 5: len 4; hex 80000000; asc     ;;
 6: len 8; hex 99b36a13cb0311a5; asc   j    

[rosa]

Thanks @jk-es335, and sorry for the delay! Looking into this one now.

[rosa]

Done in https://github.com/rails/solid_queue/pull/240, so closing this one. Thank you so much, @jk-es335!