InnoDB范围删除时为何会锁定更多无关记录？

问题背景 #

使用MySQL 5.7.32版本、RC（读已提交）隔离级别时，遇到InnoDB锁机制与预期不符的场景：删除操作在特定条件下会锁定不在筛选范围内的并发插入记录。

表结构与初始数据 #

创建表语句 #

CREATE TABLE `ep` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `e_id` int(11) NOT NULL COMMENT '',
  `name` varchar(255) NOT NULL COMMENT '',
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  PRIMARY KEY (`id`),
  KEY `idx_e_id` (`e_id`) USING BTREE,
  KEY `idx_create_at` (`create_at`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

初始数据插入 #

insert into ep(e_id, name, create_at) values(100, 'shijie', '2022-12-01 00:00:00'),(100, 'jianfeng', '2022-12-02 00:00:00'),(100, 'syx', '2022-12-03 00:00:00');

初始表中存在3条记录，id分别为1、2、3，对应create_at为2022-12-01至2022-12-03。

测试案例 #

案例1：锁等待异常场景 #

• 两个Session分别开启事务，各自插入一条create_at为2022-12-04的记录（对应id为4、5）
• Session1执行删除语句：

delete from ep where e_id=100 and create_at <= '2022-12-03 00:00:00'

此时Session1出现锁等待，尝试获取Session2插入的id=5的主键锁，但该记录的create_at为2022-12-04，不在删除条件范围内。

案例2：符合预期的正常场景 #

将删除条件改为create_at <= '2022-12-02 00:00:00'，Session1执行删除时无锁等待，不会锁定Session2插入的id=5记录。

预期逻辑 #

原本认为删除操作会先筛选e_id=100的记录，再过滤create_at符合条件的记录，仅锁定交集内的对应id记录，案例2符合该预期，但案例1出现异常。

问题核心 #

为何当create_at的删除范围覆盖到表中现有最大create_at值时，InnoDB会锁定超出范围的并发插入记录？主键范围删除时也存在类似现象，求原因解析。

原因解析 #

这是InnoDB间隙锁（Gap Lock）的特殊表现：尽管RC隔离级别下通常通过MVCC（多版本并发控制）避免幻读，不会主动加间隙锁，但存在例外场景——当优化器选择非唯一索引进行范围扫描，且范围边界触及该索引的最后一条记录时，InnoDB会对该记录之后的间隙加上临键锁（Next-Key Lock），临键锁会包含记录本身以及后续的间隙区间。

在案例1中：

• 删除条件create_at <= '2022-12-03'刚好覆盖了idx_create_at索引的最后一条记录
• 优化器基于该索引做范围扫描，扫描到末尾记录后，会对create_at>2022-12-03的间隙加临键锁
• Session2插入的create_at=2022-12-04记录恰好落在这个被锁定的间隙中，插入时会持有该记录的主键锁，导致Session1的删除操作需要等待锁释放

而案例2中，删除条件create_at <= '2022-12-02'并未触及idx_create_at索引的末尾，范围扫描在create_at=2022-12-02的记录处停止，不会对后续更大的create_at间隙加锁，因此Session2的插入不会触发锁等待。

主键范围删除的类似现象逻辑一致：当主键范围扫描到表中最大主键值时，会对主键最大值之后的间隙加临键锁，并发插入的新主键记录落在该间隙中就会导致锁等待。

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