Ruby China

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

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

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

2 个赞
hooopo #1 2015年10月08日

所谓 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。

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