Deadlocks when deleting from scheduled_executions

[pauline-koch]

Hi,

I'm still working on stabilizing my solidqueue setup. I read in the documentation that you can run multiple bin/jobs processes, so I'm doing that in our k8s cluster (3 to be exact).

We are occassionally getting Deadlocks like this:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-07-23 05:00:03 70389264804208
*** (1) TRANSACTION:
TRANSACTION 618839701, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 76 lock struct(s), heap size 8312, 1381 row lock(s), undo log entries 345
MySQL thread id 4527715, OS thread handle 70369888717168, query id 848907711 10.192.103.128 user_app updating
DELETE FROM solid_queue_scheduled_executions WHERE solid_queue_scheduled_executions.id IN (77074, 77077, 77080, 77081, 77084, 77085, 77088, 77089, 77092, 77095, 77098, 77101, 77104, 77107, 77110, 77111, 77116, 77121, 77124, 77125, 77126, 77127, 77130, 77133, 77136, 77137, 77142, 77144, 77148, 77151, 77152, 77157, 77158, 77159, 77164, 77165, 77168, 77169, 77172, 77177, 77178, 77179, 77180, 77181, 77184, 77185, 77186, 77187, 77188, 77189, 77190, 77191, 77192, 77193, 77194, 77195, 77196, 77197, 77198, 77199, 77200, 77201, 77202, 77203, 77204, 77205, 77206, 77207, 77208, 77209, 77210, 77211, 77212, 77213, 77214, 77215, 77216, 77217, 77218, 77221, 77223, 77225, 77226, 77227, 77228, 77229, 77230, 77231, 77232, 77233, 77234, 77235, 77236, 77237, 77238, 77239, 77240, 77241, 77242, 77243, 77244, 77245, 77246, 77247, 77248, 77249, 77250, 77251, 77252, 77253, 77254, 77255, 77256, 77257, 77258, 77259, 77260, 77261, 77262, 77263, 77264, 77265, 77266, 77267, 77268, 77269, 77270, 77271, 77272, 77273, 77274, 77275, 77276, 77277, 77278, 77279, 77280, 77281, 77282, 77283, 77284, 77285, 77286, 77287, 77288, 77289, 77290, 77291, 77292, 77293, 77294, 77295, 77296, 77297, 77298, 77299, 77300, 77301, 77302, 77303, 77304, 77305, 77306, 77307, 77308, 77309, 77310, 77311, 77312, 77313, 77314, 77315, 77316, 77317, 77318, 77319, 77320, 77321, 77322, 77323, 77324, 77325, 77326, 77327, 77328, 77329, 77330, 77331, 77332, 77333, 77334, 77335, 77336, 77337, 77338, 77339, 77340, 77341, 77342, 77343, 77344, 77345, 77346, 77347, 77348, 77349, 77350, 77351, 77352, 77353, 77354, 77355, 77356, 77357, 77358, 77359, 77360, 77361, 77362, 77363, 77364, 77365, 77366, 77367, 77368, 77369, 77370, 77371, 77372, 77373, 77374, 77375, 77376, 77377, 77378, 77379, 77380, 77381, 77382, 77383, 77384, 77385, 77386, 77387, 77388, 77389, 77390, 77391, 77392, 77393, 77394, 77395, 77396, 77397, 77398, 77399, 77400, 77401, 77402, 77403, 77404, 77405, 77406, 77407, 77408, 77409, 77410, 77411, 77412, 77413, 77414, 77415, 77416, 77417, 77418, 77419, 77420, 77421, 77422, 77423, 77424, 77425, 77426, 77427, 77428, 77429, 77430, 77431, 77432, 77433, 77434, 77435, 77436, 77437, 77438, 77439, 77440, 77441, 77442, 77443, 77444, 77445, 77446, 77447, 77448, 77449, 77450, 77451, 77452, 77453, 77454, 77455, 77456, 77457, 77458, 77459, 77460, 77461, 77462, 77463, 77464, 77465, 77466, 77467, 77468, 77469, 77470, 77471, 77472, 77473, 77474, 77475, 77476, 77477, 77478, 77479, 77480, 77481, 77482, 77483, 77484, 77485, 77486, 77487, 77488) /*application='DexMfv'*/

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 4001 page no 19 n bits 328 index PRIMARY of table rds_prod_01_queue.solid_queue_scheduled_executions trx id 618839701 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 8; compact format; info bits 0


