MySQL 幻读与间隙锁

发表于

这篇文章是学习极客时间专栏《MySQL 实战 45 讲》的笔记，加上我个人的一些理解，具体细节可以去读这个专栏。我们知道行锁只能锁住已经存在的行，并不能阻止插入新的行，如果同一个事务的两次查询中间其他事务插入了新的行，后一次查询就可能看到前一次查询没有看到的行，这就是幻读（Phantom Read）。需要注意的是，在可重复读隔离级别下，普通的查询是快照读，不会看到其他事务插入的数据，只有当前读才可能出现幻读。

加锁规则

为了解决幻读的问题，InnoDB 存储引擎引入了间隙锁（Gap Lock），锁住的是两个值之间的间隙。与行锁不同的是，跟间隙锁存在冲突关系的是“往这个间隙中插入行”的操作，间隙锁之间是不存在冲突的。间隙锁和行锁合称 Next-Key Lock，每个 Next-Key Lock 都是一个前开后闭区间，下面是《MySQL 实战 45 讲》中总结的加锁规则“两个原则、两个优化和一个 Bug”：

  • 原则 1：加锁的基本单位是 Next-Key Lock
  • 原则 2：查询过程中访问到的对象才会加锁
  • 优化 1：索引上的等值查询，给唯一索引加锁时，Next-Key Lock 退化为行锁
  • 优化 2：索引上的等值查询，向右遍历且最后一个值不满足等值条件时，Next-Key Lock 退化为间隙锁
  • Bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止
create table `t` (
    `id` int(11) not null,
    `c` int(11) default null,
    `d` int(11) default null,
    primary key (`id`),
    key `c` (`c`)
) engine=InnoDB;

insert into t values (0, 0, 0), (5, 5, 5), (10, 10, 10), (15, 15, 15), (20, 20, 20), (25, 25, 25);

等值查询

事务 A 事务 B 事务 C
update t set d = d + 1 where id = 7;
insert into t values (8, 8 , 8); (block)
update t set d = d + 1 where id = 10; (ok)

因为表 t 中没有 id = 7 的行，所以根据原则 1 可知事务 A 加锁的范围是 (5, 10]，再根据优化 2 可知 Next-Key Lock 退化为间隙锁，加锁范围实际是 (5, 10)，所以事务 B 插入操作被阻塞，事务 C 的插入语句能够执行成功。下面看一个给非唯一索引加锁的例子：

事务 A 事务 B 事务 C
select id from t where c = 5 lock in share mode;
update t set d = d + 1 where id = 5; (ok)
insert into t values (7, 7, 7); (block)

注意，事务 A 的查询只会使用覆盖索引，不需要回表，因为索引 c 上面已经有 id 字段的值，所以只会在索引 c 上加锁，而事务 B 的更新操作使用的是主键索引，因此不会被阻塞。根据原则 1 事务 A 会在范围 (0, 5] 上加上 Next-Key Lock，由于 c 是非唯一索引，需要向右遍历到 id = 10 的行，最后根据优化 2 最终锁住的范围是 (0, 10)，所以事务 C 的插入操作被阻塞。

范围查询

事务 A 事务 B 事务 C
select * from t where id >= 10 and id < 11 for update;
insert into t values (8, 8, 8); (ok)
insert into t values (13, 13, 13); (block)
update t set d = d + 1 where id = 15; (block)

事务 A 首先需要定位 id = 10 的行，根据优化 1 可知 Next-Key Lock 退化为行锁，只会锁住这一行。范围查找需要查到不满足 id < 11 的行 id = 15 才会停止，所以最终锁住的范围是 [10, 15]，上面事务 B 和事务 C 的表现就容易解释了。如果事务 A 中的查询条件从 id 改为 c，就不能使用优化 1 了，所以最终锁住的范围是 (5, 15]，这时事务 B 的第一个插入语句就会被阻塞。

事务 A 事务 B 事务 C
select * from t where id > 10 and id <= 15 for update;
update t set d = d + 1 where id = 20; (block)
insert into t values (16, 16, 16); (block)

根据上面提到的一个 bug，事务 A 的查询其实后访问到 id = 20 的行，所以实际锁住的范围是 (10, 20]，事务 B 和事务 C 的表示就能解释了。理论上，主键索引（唯一索引）上的查询访问到 id = 15 这条记录就可以确定后面没有满足条件的行了，实际上 MySQL 还是会向后访问到 id = 20 的行，这可能是个 bug，但是没有得到社区的确认。

间隙说明

为了更好地说明“间隙”的概念，插入一行 (30, 10, 30)，所以非唯一索引 c 变成下面这个样子（第一行是 c 的值，第二行是 id 的值），其实在行 (c = 10, id = 10) 和 (c = 10, id = 30) 之间也是存在间隙的：

0 5 10 10 15 20 25
0 5 10 30 15 20 25
事务 A 事务 B 事务 C
delete from t where c = 10;
insert into t values (12, 12, 12); (block)
update t set d = d + 1 where c = 15 (ok)

delete 等值查询，根据优化 2，最终加的是 (c = 5, id = 5) 到 (c = 15, id = 15) 的间隙锁，不包含行 (c = 15, id = 15)，所以事务 B 的插入语句被阻塞，事务 C 的插入语句能够正常执行。

Limit 语句

事务 A 事务 B
delete from t where c = 10 limit 2;
insert into t values (12, 12, 12); (ok)

现在事务 A 的 delete 语句上面加了 limit 2，因为表 t 中 c = 10 的记录就只有两行，其执行的效果和不加 limit 2 的语句相同，但是这次事务 B 的插入语句能够正常执行了。这是因为扫描到行 (c = 10, id = 30) 时就已经有两条满足条件的记录了，加了 limit 2 之后扫描就到此结束了，所以加锁范围变为从 (c = 5, id = 5) 到 (c = 10, id = 30) 的前开后闭区间。

行锁与间隙锁

事务 A 事务 B
select id from t where c = 10 lock in share mode;
update t set d = d + 1 where c = 10; (block)
insert into t values (8, 8, 8);
(1213, 'Deadlock found when trying to get lock; try restarting transaction')

根据之前的分析，很容易知道事务 A 在索引 c 上加 Next-Key Lock (5, 10] 和间隙锁 (10, 15)，这个时候事务 B 也要申请范围 (5, 10] 的 Next-Key Lock，其实只能成功申请间隙锁 (5, 10)，加 c = 10 这行的行锁时被阻塞。这时事务 A 发起的插入语句，就被事务 B 在 (5, 10) 上的间隙锁阻塞，出现死锁，InnoDB 让事务 B 回滚了。

也就是说，我们在分析加锁规则的时候可以用 Next-Key Lock 来分析，但 InnoDB 在具体执行的时候，是分成间隙锁和行锁两段来执行的。

0%