数据库 [已解决] delete from ** where ** 到底锁了什么?

xiaoronglv · 2015年10月04日 · 最后由 hooopo 回复于 2015年10月08日 · 5441 次阅读

MySQL 的官方文档说

DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

当我们 delete 时,MySQL 会锁定一堆 next-key 锁。

所以我在本地做了一个小测试,想验证一下,测试步骤如下。

step1: 设定事务隔离级别

InnoDB Transaction Isolation Level:Repeated Read. (这是 MySQL 的默认级别,无需调整)

step2: 表结构

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;

step3: 插入五条数据

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);

step4: begin two transactions

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 锁机制的同学可否解释一下?

所谓 Next key lock 其实是一个 record + 2 个 gap,即[22, 28) + 28 + [28, 32) => [22, 32)

另,如果是 unique index 会退化成 record lock.

------ update -----

两个 gap 的说法不太合适,on every record the search encounters 其实因为 btree 的有序性,在非 unique index 的情况下(本贴的例子),这个查询其实 scan 了两个 record,即 28 和 32,扫 28 的时候 gap 是 [22, 28),并且加 record lock(28),扫 32 的时候加的是 [28, 32),因为 32 没中,所以没加 record lock。

需要 登录 后方可回复, 如果你还没有账号请 注册新账号