数据库死锁排查案例

现象：飞机在上线时，将负载相机存入库中时发生死锁。插入相关逻辑为删除后再插入。

排查思路：

1. 查看是由什么锁冲突而产生的死锁；

2. 更改代码逻辑或者优化数据库字段。

解决：

执行SQL指令

SHOW ENGINE INNODB STATUS；

死锁日志

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 252650, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 5117, OS thread handle 140104994612992, query id 18461531 192.168.6.1 root updating
DELETE FROM manage_device_payload 
 
 WHERE ((payload_sn = '1581F5BLD232A0015B4K-0'))

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 254 page no 5 n bits 80 index payload_sn_UNIQUE of table `cloud_sample`.`manage_device_payload` trx id 252650 lock_mode X waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 22; hex 313538314635424c44323332413030313542344b2d30; asc 1581F5BLD232A0015B4K-0;;
 1: len 4; hex 0000040f; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 254 page no 5 n bits 80 index payload_sn_UNIQUE of table `cloud_sample`.`manage_device_payload` trx id 252650 lock_mode X waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 22; hex 313538314635424c44323332413030313542344b2d30; asc 1581F5BLD232A0015B4K-0;;
 1: len 4; hex 0000040f; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 252648, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 2
MySQL thread id 5114, OS thread handle 140104991442688, query id 18461533 192.168.6.1 root update
INSERT INTO manage_device_payload  ( payload_sn,
payload_name,
payload_type,
sub_type,

payload_index,
device_sn,
create_time,
update_time,

control_source )  VALUES  ( '',
'M30 Camera',
52,
0,

0,
'1581F5BLD232A0015B4K',
1732127410877,
1732127410877,

'A' )

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 254 page no 5 n bits 80 index payload_sn_UNIQUE of table `cloud_sample`.`manage_device_payload` trx id 252648 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 22; hex 313538314635424c44323332413030313542344b2d30; asc 1581F5BLD232A0015B4K-0;;
 1: len 4; hex 0000040f; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 254 page no 5 n bits 80 index payload_sn_UNIQUE of table `cloud_sample`.`manage_device_payload` trx id 252648 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 22; hex 313538314635424c44323332413030313542344b2d30; asc 1581F5BLD232A0015B4K-0;;
 1: len 4; hex 0000040f; asc     ;;

可以分析出具体信息：

死锁概述
时间：2024-11-21 02:30:10
事务1：ID 252650，执行DELETE操作，持有1个锁，等待1个锁。
事务2：ID 252648，执行INSERT操作，持有1个锁，等待1个锁。

事务1（DELETE操作）
状态：ACTIVE 0秒，起始索引读取。
锁等待：等待payload_sn_UNIQUE索引上的记录锁（X锁）。
操作：从manage_device_payload表中删除payload_sn为'1581F5BLD232A0015B4K-0'的记录。
持有的锁：payload_sn_UNIQUE索引上的记录锁（X锁）。

事务2（INSERT操作）
状态：ACTIVE 0秒，正在插入。
锁等待：等待payload_sn_UNIQUE索引上的记录锁（X锁）。
操作：向manage_device_payload表插入一条新记录。
持有的锁：payload_sn_UNIQUE索引上的记录锁（X锁），但不是同一个记录，因为插入操作需要在记录之前的位置（间隙）上等待。

死锁分析
事务1尝试删除一条记录，已经获得了该记录的X锁。
事务2尝试插入一条新记录，需要在相同的payload_sn值之前的位置（间隙）上获得X锁，但由于事务1已经持有该记录的X锁，事务2无法获得锁，因此等待。
事务1在删除记录后，需要释放持有的锁，但由于事务2持有的锁阻止了它，因此事务1也进入等待状态。
循环等待：事务1等待事务2释放锁，事务2等待事务1释放锁，形成死锁

解决方案：

优化代码逻辑，长事务变为短事务。

不积跬步，无以至千里 --- xiaokai