MySQL死锁排查：两条更新语句为何触发死锁？

AI 小北

2026-7-19

任务队列表死锁问题分析与解决

冲突SQL语句

两条引发死锁的固定结构SQL如下：

批量锁定任务的更新语句：

UPDATE db.tbl 
    SET pid = 68111
WHERE pid IS NULL 
AND processed IS NULL 
AND `time` <= now() 
LIMIT 10

单条任务标记完成的更新语句：

UPDATE `db`.`tbl` 
    SET `processed` = -1,
        `updated_on` = NOW() 
WHERE (`tbl`.`id` = 108588129)

死锁日志详情

通过show engine status innodb;获取的精简死锁日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-06-08 08:16:27 0x7f70f3ef1700
*** (1) TRANSACTION:
TRANSACTION 2857804352, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 2480 lock struct(s), heap size 286928, 9581 row lock(s)
MySQL thread id 25966272, OS thread handle 140144661681920, query id 847014117 x.x.x.x db_user updating
UPDATE db.tbl SET pid = ''68111'' WHERE pid IS NULL AND processed IS NULL AND `time` <= now() LIMIT 10

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2714 page no 86552 n bits 1552 index processed of table `db`.`tbl` trx id 2857804352 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: SQL NULL;
 1: len 4; hex 8678cf9b; asc  x  ;; 

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: SQL NULL;
 1: len 4; hex 8678cfad; asc  x  ;; 

<Snip a lot of Record Locks just like the one above>

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2714 page no 113955 n bits 160 index PRIMARY of table `db`.`tbl` trx id 2857804352 lock_mode X locks rec but not gap waiting
Record lock, heap no 92 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 4; hex 8678ec61; asc  x a;;
 1: len 6; hex 0000aa56a25d; asc    V ];;
 2: len 7; hex 0100002bc01686; asc    +   ;; 
 3: len 4; hex 803ff583; asc  ?  ;; 
 4: len 4; hex 73746f70; asc stop;;
 5: len 4; hex 80000002; asc     ;; 
 6: len 1; hex 83; asc  ;; 
 7: len 4; hex 80005737; asc   W7;;
 8: len 6; hex 4b4a38363431; asc KJ8641;;
 9: len 8; hex 80000008a515e59b; asc         ;; 
10: len 4; hex 800001be; asc     ;; 
11: len 5; hex 99b050b41a; asc   P  ;; 
12: len 4; hex 84ad8892; asc     ;; 
13: len 3; hex 736d73; asc sms;;
14: SQL NULL;
15: SQL NULL;
16: len 1; hex 7f; asc  ;; 
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: len 4; hex 53746f70; asc Stop;;
21: len 5; hex 99b050b41b; asc   P  ;; 
22: len 5; hex 99b050b41b; asc   P  ;; 


*** (2) TRANSACTION:
TRANSACTION 2857804381, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 25966282, OS thread handle 140122483259136, query id 847014368 x.x.x.x other_db_user updating
UPDATE `db`.`tbl` SET `processed` = ''-1'', `updated_on` = NOW() WHERE (`tbl`.`id` = 108588129)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2714 page no 113955 n bits 160 index PRIMARY of table `db`.`tbl` trx id 2857804381 lock_mode X locks rec but not gap
Record lock, heap no 92 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 4; hex 8678ec61; asc  x a;;
 1: len 6; hex 0000aa56a25d; asc    V ];;
 2: len 7; hex 0100002bc01686; asc    +   ;; 
 3: len 4; hex 803ff583; asc  ?  ;; 
 4: len 4; hex 73746f70; asc stop;;
 5: len 4; hex 80000002; asc     ;; 
 6: len 1; hex 83; asc  ;; 
 7: len 4; hex 80005737; asc   W7;;
 8: len 6; hex 4b4a38363431; asc KJ8641;;
 9: len 8; hex 80000008a515e59b; asc         ;; 
10: len 4; hex 800001be; asc     ;; 
11: len 5; hex 99b050b41a; asc   P  ;; 
12: len 4; hex 84ad8892; asc     ;; 
13: len 3; hex 736d73; asc sms;;
14: SQL NULL;
15: SQL NULL;
16: len 1; hex 7f; asc  ;; 
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: len 4; hex 53746f70; asc Stop;;
21: len 5; hex 99b050b41b; asc   P  ;; 
22: len 5; hex 99b050b41b; asc   P  ;; 


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2714 page no 86552 n bits 1552 index processed of table `db`.`tbl` trx id 2857804381 lock_mode X locks rec but not gap waiting
Record lock, heap no 1462 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: SQL NULL;
 1: len 4; hex 8678ec61; asc  x a;; 

*** WE ROLL BACK TRANSACTION (2)

表结构

表的精简定义：

CREATE TABLE `tbl` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pid` int DEFAULT NULL,
  `processed` tinyint DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,

  `other` varchar(10) NOT NULL,
  
  PRIMARY KEY (`id`),
  KEY `ix_other` (`other`),
  KEY `ix_processed` (`processed`),
  KEY `ix_time` (`time`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

补充说明：表数据量超500万，月新增约140万条，用作任务队列。处理器通过第一条语句批量锁定10条任务，处理完成后用第二条语句更新processed标记，偶发死锁。

死锁成因分析

从死锁日志可以明确循环等待的锁关系：

事务1（批量更新）：
- 先通过ix_processed索引筛选符合processed IS NULL的行，对这些行的索引记录加了X锁。
- 由于要更新pid字段，需要回表到主键索引获取对应行的锁，此时请求某条特定行的主键X锁，但该锁已被事务2持有。
事务2（单条更新）：
- 先通过主键索引找到目标行，对主键记录加了X锁。
- 由于要更新processed字段，需要维护ix_processed索引，此时请求该行在ix_processed索引上的X锁，但该锁已被事务1持有。

双方互相持有对方需要的锁，形成循环等待，触发InnoDB死锁检测并回滚其中一个事务（此处为事务2）。

解决办法

1. 创建覆盖联合索引，避免回表

为批量更新语句创建联合覆盖索引：

CREATE INDEX idx_processed_pid_time ON tbl(processed, pid, `time`);

该索引包含批量更新WHERE子句的所有条件字段，InnoDB可以直接通过该索引找到符合条件的行并更新pid，无需回表到主键索引，减少锁的持有范围和跨索引的锁等待，从根源避免死锁。

2. 调整锁获取顺序

确保两个事务获取锁的顺序一致：

对于单条更新语句，先通过ix_processed索引锁定目标行，再更新主键行；不过实际操作中，联合索引方案更高效。

3. 优化批量更新逻辑

可以将批量更新拆分为“先查询锁定行，再逐个更新”的方式，不过会增加代码复杂度，不如索引优化直接。
保持LIMIT 10的小批量更新规模，避免一次性锁定过多行。

4. 死锁重试机制

在业务代码中为更新语句添加死锁重试逻辑，当捕获到死锁错误（MySQL错误码1213）时，自动重试几次，降低业务影响。

内容的提问来源于stack exchange，提问作者Vilx-

MySQL死锁排查：两条更新语句为何触发死锁？

冲突SQL语句 #

死锁日志详情 #

表结构 #

死锁成因分析 #

解决办法 #

1. 创建覆盖联合索引，避免回表 #

2. 调整锁获取顺序 #

3. 优化批量更新逻辑 #

4. 死锁重试机制 #