从幻读看MySQL：读取方式解读与解决方案

本文为《MySQL归纳学习》专栏的第十七篇文章，同时也是关于《MySQL锁机制》知识点的第二篇文章。

相关文章：

锁的三重奏：MySQL全局锁、表级锁与行锁详解

在数据库的世界里，读取方式的选择和幻读的处理是至关重要的。本文将详细解读MySQL的三种不同读取方式：一致性读、当前读和半一致性读，每一种都有其特点和适用场景。同时，我们也会深入探讨幻读的本质，分析它可能带来的问题，以及MySQL是如何巧妙地解决幻读问题的。我们希望这篇文章可以帮助您更好地理解MySQL的读取机制，并在实际应用中避免幻读带来的问题。

并发事务存在的问题，其中包括幻读，而 MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读），下面我们学习该隔离级别是如何解决幻读的。

假设有如下表结构：

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

执行如下语句

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

在真正讲解幻读前，我们先了解一下 MySQL 读取数据的三种不同方式。

MySQL读取方式

快照读

1. 适用于隔离级别：快照读是在隔离级别为"repeatable read"或"read committed"下的一种读取方式。
2. 一致性：快照读也使用事务开始时的快照视图，保证事务内查询结果的一致性。不同事务之间的读取操作可能使用不同的快照视图，因此可以读取到其他事务已提交的数据变化。
3. 锁定行：快照读会在读取的过程中对被读取的行进行共享锁定，以避免其他事务修改这些行。这样可以保证读取的数据在整个事务期间的一致性。

简单来说：快照读指的是在语句执行之前或者在事务开始的时候会创建一个一致性视图，后面的读都是基于这个视图的，不会再去查询最新的值。

当前读

1. 适用于隔离级别：当前读是一种实时获取最新数据的读取方式，适用于任何隔离级别。
2. 一致性：当前读会直接读取数据库中的最新数据，而不使用一致性视图。这意味着在并发环境下，当前读可能会读取到其他事务尚未提交的数据变化，从而可能出现脏读（读取到未提交的数据变化）或不可重复读（同一事务内两次读取的数据不一致）的情况。
3. 锁定行：当前读会对被读取的行进行共享锁定，以防止其他事务对这些行进行修改。这样可以保证读取的数据在当前事务中的一致性。

简单来说：当前读指的是 select for update 或者 select in share mode，指的是在更新之前必须先获取到当前的最新值，因此叫当前读。

半一致性读

1. 适用于隔离级别：半一致读是InnoDB存储引擎在默认隔离级别（repeatable read）下的一种读取方式，或者基于 RR隔离级别，且innodb_locks_unsafe_for_binlog=true
2. 一致性：半一致读会在事务开始时创建一个一致性视图，该视图反映了事务开始时刻数据库的数据状态。在整个事务期间，读取操作都会使用这个一致性视图，以保证事务内部查询结果的一致性。
3. 并发性能：半一致读不会对被读取的行进行锁定，允许其他事务同时修改被读取的行，从而提供了较好的并发性能。

执行以下命令，查看状态：

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set, 1 warning (0.10 sec)

innodb_locks_unsafe_for_binlog

• innodb_locks_unsafe_for_binlog 默认为 off，即启用 gap 锁。
• 如果设置为1，会禁用 gap 锁，但对于外键冲突检测（foreign-key constraint checking）或者重复键检测（duplicate-key checking）还是会用到 gap 锁。
• 启用 innodb_locks_unsafe_for_binlog 产生的影响等同于将隔离级别设置为RC，不同之处是：

1）innodb_locks_unsafe_for_binlog 是全局参数，影响所有session；但隔离级别可以是全局也可以是会话级别。

2）innodb_locks_unsafe_for_binlog 只能在数据库启动的时候设置；但隔离级别可以随时更改。 基于上述原因，RC 相比于 innodb_locks_unsafe_for_binlog 会更好更灵活。

启用innodb_locks_unsafe_for_binlog还有以下作用：

• 对于update或者delete语句，InnoDB只会持有匹配条件的记录的锁。在MySQL Server过滤where条件，发现不满足后，会把不满足条件的记录释放锁。这可以大幅降低死锁发生的概率。

详细讲解推荐阅读：RR与RC隔离级别下MySQL不同的加锁解锁方式

具体实现：

此时有两个Session，Session1和Session2(navicat上连接的同一用户开启的不同窗口是不同的session) Session1执行

update test set color = 'black' where color = 'white';

先不Commit事务；

与此同时Ssession2执行

update test set color = 'white' where color = 'black';

在 RC 隔离级别下，session 2尝试加锁的时候，发现行上已经存在锁*，*InnoDB会开启semi-consistent read，返回最新的committed版本(1,balck),(2，white),(3,balck),(4,white)。MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。

而在RR隔离级别下，Session2只能等待。

明显用RC级别可以提高数据库的性能，还有一个问题，不可重复读的问题不用解决吗？是不用的，读取前后不一致的问题是可以接受的，毕竟你的数据都已经提交了。

幻读是什么？

如果只在 id=5 这一行加锁，而其他行的不加锁的话，会怎么样。

begin;
select * from t where c=5 for update;

update t set c=5 where id=0;

insert into t values(1,5,1);

注意，事务A 中的查询都说当前读，而非一致性读（快照读）。

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

对“幻读”做如下说明：

• 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
• 上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

幻读有什么问题？

1、语义被破坏

session A 在 T1 时刻就声明了，“我要把所有 c=5 的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。

session B 的第二条语句 update t set d=5 where id=0，语义是“我把 id=0、c=5 这一行的 d 值，改成了 5”。

由于在 T1 时刻，session A 还只是给 id=5 这一行加了行锁， 并没有给 id=0 这行加上锁。因此，session B 在 T2 时刻，是可以执行这两条 update 语句的。这样，就破坏了 session A 里 Q1 语句要锁住所有 c=5 的行的加锁声明。

2、数据无法保存一致性

锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。

上述三个事务执行完毕后，结果如上图中的 result 所示。

这些 insert、update 语句执行完毕后，我们看下 binlog 的记录

update t set c=5 where id=0; /*(0,5,0)*/
update t set d=5 where id=0; /*(0,5,5)*/

insert into t values(1,5,1); /*(1,5,1)*/
update t set d=5 where id=1; /*(1,5,5)*/

update t set c=100 where c=5;/*所有c=5的行，c改成100*/

好，你应该看出问题了。这个语句序列，不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，这三行的结果，都变成了 (0,100,5)、(1,100,5) 和 (5,100,5)。

也就是说，id=0 和 id=1 这两行，发生了数据不一致。这个问题很严重，是不行的。

即使把所有的行记录都加上锁，那也只能阻塞 session B 的执行，因为 sessionC 中做新增操作，是无法被阻塞的。所以该怎么解决幻读呢？

解决幻读

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

行锁的冲突关系

但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

间隙锁和行锁合称 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]。 注意，next-key lock 是前开后闭区间，但间隙锁是开区间。

间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。

看如下场景

语句执行逻辑如下：

1. session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
2. session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
3. session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
4. session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。

间隙锁存在于主键索引和二级索引，有间隙的地方就可能有间隙锁。