MySQL 死锁日志解析：案例分析及解决方法

*** (1) TRANSACTION: TRANSACTION 10024296578, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id 4014781, OS thread handle 140162772596480, query id 1879613808 192.168.50.221 root Searching rows for update update shipment_header sh set processStamp = null where sh.leadingSts = 600 and sh.trailingSts=600 and sh.warehouseCode='ILC' and lastUpdated < date_sub(NOW(),interval 10 MINUTE ) and processStamp is not null limit 500 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 5795 page no 316314 n bits 80 index PRIMARY of table itoc-wms-prod.shipment_header trx id 10024296578 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 182; compact format; info bits 0

*** (2) TRANSACTION: TRANSACTION 10024296579, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 4014778, OS thread handle 140163171251968, query id 1879613809 192.168.50.221 root updating UPDATE shipment_header SET leadingSts = 610,trailingSts = 610,processType='NORMAL',webRefundCreated = NOW(),processStamp = NULL WHERE id = 66575492内容：*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 5795 page no 316314 n bits 80 index PRIMARY of table itoc-wms-prod.shipment_header trx id 10024296579 lock_mode X locks rec but not gap Record lock, heap no 9 PHYSICAL RECORD: n_fields 182; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 5795 page no 316314 n bits 80 index PRIMARY of table itoc-wms-prod.shipment_header trx id 10024296579 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 182; compact format; info bits 0

在这段 MySQL 死锁日志中，我们可以看到有两个事务，事务 ID 分别为 10024296578 和 10024296579。事务 1 试图更新 shipment_header 表中的行，并等待对特定记录授予锁。事务 2 已经持有该记录的锁，并试图更新它，但它正在等待对不同记录授予锁。

这会导致死锁，其中两个事务都在等待对方释放锁。在这种情况下，解决死锁的唯一方法是回滚其中一个事务，从而允许另一个事务继续并完成其操作。