MySQL可重复读下的幻读复现及原理不同的数据库厂商对SQL标准的实现不同。MySQL在RR隔离级别下，在一定程度下解决

SQL标准对于隔离级别的定义：
READ UNCOMMITTED隔离级别下，可能发生脏读、不可重复读和幻读问题。

READ COMMITTED隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。

REPEATABLE READ隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。

SERIALIZABLE隔离级别下，各种问题都不可以发生。

不同的数据库厂商对SQL标准的实现不同。MySQL在RR隔离级别下，在一定程度下解决了幻读问题

场景1

创建表：

CREATE TABLE `book` (
  `name` varchar(100) NOT NULL,
  `book_id` int(100) NOT NULL,
  PRIMARY KEY (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

打开两个SQL控制台，事务模式选择手动。

控制台1：

start transaction ;                                          # 1.1
select * from test.`book`;                                   # 1.2
insert into test.`book` values ('book1',1);                  # 1.3
update test.`book` set name = 'book2-1' where book_id = '2'; # 1.4
select * from test.`book`;                                   # 1.5
commit ;                                                     # 1.6

控制台2：

start transaction ;                                             # 2.1
select * from test.`book`;                                      # 2.2
insert into test.`book` values ('book2',2);                    # 2.3
update test.`book` set name = 'book2-1' where book_id = '2';    # 2.4
select * from test.`book`;                                      # 2.5
commit ;                                                        # 2.6

模拟：事务1开启，执行一条insert语句插入数据1，事务2开启，也插入一条数据2,事务2提交后在事务1中查询，发现不能查询到数据2

操作步骤：

在控制台1中执行1.1、1.2，此时查询数据为空，执行1.3、1.5,此时事务1插入了book1并能够在事务1中查询。
在控制台2中执行2.1、2.2，此时查询数据为空，执行2.3、2.5,此时事务2插入了book2并能够在事务2中查询。
在控制台2中执行2.6,此时事务2提交。在没有事务场景可以查询到book1、book2。
在控制台1中执行1.5,此时发现事务1中不能查询到book2,表明没有发生幻读。即其他事务新增的数据并未出现。

note：步骤1中需要插入book1,若只是执行select语句不会真正开启事务。

场景1还原了MVCC解决了幻读问题的场景，但有一种场景依然会发生幻读

场景2：

沿用场景1的SQL，模拟：事务1开启，执行一条insert语句插入数据1，事务2开启，也插入一条数据2,事务2提交后在事务1中先执行1.4，发现并能查询到数据2

操作步骤(1~4步同场景1)：

在控制台1中执行1.1、1.2，此时查询数据为空，执行1.3、1.5,此时事务1插入了book1并能够在事务1中查询。
在控制台2中执行2.1、2.2，此时查询数据为空，执行2.3、2.5,此时事务2插入了book2并能够在事务2中查询。
在控制台2中执行2.6,此时事务2提交。在没有事务场景可以查询到book1、book2。
在控制台1中执行1.5,此时发现事务1中不能查询到book2,表明没有发生幻读。
在控制台1中执行1.4,即修改了事务2中插入的新数据
控制台中执行1.5,发现可以查询到book2,发生了幻读！

即事务1修改了事务2中新增的数据，则后续可以查询到该数据，发生幻读

原理

对于快照读，在REPEATABLE READ隔离级别下，第一次查询时候生成快照，后续的查询均使用该快照。快照主要内容：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
creator_trx_id：表示生成该ReadView的事务的事务id。

数据库记录在存储时基于undolog生成了由有不同版本号的数据组成的数据链，在记录的数据中除了业务数据外还包括很重要的一列：事务id trx_id。在查询数据时，会根据事务id由大到小的顺序，基于特定算法找出事务id最大的一条返回，算法为：

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

结合场景2,进行到步骤4时，book2的trx_id大于事务1的id，对比算法可知不会被查询到。但进行步骤5后，book2的trx_id被修改为事务1的id，因此事务1后续可以查询到book2了。

怎么解决？

尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

参考

MySQL 是怎样运行的：从根儿上理解 MySQL