*** (2) TRANSACTION:
TRANSACTION 618839682, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 216 lock struct(s), heap size 24696, 2001 row lock(s), undo log entries 1000
MySQL thread id 4527186, OS thread handle 70369855625584, query id 848907765 10.192.99.144 user_app updating
DELETE FROM solid_queue_scheduled_executions WHERE solid_queue_scheduled_executions.id IN (75992, 75997, 75998, 75999, 76000, 76004, 76005, 76006, 76012, 76019, 76022, 76023, 76024, 76025, 76029, 76032, 76035, 76038, 76041, 76042, 76044, 76045, 76047, 76048, 76049, 76050, 76051, 76052, 76053, 76054, 76055, 76056, 76059, 76062, 76063, 76066, 76067, 76069, 76072, 76079, 76080, 76085, 76086, 76089, 76092, 76098, 76101, 76102, 76105, 76108, 76111, 76112, 76113, 76118, 76119, 76122, 76126, 76129, 76130, 76131, 76134, 76135, 76138, 76141, 76142, 76143, 76144, 76148, 76150, 76152, 76157, 76158, 76161, 76162, 76165, 76166, 76169, 76170, 76173, 76174, 76177, 76180, 76181, 76184, 76185, 76186, 76189, 76190, 76192, 76195, 76196, 76199, 76200, 76201, 76202, 76205, 76206, 76211, 76212, 76215, 76216, 76217, 76220, 76221, 76224, 76225, 76226, 76229, 76230, 76235, 76236, 76237, 76240, 76243, 76244, 76247, 76248, 76251, 76252, 76255, 76256, 76259, 76260, 76261, 76264, 76267, 76268, 76271, 76273, 76274, 76275, 76278, 76281, 76282, 76285, 76288, 76289, 76292, 76293, 76294, 76297, 76298, 76299, 76303, 76306, 76307, 76308, 76311, 76312, 76313, 76316, 76319, 76320, 76323, 76324, 76325, 76330, 76331, 76334, 76335, 76337, 76338, 76339, 76340, 76343, 76344, 76345, 76346, 76347, 76348, 76351, 76354, 76355, 76356, 76357, 76358, 76359, 76362, 76364, 76365, 76368, 76369, 76370, 76371, 76372, 76373, 76376, 76379, 76380, 76381, 76382, 76385, 76386, 76389, 76392, 76393, 76394, 76399, 76402, 76403, 76404, 76411, 76412, 76415, 76416, 76417, 76418, 76421, 76423, 76424, 76425, 76426, 76427, 76428, 76429, 76430, 76433, 76438, 76439, 76440, 76441, 76444, 76445, 76446, 76447, 76448, 76459, 76460, 76466, 76467, 76470, 76473, 76474, 76475, 76478, 76479, 76485, 76486, 76488, 76490, 76491, 76494, 76497, 76499, 76502, 76503, 76504, 76505, 76508, 76509, 76511, 76514, 76517, 76518, 76521, 76522, 76523, 76526, 76527, 76528, 76531, 76534, 76535, 76536, 76537, 76540, 76541, 76542, 76547, 76550, 76551, 76552, 76554, 76555, 76556, 76559, 76560, 76565, 76566, 76569, 76570, 76573, 76576, 76577, 76578, 76580, 76583, 76586, 76590, 76591, 76592, 76593, 76594, 76596, 76601, 76602, 76605, 76608, 76607, 76611, 76612, 76616, 76617, 76619, 76621, 76624, 76625, 76626, 76629, 76631, 76632, 76635, 76640, 76642, 76643, 76646, 76647, 76648, 76651, 76654, 76655, 76660, 76661, 76662, 76663, 76666, 76668, 76669, 76672, 76673, 76676, 76677, 76678, 76681, 76686, 76687, 76688, 76691, 76694, 76695, 76698, 76699, 76700, 76703, 76706, 76707, 76708, 76711, 76712, 76715, 76716, 76717, 76718, 76722, 76723, 76728, 76731, 76734, 76737, 76738, 76741, 76742, 76743, 76744, 76745, 76746, 76749, 76752, 76753, 76756, 76757, 76758, 76761, 76762, 76763, 76766, 76767, 76772, 76773, 76776, 76777, 76779, 76781, 76782, 76783, 76784, 76785, 76788, 76791, 76792, 76797, 76800, 76801, 76802, 76805, 76806, 76809, 76810, 76811, 76814, 76817, 76818, 76821, 76822, 76824, 76825, 76828, 76829, 76830, 76833, 76834, 76835, 76838, 76841, 76844

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4001 page no 7 n bits 280 index PRIMARY of table rds_prod_01_queue.solid_queue_scheduled_executions trx id 618839682 lock_mode X locks rec but not gap
Record lock, heap no 42 PHYSICAL RECORD: n_fields 8; compact format; info bits 32

How can I reduce these deadlocks in my setup? Reduce the batch size of the dispatcher?

[rosa]

Hey @pauline-koch! Could you share some more details about your setup? How many dispatchers are you running in total?

[pauline-koch]

Hi @rosa , thank you for your quick response. I'm running 3 dispatchers I think because all 3 pods use this config:

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

development:
  <<: *default

stage:
  <<: *default

preview:
  <<: *default

test:
  <<: *default

production:
  <<: *default

SQ_THREAD_CONCURRENCY is 20 in production.

[rosa]

Thanks @pauline-koch, and sorry for the delay! 3 pods with that is quite reasonable, and also 1 second of polling interval... I'm surprised you get deadlocks 🤔 But in any case, could you try:

• Running a single dispatcher. For that, you'd need to use a different configuration for 2 of the pods, without the dispatchers section.
• Continue running the 3 dispatchers, but with a smaller batch size, for example, 150 to start.

[pauline-koch]

Hi @rosa, Thank you for the advice! I changed the batch size to 100 yesterday, and that did not help the problem 😁 Today I had more deadlocks then yesterday. I will try running one dispatcher next.