如何理解MySQL中间隙锁可以避免幻读的问题？

码上得天下

已于 2024-03-03 01:04:58 修改

阅读量351

点赞数 1

分类专栏： Mysql 文章标签： mysql 数据库

于 2024-03-03 00:50:38 首次发布

原文链接：https://www.zhihu.com/question/422425988/answer/2763688819

版权

Mysql 专栏收录该内容

107 篇文章 14 订阅

订阅专栏

有一个困扰我的问题，看了很多资料都说在innodb下的RR级别，可以通过mvcc+间隙锁避免幻读。而RR下又存在幻读问题，那mvcc+间隙锁避免幻读避免了哪些幻读问题呢，还有哪些幻读问题是它们解决不了的？

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

快照读：单纯的select操作。读取的是快照（ReadView）中的数据，可能是历史数据

当前读：select ... for update/in share mode、update、insert、delete。读取的总是当前的最新数据

对于快照读，RR中一个事务的所有快照读读取的都是同一份快照，所以无论其他的事务怎么修改，无论是更新还是插入删除，都不会影响当前事务的快照读结果，也就不会出现不可重复读、幻读的情形。

对于当前读，你读取的行，以及行的间隙都会被加锁，直到事务提交时才会释放，其他的事务无法进行修改，所以也不会出现不可重复读、幻读的情形。

所以如果你总是进行快照读，或者总是进行当前读，是不会出现幻读的情况的。（如果同时出现快照读和当前读，则可能幻读，下文的两个场景都是这种情况）

MySQL在RR隔离级别下，这种读写不一致的行为合理吗？读取旧的快照数据，却更新最新的数据版本，这很容易带来一些不符合直觉的结果，比如明明能读到一行，却无法删除该行（事务一通过id=1查询到数据，事务2把id=1改成id=2,事务1通过id=1删除数据不成功），或者明明能更新一行，却无法读到该行（下面幻读场景一），具体可以参考这篇文章中的例子。相反，在PostgreSQL中，RR是不会出现这种读写不一致的问题的，这是因为PG如果发现当前事务想要进行更新的行已经被其他并发事务更改过了（并且已经提交），当前事务就会abort，详情可见PG文档。想一想，如果当前事务不abort，会怎么样？有两种选择：

1. 在RR的事务快照版本基础之上进行更新，这可能导致丢失更新（Lost Update），比如两个事务执行update table set value=value+1，最终value可能只加了1，丢失了其中一个事务的修改。

2. 在最新版本的数据基础之上进行更新（MySQL的做法），这可能导致读写操作观察到不一致的结果。

为了避免这两种情况中出现的异常现象，PG选择了abort事务，Oracle也是类似的做法。

SQL 92标准中对隔离级别的定义是失败的，其表述非常含糊，且忽略了一些情况，这也一定程度上导致了后来各个数据库产品对隔离级别都有自己特殊的解释和实现，但也都能号称自己满足SQL标准。比如Oracle和PostgreSQL9.1之前的Serializable隔离级别并不能保证真正的可串行化，会出现Write Skew等不可串行化的异常。数据库产品之间在隔离级别行为上的区别对数据库的使用者---上层业务带来了很多不便，切换数据库前必须慎重。论文《A Critique of ANSI SQL Isolation Levels》对SQL 92标准做出了批判和补全，推荐对隔离级别感兴趣的同学阅读这篇论文

作者：福柯
链接：https://www.zhihu.com/question/422425988/answer/1488030769
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

这两个解决方案是很大程度上解决了幻读现象，但是还是有个别的情况造成的幻读现象是无法解决的。

这次，就跟大家好好聊这个问题

什么是幻读？

首先来看看 MySQL 文档是怎么定义幻读（Phantom Read）的:

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

翻译：当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。

举个例子，假设一个事务在 T1 时刻和 T2 时刻分别执行了下面查询语句，途中没有执行其他任何语句：

SELECT * FROM t_test WHERE id > 100;

只要 T1 和 T2 时刻执行产生的结果集是不相同的，那就发生了幻读的问题，比如：

T1 时间执行的结果是有 5 条行记录，而 T2 时间执行的结果是有 6 条行记录，那就发生了幻读的问题。
T1 时间执行的结果是有 5 条行记录，而 T2 时间执行的结果是有 4 条行记录，也是发生了幻读的问题。

快照读是如何避免幻读的？

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是启动事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题。

做个实验，数据库表 t_stu 如下，其中 id 为主键。

然后在可重复读隔离级别下，有两个事务的执行顺序如下：

从这个实验结果可以看到，即使事务 B 中途插入了一条记录，事务 A 前后两次查询的结果集都是一样的，并没有出现所谓的幻读现象。

当前读是如何避免幻读的？

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

这很好理解，假设你要 update 一个记录，另一个事务已经 delete 这条记录并且提交事务了，这样不是会产生冲突吗，所以 update 的时候肯定要知道最新的数据。

另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。

接下来，我们假设select ... for update当前读是不会加锁的（实际上是会加锁的），在做一遍实验。

这时候，事务 B 插入的记录，就会被事务 A 的第二条查询语句查询到（因为是当前读），这样就会出现前后两次查询的结果集合不一样，这就出现了幻读。

所以，Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

举个具体例子，场景如下：

事务 A 执行了这面这条锁定读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）。

然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。

幻读被完全解决了吗？

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

我举例一个可重复读隔离级别发生幻读现象的场景。

第一个发生幻读现象的场景

还是以这张表作为例子：

事务 A 执行查询 id = 5 的记录，此时表中是没有该记录的，所以查询不出来。

# 事务 A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_stu where id = 5;
Empty set (0.01 sec)

然后事务 B 插入一条 id = 5 的记录，并且提交了事务。

# 事务 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_stu values(5, '小美', 18);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

此时，事务 A 更新 id = 5 这条记录，对没错，事务 A 看不到 id = 5 这条记录，但是他去更新了这条记录，这场景确实很违和，然后再次查询 id = 5 的记录，事务 A 就能看到事务 B 插入的纪录了，幻读就是发生在这种违和的场景。

# 事务 A
mysql> update t_stu set name = '小林coding' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_stu where id = 5;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  5 | 小林coding   |   18 |
+----+--------------+------+
1 row in set (0.00 sec)

整个发生幻读的时序图如下：

在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

因为这种特殊现象的存在，所以我们认为 MySQL Innodb 中的 MVCC 并不能完全避免幻读现象。

第二个发生幻读现象的场景

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。