MySQL 的官方文档说
DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
当我们 delete 时,MySQL 会锁定一堆 next-key 锁。
所以我在本地做了一个小测试,想验证一下,测试步骤如下。
InnoDB Transaction Isolation Level:Repeated Read. (这是 MySQL 的默认级别,无需调整)
CREATE TABLE `follows` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`followable_type` varchar(255) DEFAULT NULL,
`followable_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_first` (`followable_id`,`followable_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into follows(followable_type, followable_id) values ("Post", 6);
insert into follows(followable_type, followable_id) values ("Post", 22);
insert into follows(followable_type, followable_id) values ("Post", 28);
insert into follows(followable_type, followable_id) values ("Post", 32);
insert into follows(followable_type, followable_id) values ("Post", 34);
Session1
session1> begin;
session1> delete from follows where followable_id=28;
Session2
session2> begin;
session2> insert into follows(followable_type, followable_id) value ("Post", 22);
Lock wait timeout exceeded; try restarting transaction
session2>insert into follows(followable_type, followable_id) value ("Post", 28);
Lock wait timeout exceeded; try restarting transaction
session2>insert into follows(followable_type, followable_id) value ("Post", 32);
Query OK, 1 row affected (0.01 sec)
测试显示这条 sql 语句锁定的区间是 [22, 32),是一个前闭后开的区间。
next key lock 的定义是:This is a combination of a record lock on the index record and a gap lock on the gap before the index record.
所以按道理应该是 (22, 28],一个前开后闭的区间。
精通 MySQL 锁机制的同学可否解释一下?