这篇文章将介绍MySQL幻读相关的内容。

假设数据库中有以下表：

id c d
0 0 0
5 5 5
10 10 10
15 15 15
20 20 20
25 25 25

其中id是主键，c上建立有普通索引。

以下讨论场景除非必要说明，否则都是在可重复读的隔离级别下。

什么是幻读

现在考虑以下场景：

image-20230414141001291

其中，select * from table for update表示当前读，并且加上写锁。

幻读就是指在同一个事务中执行相同的sql，后一次查询看到了前一次没看到的数据。就比如Q3比Q2多了一条数据。

Q2看到Q1并不被称为幻读，幻读仅仅指新插入的数据。

在可重复读的隔离级别下，普通查询是看不到其他插入的数据，只有当前读（要能读到所有已经提交的记录的最新值）才有可能出现幻读。

幻读存在的问题

1、语义的破坏

session A已经声明了写锁，即要锁住d = 5的行，不允许其他事务进行读写，而后续却插入了一条d = 5的数据。

考虑另一个更明显的场景：

image-20230414143724913

sessionB将id = 0的这一行的d改成了5，然后又把c改成了5，也就是说变化为从（0，0，5）->（0，5，5）。这样破坏了sessionA要锁住所有d=5的行的加锁声明。

sessionC也是同样的道理，修改了d = 5，id = 1的行。

2、数据一致性问题

现在在sessionA追加一条语句：

image-20230414144337377

此时，sessionA要锁住d = 5的这一行，然后把d 改为100，即sessionA在T1要做的操作为将（5，5，5）-> (5，5，100)。

在T2时刻，id = 0这一行变成了（0，5，5）

在T4时刻，数据库多了一条（1，5，5）的记录。

但是我们看binlog的内容：

在T2时刻，sessionB提交，写入两条语句，两条update。

T4时刻，sessionC提交，写入两条语句，一条insert，一条update。

把上面的语句放在一起，顺序如下：

1
2
3
4
5
6
7
8
9
10
/* sessionB */
update t set d = 5 where id = 0; /*(0,0,5)*/
update t set c = 5 where id = 0; /*(0,5,5)*/

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

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

通过这个语句的顺序，如果我们要拿它来恢复数据，最终恢复出来的数据会变为： (0,5,100)、(1,5,100) 和 (5,5,100)。

而正确的数据应该是（0，5，5），（1，5，5）和（5，5，100）。因为在可重复读隔离级别下，事务A没提交，事务B和C看不到A的修改，而事务A在提交之前看不到B和C的修改。

这里的数据不一致是因为我们假设了select * from t where d=5 for update这条语句只给id = 5这一样加锁导致的。

现在我们假设把扫描过程中的所有行都加锁，场景如下：

image-20230414150914211

这个时候，id = 0的这一行在修改时，由于被加了锁，所以无法执行。也就是说现在的执行顺序如下：

1
2
3
4
5
6
7
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

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

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

此时，id = 0的这一行数据正确，但是新插入的id = 1的问题还是没有解决。

也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录，这也是为什么要单独解决幻读。

如何解决

即使我们在一开始就锁住所有行，但是由于插入语句是在行的间隙进行的，所以InnoDB的解决办法是引入了间隙锁(Gap Lock)。比如开头的表，有6行数据，7个间隙。

image-20230414151304834

这样，当你执行 select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。

间隙锁和间隙锁之间不冲突，两个事务可以在同一个间隙加间隙锁，与间隙锁冲突的是往间隙插入数据的操作。

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间，

间隙锁只有在可重复读的隔离级别下才有。而且引入间隙锁会降低并发度，并且有可能造成死锁。

另外一种解决幻读的方式是采用读提交的隔离级别加binlog_format=row。