mysql中幻读出现的原因及解决方案

待我称王封你为后i 2023-01-15 10:23 287阅读 0赞

今天分享 mysql中幻读出现的原因及解决方案：

一、首先明确什么是幻读：  事务A按照一定条件进行数据读取，期间事务B插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取操作修改时，发现了事务B新插入的数据称之为幻读。

二、幻读出现的场景：

1、如果事务中都是用快照读，那么不会产生幻读的问题

2、快照读和当前读一起使用的时候就会产生幻读

三、实验验证

1、采用mysql 5.6之后的版本和 默认的隔离级别 RR ，启动A、B两个事务对比，阿拉伯数字递增代表事务执行的时间顺序，比如 1,2,3,4.......，模拟数据库执行(前提是数据库有两条数据)，

假设有如下业务场景：

| 时间 | 事务1                                                                                                        | 事务2                                               |  
| ---- | --------------------------------------------------                                              ---------- | ------------------------------------------- |  
|      | begin；                                                                                               |                                                                 |  
| T1   | select \* from user where age = 20;2个结果                                       |                                                               |  
| T2   |                                                                                                            | insert into user values(25,'25',20);commit; |  
| T3   | select \* from user where age =20;2个结果                                        |                                                                    |  
| T4   | update user set name='00' where age =20;此时看到影响的行数为3 |                                                                  |  
| T5   | select \* from user where age =20;三个结果                                        |                                                                 |

2、执行流程如下：

1)、T1时刻读取年龄为20 的数据，事务1拿到了2条记录

2)、T2时刻另一个事务插入一条新的记录，年龄也是20

3)、T3时刻，事务1再次读取年龄为20的数据，发现还是2条记录，事务2插入的数据并没有影响到事务1的事务读取

4)、T4时刻，事务1修改年龄为20的数据，发现结果变成了三条，修改了三条数据

5)、T5时刻，事务1再次读取年龄为20的数据，发现结果有三条，第三条数据就是事务2插入的数据，此时就产生了幻读情况

此时大家需要思考一个问题，在当下场景里，为什么没有解决幻读问题？

其实通过前面的分析，大家应该知道了快照读和当前读，一般情况下select \* from ....where ...是快照读，不会加锁，而 for update,lock in share mode,update,delete都属于当前读，\*\*如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读\*\*。

3、模型结果如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmRhbzE1OA_size_16_color_FFFFFF_t_70][]

分析，select 执行的是快照读（某个版本的数据，Read View）,而update 执行的是当前读（最新的数据，即最新的Read View,因此更新了三条数据）。这就是幻读的场景，它是不可重复读的一个子类。

3、怎么解决幻读：加间隙锁（如果当前和快照读均存在的情况下）。采用mysql 5.6之后的版本和 默认的隔离级别 RR ，启动A、B两个事务对比，阿拉伯数字递增代表事务执行的时间顺序，比如 1,2,3,4.......，模拟数据库执行(前提是数据库有两条数据)

结果模型如下图：

![2021042214144266.png][]

此时，可以看到事务B被阻塞了，需要等待事务A提交事务之后才能完成，其实本质上来说采用的是间隙锁的机制解决幻读问题,因此可以发现，MVCC + 锁 共同实现隔离级别。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25hbmRhbzE1OA_size_16_color_FFFFFF_t_70]: /images/20221022/4ad3b9321bba4e49829b7e7c61ebd117.png
[2021042214144266.png]: /images/20221022/edfa9226c8fd45fd921355d185329702.png