MySQL 只改一条数据我这么难的吗

   “我一直觉得在休息的同时，也是人和人拉开差距的时候。我之前喜欢说一句话，生时何必久睡，死后自然长眠。现在我觉得早睡早起身体好。”

本篇文章是学习极客专栏幻读后所写的，文章中一些总结来源于作者。

开篇

如果没有特别说明，本篇的内容都是在可重复读级别事务下进行，因为为了解决幻读，InnoDB 引入了间隙锁🔐，而间隙锁是在可重复读的隔离级别下才会生效的，所以如果你把隔离级别设置成读提交的情况下，就不存在间隙锁了，当然你需要面对的是其他出现的问题了，也不在本篇文章的讨论之中，本篇文章主要是以实践为主。

开始之前运行一下命令查看自己 InnoDB 事务隔离级别。

show variables like 'transaction_isolation'

幻读的定义

为了便于分析，我们开始在数据库中创建如下表，并初始化插入一些数据。把id设置为主键索引，字段c设置为普通索引，并插入六条数据。

接着我们来看下面的一条语句，会咋么进行加锁。

begin;
select * from t where d=5 for update;
commit;

比较好理解是，这条语句会查询到 d=5 这一行，也就是记录(5,5,5),对应的主键 id =5,这里的 for update 会在查询完成之后，給当前主键 id 等于5的这条记录加一个写锁，由于两阶段锁的协议，这个写锁会在事务 commit 之后才释放。

现在的问题在于，如果只是在 id =5 这一行上加锁，而其他行不加锁的情况下会发生什么？下面模拟多个事务同时进行的情况。

从上面可以看到事务A执行了三次查询，并且都是 for update，表示当前读，按照我们说的，如果只是给 id=5 这一行加上写锁，这时候事务 B 的 T2 时刻在编辑 id=0 这一条记录的时候并没有被锁住，所以事务 B 的的更新语句可以执行，那么 id=0 的当前的记录变成(0,0,5),事务 B 并没有像 A 一样显式的开启事务，代表的这一条更新就是事务，更新完毕事务提交，事务 A 的 T3 时刻当前读，条件语句还是 where,所以可以读取事务 B 提交的修改。T4 时刻 C 事务插入一条记录，所以 T5 时刻 A 事务查询的结果是3条。接着 T6 时刻事务 A 提交事务，释放锁。

这里需要说明的是，在 T5 时刻读到 id=1 这一行数据才称之为幻读，幻读指的是在一次事务中前后两次查询同一范围数据的时候，后一次查询看到了前一次查询没有看到的行，事务 A 的 T3 时刻查询 where d=5 的时候，此时并没有 id=1 这一行数据，等到事务 A 的 T5 时刻查询的时候由于事务 C 的 T4 时刻插入了一条 id=1 的数据，导致了 id=1 这一行的幻读，所以你可以知道，幻读仅仅指新插入的行，所以对于 id=0 的修改不能算是幻读。**

那么按照我们想的有什么问题吗？当然有问题。首先是事务 A 在 T1 时刻就声明要把所有 d=5 的行锁住，但是现在这个语义已经被破坏了。让我们来稍微修改下列子。

由于事务 A 的 T1 时刻只是锁住 id=5 这一条数据加了行数，但是并没有给 id=0 的数据加上行锁，导致此时即便它的 d=5 也可以进行更新操作，至于事务 C 在事务 A 加锁的时候记录都还没生成，导致它也能进行修改操作。这两条语句都破坏了事务A 在 T1 时刻的加锁声明。

另一点在于数据一致性的问题。如果我们再在事务 A 那稍微改动一下。

update 加锁的语义和 select ..... for update 的语义是一致的，所以这时候加上 update 也很合理。如果按照这个流程走过来，会发生什么，注意事务 A的事务要到 T6 时刻才提交。

当然有问题了，等到 T6 时候事务 A 提交事务的时候，会将所有 d=5 记录的 d 值修改成 100(自己动手做，可以查看下 binlog 内容)。这就造成了本来正常情况下事务 B 的记录是(0,5,5),事务 C 的记录是 (1,5,5)。这时候三行 d=5 的数据变成了 (0,5,100),(1,5,100)和(5,5,100)。这时候就发生了数据不一致。

所以综上所述，如果在 select * from t where d=5 for update 的时候只是给 id=5 这一行加上行锁是不行的。如果我们把 select 扫描到的行全部加上行锁呢？那么事务 B 确实没问题，因为在它要更新的时候，事务 A 的写锁导致它被锁住，需要等到事务 A 提交事务才得以进行，所以没问题。但是事务 C 就不一样了，因为在事务 A 锁住扫描行的时候，id=1 这条记录还没诞生呢，所以也就不存在被锁住了，这也就是幻读的问题还是没解决

如何解决幻读

幻读产生的原因就是行锁只能锁住行， 但是对于新插入的记录，更新的是行之间的间隙，InnoDB 为了在可重复读的情况下解决幻读问题，引入了间隙锁(Gap Lock)。

这样当你再次执行 select * from t where d =5 for update 的时候，就不仅仅只是给6条记录加上行锁，还同时加上7个间隙锁。这样能确保的是在一个事务锁的期间，没有新的记录能 insert 进来，就解决了幻读的问题。

