Skip to content

added suggestion for deadlock fix for rapid updater

Jorn-Are Klubben Flaten requested to merge deadlock_fix_PriorityRapidUpdater into master

ref issues such as


------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-02-26 07:57:12 140603979961920
*** (1) TRANSACTION:
TRANSACTION 249686741924, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 5 row lock(s), undo log entries 4
MySQL thread id 165320, OS thread handle 140616760268352, query id 485592648 pcapiserv02.cern.ch 128.142.249.46 root update
INSERT INTO PRIORITY (userId, waiting, running, totalRunningTimeLast24h, totalCpuCostLast24h) VALUES (1234578, -11, 6, 198054, 6114770.0), (1235475, 0, 0, 30871, 30959.0), (1234576, -6, 72, 226974, 3370950.0), (1235889, -5, -39, 190875, 6.8871934E7), (974855, -13, 116, 1290194, 1862053.0), (1236152, 0, -2, 0, 0.0), (1235974, 0, -1, 0, 0.0) ON DUPLICATE KEY UPDATE waiting = GREATEST(waiting + VALUES(waiting), 0), running = GREATEST(running + VALUES(running), 0), totalRunningTimeLast24h = totalRunningTimeLast24h + VALUES(totalRunningTimeLast24h), totalCpuCostLast24h = totalCpuCostLast24h + VALUES(totalCpuCostLast24h)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 60398 page no 23 n bits 152 index PRIMARY of table `processesdev`.`PRIORITY` trx id 249686741924 lock_mode X locks rec but not gap
Record lock, heap no 16 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
 0: len 4; hex 8012d690; asc     ;;
 1: len 6; hex 003a227d53a4; asc  :"}S ;;
 2: len 7; hex 01000011ef07cc; asc        ;;
 3: len 4; hex 00606a46; asc  `jF;;
 4: len 4; hex 8000ea60; asc    `;;
 5: len 4; hex 80002bc8; asc   + ;;
 6: len 4; hex a2208b3c; asc    <;;
 7: len 4; hex 8000000a; asc     ;;
 8: len 8; hex 80005af3107a4000; asc   Z  z@ ;;
 9: len 4; hex 800f4240; asc   B@;;
 10: len 4; hex 5741f344; asc WA D;;
 11: len 4; hex 21e6b556; asc !  V;;
 12: len 8; hex 800000006a8c0fd3; asc     j   ;;
 13: len 4; hex 80000000; asc     ;;
 14: len 4; hex 801e71ca; asc   q ;;
 15: len 4; hex 27074d50; asc ' MP;;

Record lock, heap no 18 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
 0: len 4; hex 8012d692; asc     ;;
 1: len 6; hex 003a227d53a4; asc  :"}S ;;
 2: len 7; hex 01000011ef076c; asc       l;;
 3: len 4; hex 00803b46; asc   ;F;;
 4: len 4; hex 8000c350; asc    P;;
 5: len 4; hex 800058d4; asc   X ;;
 6: len 4; hex dffd313e; asc   1>;;
 7: len 4; hex 800003e8; asc     ;;
 8: len 8; hex 80005af3107a4000; asc   Z  z@ ;;
 9: len 4; hex 800f4240; asc   B@;;
 10: len 4; hex d225fc42; asc  % B;;
 11: len 4; hex 21e6b556; asc !  V;;
 12: len 8; hex 800000003bf5e2be; asc     ;   ;;
 13: len 4; hex 80000000; asc     ;;
 14: len 4; hex 8021900e; asc  !  ;;
 15: len 4; hex 01e75a53; asc   ZS;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 60398 page no 23 n bits 152 index PRIMARY of table `processesdev`.`PRIORITY` trx id 249686741924 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
 0: len 4; hex 800ee007; asc     ;;
 1: len 6; hex 003a227d5391; asc  :"}S ;;
 2: len 7; hex 02000004852a12; asc      * ;;
 3: len 4; hex 0000803f; asc    ?;;
 4: len 4; hex 80030d40; asc    @;;
 5: len 4; hex 80000380; asc     ;;
 6: len 4; hex 73baac3d; asc s  =;;
 7: len 4; hex 800004b0; asc     ;;
 8: len 8; hex 80005af3107a4000; asc   Z  z@ ;;
 9: len 4; hex 800f4240; asc   B@;;
 10: len 4; hex bf11ba3b; asc    ;;;
 11: len 4; hex 21e6b556; asc !  V;;
 12: len 8; hex 80000001bdd9d24e; asc        N;;
 13: len 4; hex 80000000; asc     ;;
 14: len 4; hex 803bfbdb; asc  ;  ;;
 15: len 4; hex 411a3c50; asc A <P;;

Merge request reports