mysql45讲：讲幻读是什么，幻读有什么问题

一. 幻读是什么

       幻读的意思就是说在可重复读的隔离级别下会出现的一种情况.

      我的理解是如下图

 因为加上for update 所以现在的sql语句是当前读,所以现在每次在sessionA中都会将其他两个事务做的操作后的后果读出来,但是我们使用重复读就是想可以通过快照重复读原先的数据.但是现在的却出现了我们不想产生的现象,我们将这种现象称为幻读.

二. 幻读会产生什么问题

如果只是读错数据其实都还好,但是看下图,其实是会产生更加严重的后果的.

单独看好像除了会读出一些不属于此事务的数据之外不会产生其他的影响.

但是我们将binlog加入进来考虑一下的话,那么就不一样了,就是我们来写一下过程

 T1 事务a开启，（5,5,5）变为（5,5,100）

T2 事务b开启       （0,0,0）变为（0,5,5）

T4事务c开启        加入一条（1,5,5）

但是binlog里是如何呢？

事务B先结束，binlog里存两条

会让（0,0,0）变成（0,5,5），

然后事务c结束 binlog再存两条

会让数据库里多一条数据 （1,5,5）

然后现在事务a才结束 binlog再存两条

会让（0,5,5）变为（0，5，100）

（1,5,5）变为（1,5,100）

到这已经使得logbin和数据库里的数据不一样了。

那么我们这边一开始想到的策略就是给原先的所有行加上行锁，

但是这真的有用吗？

 我们像像上面一样来推

  

三. 如何解决幻读

那么到底如何解决幻读带来的数据冲突呢

InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

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

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

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

在前面的文章中，就有同学提到了这个问题。我把他的问题转述一下，对应到我们这个例子的表来说，业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：

begin;
select * from t where id=N for update;

/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set d=N set id=N;

commit;

可能你会说，这个不是insert ... on duplicate key update 就能解决吗？但其实在有多个唯一键的时候，这个方法是不能满足这位提问同学的需求的。至于为什么，我会在后面的文章中再展开说明。

现在，我们就只讨论这个逻辑。

这个同学碰到的现象是，这个逻辑一旦有并发，就会碰到死锁。你一定也觉得奇怪，这个逻辑每次操作前用for update锁起来，已经是最严格的模式了，怎么还会有死锁呢？

这里，我用两个session来模拟并发，并假设N=9。

图8 间隙锁导致的死锁

你看到了，其实都不需要用到后面的update语句，就已经形成死锁了。我们按语句执行顺序来分析一下：

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。这，也是现在不少公司使用的配置组合。

前面文章的评论区有同学留言说，他们公司就使用的是读提交隔离级别加binlog_format=row的组合。他曾问他们公司的DBA说，你为什么要这么配置。DBA直接答复说，因为大家都这么用呀。

所以，这个同学在评论区就问说，这个配置到底合不合理。

关于这个问题本身的答案是，如果读提交隔离级别够用，也就是说，业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

但其实我想说的是，配置是否合理，跟业务场景有关，需要具体问题具体分析。

但是，如果DBA认为之所以这么用的原因是“大家都这么用”，那就有问题了，或者说，迟早会出问题。

比如说，大家都用读提交，可是逻辑备份的时候，mysqldump为什么要把备份线程设置成可重复读呢？（这个我在前面的文章中已经解释过了，你可以再回顾下第6篇文章《全局锁和表锁 ：给表加个字段怎么有这么多阻碍？》的内容）

然后，在备份期间，备份线程用的是可重复读，而业务线程用的是读提交。同时存在两种事务隔离级别，会不会有问题？

进一步地，这两个不同的隔离级别现象有什么不一样的，关于我们的业务，“用读提交就够了”这个结论是怎么得到的？

如果业务开发和运维团队这些问题都没有弄清楚，那么“没问题”这个结论，本身就是有问题的。