InnoDB并发插入引发死锁问题排查求助

核心原因 #

InnoDB的B+树索引末尾会维护一个虚拟的Supremum记录，作为索引范围的边界。结合你的场景，死锁触发逻辑如下：

• 在RR隔离级别下，事务执行SELECT ... FOR UPDATE时，若查询的二级索引范围覆盖到索引末尾（比如无匹配行、查询条件为范围且延伸至索引最后），会对Supremum记录加间隙锁或共享锁。
• 当插入新行的二级索引键值为当前索引最大值时，插入操作需要获取Supremum的排他锁来完成索引节点更新（新行需插在Supremum前，需修改索引指针）。
• 并发场景中，两个事务先通过SELECT ... FOR UPDATE持有Supremum的锁，随后各自尝试插入时，均需获取对方持有的Supremum排他锁，形成循环等待，触发死锁。
• 即便插入不同行，只要二级索引键值落在Supremum前的同一间隙，或均为当前索引最大值，就会触发该冲突。

结合场景的具体分析 #

假设你的shoes表结构（含引发死锁的二级索引）：

CREATE TABLE `shoes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `brand` varchar(50) NOT NULL COMMENT '品牌',
  `size` int NOT NULL COMMENT '尺码',
  `color` varchar(30) NOT NULL COMMENT '颜色',
  `stock` int NOT NULL DEFAULT '0' COMMENT '库存',
  PRIMARY KEY (`id`),
  KEY `idx_brand_size` (`brand`, `size`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

事务代码逻辑：

BEGIN;
SELECT id FROM shoes WHERE brand = ? AND size = ? FOR UPDATE;
INSERT INTO shoes (brand, size, color, stock) VALUES (?, ?, ?, ?);
COMMIT;

大量并发时，多个事务的SELECT ... FOR UPDATE会锁定到Supremum的间隙，后续插入操作争夺Supremum排他锁，最终形成死锁。

解决方案 #

1. 替换先查后插逻辑（推荐） #

使用INSERT ... ON DUPLICATE KEY UPDATE或REPLACE INTO替代手动锁表逻辑，让InnoDB自动处理唯一性约束，避免锁冲突：

• 先给brand和size添加唯一索引：

  ALTER TABLE shoes ADD UNIQUE KEY uk_brand_size (brand, size);
  

• 替换事务代码：

  INSERT INTO shoes (brand, size, color, stock) VALUES (?, ?, ?, ?)
  ON DUPLICATE KEY UPDATE stock = stock + 1; -- 按业务需求调整更新逻辑
  

2. 降低隔离级别至RC #

将数据库隔离级别改为读已提交（RC），该级别下InnoDB会关闭间隙锁（外键、唯一性约束场景除外），大幅降低Supremum锁冲突概率。需评估业务是否能接受不可重复读的特性。

3. 缩小查询范围 #

若必须保留先查后插逻辑，优化SELECT ... FOR UPDATE的查询条件，避免让范围覆盖到二级索引的Supremum位置，比如明确枚举值查询，避免模糊范围。

4. 拆分事务（谨慎使用） #

将SELECT ... FOR UPDATE与INSERT拆分为独立事务，但会引入重复插入风险，需结合业务额外做唯一性校验。

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