自增主键表执行SELECT MAX() FOR UPDATE时的死锁问题及解决

表结构定义 #

CREATE TABLE `measure` (
  `measureId` bigint NOT NULL,
  `sensorId` int NOT NULL,
  `timestamp` bigint NOT NULL,
  `data` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE `measure`
  ADD PRIMARY KEY (`measureId`),
  ADD KEY `measure_index` (`sensorId`,`timestamp`);

ALTER TABLE `measure`
  MODIFY `measureId` bigint NOT NULL AUTO_INCREMENT;

measureId 主要作为自增主键使用，但有时需要手动指定该值插入数据。应用中使用如下事务逻辑：

begin;
select max(measureId) from measure for update;
-- 使用获取到的最大ID创建新记录
insert into measure values (max_id + 1, ...), (max_id + 2, ...), ...;
-- 执行其他操作
commit;

使用 select ... for update 是为了防止在查询最大ID和插入新行之间有其他事务插入数据，避免因自增机制导致ID冲突（使用默认的REPEATABLE READ隔离级别）。

非并发环境下事务正常，但两个事务同时执行时会触发死锁：

T1: select max(measureId) ...;
T2: select max(measureId) ...; -- 开始等待
T1: insert into measure values (max_id + 1, ...), ...;
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

补充InnoDB死锁监控输出：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-01-23 13:58:10 139850373236480
*** (1) TRANSACTION:
TRANSACTION 41530, ACTIVE 62 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1251, OS thread handle 139850310264576, query id 5030779 172.19.0.1 root optimizing
select max(measureId) from measure for update

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 22722 n bits 360 index PRIMARY of table `WEATHER_STATION`.`measure` trx id 41530 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 22722 n bits 360 index PRIMARY of table `WEATHER_STATION`.`measure` trx id 41530 lock_mode X waiting
Record lock, heap no 290 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 80000000004aa795; asc      J  ;; 
 1: len 6; hex 00000000a1f8; asc       ;; 
 2: len 7; hex 82000000a913e6; asc        ;; 
 3: len 4; hex 80000192; asc     ;; 
 4: len 8; hex 8000000063cac0ed; asc     c   ;; 
 5: len 4; hex 6666ea41; asc ff A;;


*** (2) TRANSACTION:
TRANSACTION 41529, ACTIVE 163 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 1250, OS thread handle 139850312378112, query id 5030780 172.19.0.1 root update
insert into measure values (4892566, 1, 2, 3)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 22722 n bits 360 index PRIMARY of table `WEATHER_STATION`.`measure` trx id 41529 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 290 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 80000000004aa795; asc      J  ;; 
 1: len 6; hex 00000000a1f8; asc       ;; 
 2: len 7; hex 82000000a913e6; asc        ;; 
 3: len 4; hex 80000192; asc     ;; 
 4: len 8; hex 8000000063cac0ed; asc     c   ;; 
 5: len 4; hex 6666ea41; asc ff A;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 22722 n bits 360 index PRIMARY of table `WEATHER_STATION`.`measure` trx id 41529 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

核心疑问 #

1. 为什么会出现死锁？按预期T1应该已持有主键索引的锁。
2. 如何解决该死锁问题？

死锁原因分析 #

从InnoDB死锁日志可明确锁的互斥关系：

1. 事务T2先执行select max(measureId) for update，拿到了主键索引的supremum伪记录锁（索引末尾的虚拟记录，用于标记插入位置），同时锁定了当前最大的measureId行（heap no 290）。
2. 事务T1随后执行同样的查询，成功拿到supremum伪记录锁，但需要等待T2释放最大行的锁才能完成查询。
3. 此时T2开始执行插入操作，插入新行需要获取supremum伪记录的插入意向锁，但T1已持有该位置的X锁，导致T2进入等待。
4. 最终形成循环等待：T1等T2释放最大行锁，T2等T1释放supremum锁，触发InnoDB死锁检测，回滚T2。

本质上，select max(...) for update在InnoDB中会锁定两个关键资源：当前最大数据行，以及索引末尾的supremum伪记录。并发场景下两个事务分别持有对方需要的锁，就会导致死锁。

解决方案 #

1. 用全局排他锁控制并发 #

在事务开始前先获取自定义全局锁，确保同一时间只有一个事务执行ID生成和插入逻辑：

BEGIN;
-- 获取锁，超时时间10秒，可根据业务调整
SELECT GET_LOCK('measure_id_generator_lock', 10);
SELECT MAX(measureId) INTO @max_id FROM measure;
INSERT INTO measure VALUES (@max_id + 1, ...), (@max_id + 2, ...);
-- 释放锁
SELECT RELEASE_LOCK('measure_id_generator_lock');
COMMIT;

这种方式彻底避免并发冲突，但会降低并发能力，适合并发量不高的场景。

2. 尽量依赖自增主键自动生成ID #

如果不是必须手动指定measureId，直接让InnoDB自动分配自增ID：

BEGIN;
INSERT INTO measure (sensorId, timestamp, data) VALUES (...), (...);
-- 执行其他操作
COMMIT;

这是最安全高效的方式，完全规避ID冲突和死锁问题。如果确实需要手动指定部分ID，可以将手动ID放在自增序列之外的区间（比如自增从1开始，手动ID从1000000开始），避免冲突。

3. 调整锁定范围避免循环等待 #

修改查询语句，锁定整个主键索引范围，确保后续插入不会被干扰：

BEGIN;
SELECT MAX(measureId) INTO @max_id FROM measure WHERE measureId > 0 FOR UPDATE;
INSERT INTO measure VALUES (@max_id + 1, ...), (@max_id + 2, ...);
COMMIT;

这种方式会锁定从measureId>0到supremum的整个范围，阻止其他事务获取该范围的锁，避免形成循环等待。

4. 使用SKIP LOCKED（MySQL 8.0+） #

如果业务允许跳过锁定的行，改用SKIP LOCKED让查询直接返回，避免等待：

BEGIN;
SELECT MAX(measureId) INTO @max_id FROM measure FOR UPDATE SKIP LOCKED;
-- 如果@max_id为空，说明有其他事务在处理，可重试或返回错误
INSERT INTO measure VALUES (@max_id + 1, ...), (@max_id + 2, ...);
COMMIT;

这种方式适合可以接受重试逻辑的场景，避免长时间等待和死锁。

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