Deadlock
A couple of days ago this deadlock happened on our MySql 5.7 database:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-09-25 11:03:39 0x14b52a06b700
*** (1) TRANSACTION:
TRANSACTION 7342267151, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 23580984, OS thread handle 22769182357248, query id 422061910 10.11.1.43 multidialogo2 updating
UPDATE `multiqueue` SET `postal_orders_generation_status` = 'DONE' WHERE uuid = 'ff9c409d-7b11-55a2-a37f-a278d3b7a75b'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5386 page no 877543 n bits 280 index uuid_uq of table `mdnetb_main`.`multiqueue` trx id 7342267151 lock_mode X locks rec but not gap waiting
Record lock, heap no 214 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 66663963343039642d376231312d353561322d613337662d613237386433; asc ff9c409d-7b11-55a2-a37f-a278d3; (total 36 bytes);
1: len 4; hex 0012d0f0; asc ;;
*** (2) TRANSACTION:
TRANSACTION 7342267150, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 23580988, OS thread handle 22768326719232, query id 422061911 10.11.1.123 multidialogo2 updating
UPDATE multiqueue SET `acknowledged_at` = '2024-09-25 13:03:39', status = 'ACKNOWLEDGED' , status_message = 'Queue has ben acknowledged by dispatch service' , debug_last_modifier_class = 'Netbuilder\\Console\\Service\\Storage\\Queue\\QueueReportStorage' WHERE uuid = 'ff9c409d-7b11-55a2-a37f-a278d3b7a75b'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5386 page no 877543 n bits 280 index uuid_uq of table `mdnetb_main`.`multiqueue` trx id 7342267150 lock mode S locks rec but not gap
Record lock, heap no 214 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 66663963343039642d376231312d353561322d613337662d613237386433; asc ff9c409d-7b11-55a2-a37f-a278d3; (total 36 bytes);
1: len 4; hex 0012d0f0; asc ;;
The cause of the deadlock seems (to me) this:
there are two PHP services that have issued two different UPDATE statements on the same table line *at the same instant*.
I have two questions, though:
1. the two statements are two UPDATE very similar, the lock mode should be X for both of them. Why the second one has the mode S ?
2. the deadlock is caused by the simultaneity? What could be the best strategy to handle this risk? Adding a timeout the the UPDATE, in order to attempt again after the other one (hopefully) succeeds?
Thanks in advance
Nicola