为什么innodb在删除范围时会锁定更多的记录?

Question

我使用mysql innodb数据库。我以为我已经理解了mysql的锁机制。但我发现了一个与我的理解相冲突的例子。请参阅下面的示例(在版本5.7.32中使用rc隔离级别进行了验证)。

创建表

Create Table: 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');

在下一次用例测试之前，ep表中有3条记录。

id: 1
e_id: 100
name: shijie
create_at: 2022-12-01 00:00:00
update_at: 2023-01-01 05:31:13
id: 2
e_id: 100
name: jianfeng
create_at: 2022-12-02 00:00:00
update_at: 2023-01-01 05:31:13
id: 3
e_id: 100
name: syx
create_at: 2022-12-03 00:00:00
update_at: 2023-01-01 05:31:13

• test case 1
并发事务

tbody> <<tr>

Session1	Session2	Locks
开始,	开始,
插入ep (e_id、名称、create_at)值(100年,"2022-12-04"就是"stt");
	插入ep (e_id、名称、create_at)值(100年,ssd, 2022-12-04就是),
select * from epG; id: 1 e_id: 100 name: shijie create_at: 2022-12-01 00:31:13 id: 2 e_id: 100 name: jianfeng create_at: 2022-12-01 00:00:00 update_at: 2023-01-01 05:31:13 id: 3 e_id: 100 name: syx create_at: 2023-12-01 00:00:00 update_at: 2023-12-01 05:31:13 id: 4 e_id: 100 name: stt create_at: 2022-12-01 00:00:00 update_at: 2023-01-01 05:31:13 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 update_at: 2023-12-01 00:00:00 name: stt create_at: 2022-12-04 00:00:00 update_at:2023-01-01 05:41:47	select * from ep G; id: 1 e_id: 100 名称:运输 create_at: 2022-12-01就是 update_at: 2023-01-01 05:31:13 id: 2 e_id: 100 名称:剑锋 create_at: 2022-12-02就是 update_at: 2023-01-01 05:31:13 id: 3 e_id: 100 名称:syx create_at: 2022-12-03就是 update_at: 2023-01-01 05:31:13 id: 5 e_id: 100 名称:ssd create_at: 2022-12-04就是 update_at:2023-01-01 05:44:46
delete from ep where e_id=100 and create_at <= '2022-12-03 00:00:00';		锁等待

MySQL在执行过程中锁定它所查看的行(并可能再次释放它们)。

在你的例子中，基于行为，对于第二个测试用例，MySQL使用create_at上的索引来查找行，对于第一个案例，它没有。

因此，对于第二次删除，MySQL甚至不会查看id 4和5(因为它们在日期范围之外)，因此不必等待id 4和5上的锁。

对于第一次删除，MySQL不使用索引，而是使用主键或e_id上的索引，因此必须等待锁。

在没有索引的情况下重复这个实验，它可能符合你的理解(尽管你实际上没有指定你所期望的)。

(不是答案，但有用的信息…)

将idx_e_id替换为

INDEX(e_id, created_at)

使DELETE有一个有用的指标。