MySql——幻读

幻读

所谓幻读，就是指一个事务在前后两次查询同一个范围的时候，后一次查询看到的前一次查询没有看到的行。
我们来看这样一个场景：创建一个表

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)

当执行下面语句时，会命中d=5这一行，并给改行加一个写锁，由于两阶段锁协议，这个锁会在执行commit语句的时候释放。
由于字段d上没有索引，因此这条语句会做全表扫描。其他被扫描到的，但是不满足d=5这个条件的，会不会加上锁呢？
比如执行下面的事务：

在读未提交的隔离级别下，上面sessionA在前后执行查询语句时，返回的结果并不相同，而这种多出几行的现象，被称为幻读
在可重复读隔离级别下，普通的查询都是快照读，是不会看到别的事务插入的数据的，因此，幻读只会在当前都下才会出现。
需要注意的是，在T2中sessionB的修改结果被sessionA之后的查询语句用“当前读”看到，并不能称为幻读，幻读仅专指“新插入的行”。

幻读有什么问题

从语义上，sessionA在T1时刻就声明了，要把所有的d=5的行锁住，不准别的事务进行读写操作，但事实上，按照上面的逻辑，这个语义被破坏了。
其次，幻读会导致数据不一致问题，锁的设计是为了保证数据的一致性，而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包括了数据和日志在逻辑上的一致性。
举个例子：执行下面的事务：
如果update和select…for update是一致的话，只会在d=5的语句加锁，那三个事务的执行过程如下：
1. 经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的 ;
2. 经过 T2 时刻，id=0 这一行变成 (0,5,5);
3. 经过 T4 时刻，表里面多了一行 (1,5,5);
4. 其他行跟这个执行序列无关，保持不变
这样看的话，这些数据并没有什么问题，但来看看binlog中记录的内容：
1. T2 时刻，session B 事务提交，写入了两条语句；
2. T4 时刻，session C 事务提交，写入了两条语句；
3. T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。
也就是下面这样：

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

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*/

当我们拿这个binlog去备库时，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。
也就是说，id=0和id=1发生了数据不一致的问题。

幻读问题如何解决

锁住所有行

其实只需要把扫描过程中遇到的行都加上写锁，由于sessionA把所有的行都加上写锁，需要在T6时刻提交事务后，sessionB才能执行，在binlog里的记录就会变成这样：

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=1这一行是新插入的，所以在sessionA加上写锁时，并没有对id=1这一行加上锁，这就导致了写入binlog时，id=1这一行的修改是在update t set d=100 where d=5;之前的，即id=1的数据不一致问题并没有得到解决。

间隙锁

现在我们知道了，产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的间隙。因此，为了解决幻读问题，InnoDB只好引入了间隙锁。
顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。
这样，当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 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]。
另外，间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。
所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。