间隙锁之前不互锁。比如说。

上面的语句事务 B 并不会被锁住，因为表中没有 c=7 这条记录，所以事务 A 加的是间隙锁(5,10)，而事务 B 也是加的(5,10) 的间隙锁，他们保护的是共同的目标，就是保护这个间隙不被插入新值，所以他们并不冲突。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

这里的 supremum 的值是多少呢。实际上 InnoDb 给每一个索引加了一个不存在的最大值 supremum ，这样就符合前开后闭的准则了。

间隙锁在解决幻读的同时，也会带来新的问题，比如下面的。

我们来实验一下，看发生了什么。事务 B 在插入的时候会被锁住。

当我在事务 A 中执行同样的操作时，事务 A 此时也被锁住了，这时候已经造成了死锁，两个事务互相等待对方释放锁的资源，形成了死锁，当然 InnoDB 马上监测到死锁，让事务 A 的 insert 语句直接报错返回并且释放锁，所以此时可以看到事务 B 获取到锁马上执行插入成功。

这两个事务的执行语句加锁过程又是则么样的呢？

1. 事务 A 执行 select * from t where id =9 ，因为 id=9 并不存在，所以语句加上间隙锁(5,10)。

2. 事务 B 执行 select * from t where id =9, 因为 此时 id=9 不存在，所以事务 B 也加上间隙锁(5,10)。

3. 当事务 B 执行 insert (9,9,9) 的时候 ，被事务 A 的间隙锁锁住了。

4. 当事务 A 执行 insert (9,9,9) 的时候，被事务 B 的间隙锁锁住了。

5. 因此产生了死锁。

在引入间隙锁之后，可能会导致同样的语句锁住更大的范围。影响了并发性。为了解决幻读，我们竟然引入了这么多东西，还能咋么搞？开篇就已经说过了，间隙锁是在可重复读的隔离级别下才会出现的，如果改为读已提交的情况下，就没有这些问题了，当然出现的问题就是读已提交情况下能出现的问题了😃。

上面介绍的只是间隙锁和 next-key lock 的概念，还没有实际开始加锁的规则，下面开始一些实践。MySQL 在后续版本中可能会修改加锁的规则，所以以下的内容版本应该在 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

首先上面提到过间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间，这也是加锁的基本单位。接下来我们来看第一个例子。等值查询的间隙锁。

思考下结果是什么?我们来简单分析一下，事务 A 在 T1 时刻进行加锁的操作，由于当前记录中并不存在 id=7 这条记录，所以加锁的单位就是 next-key lock，所以加锁的范围就是(5,10],同时这是一个等值的查询 id=7, 而 id=10显然不满足条件，所以锁退化成间隙锁即(5,10)。所以最终的结果应该是事务 B 被挂起，事务 C 执行成功，让我们来验证一下。

同时开启三个窗口，事务 B 插入被锁住了，而事务 C 可以执行。

刚才是在唯一索引上进行的操作，接下来是非唯一索引等值锁。

我们先来复现一下结果。千万别惊呆😮

什么情况❓你可以看到 事务 B 竟然没有被锁住，反而是事务 C 被锁住了。首先加锁会给(0,5]加上 next-key lock，接着因为 c 只是普通索引，所以在查询 c=5 的时候并不会立即停下。而是会继续向右查询，此时查询到下一条 c=10 (不等于5)，根据规则，访问到的都必须加锁，所以给(5,10]加上 next-key lock。但是同时是等值判断最后一个 c=10 明显不等于5，所以又退化为间隙锁(5,10)。之前说的只有访问到的对象会加上锁。你再看事务 A ，这个查询明显会使用到覆盖索引，并不需要去访问主键索引，换句话说，这时候主键索引没有被访问，那就不会给他加锁。所以事务 B 并没有被锁住，事务 C 被事务 A 间隙锁(5,10)锁住了。

上面的例子不同点在于 lock in share mode,这个语句只会锁住覆盖索引，如果你想让他锁住事务 B 很简单 改为 for update ，系统会默认你接下来要修改数据，会顺便给主键索引上满足条件的记录加上行锁。我们来看一下。

现在就可以看到两个事务都被事务 A 锁住了。

再来看一个主键索引范围锁。

我们来分析一下，开始的时候要找到第一行 id=10,本来应该是 next-key lock(5,10],因为是主键上的等值查询，所以退化成行锁，所以只锁住 id=10 这一行。接下来范围查找的时候，找到 id=15 这一行即停下，所以此时需要加上next-key lock (10,15] ,所以综合下来事务 A 锁的范围是主键索引上，行锁 id=10 以及锁 (10,15]，这时候你再看下面这张图，你就知道为什么了。

上面的几个例子展示了一些间隙锁的加锁规则，当前实际的场景还有很多很多，比如如果带上 limit 的时候又是咋么加锁的呢，这些都是可以自己动手做实践的，实践才能检验真理。

其实在专栏里，作者已经总结了间隙锁的加锁原则，我这里直接照搬过来，间隙锁规则包含了两个“原则”、两个“优化”和一个“bug”。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

本作品采用《CC 协议》，转载必须注明作者和本文链接