MySQL：图解MVCC到底能不能解决幻读问题？

绝地灬酷狼 2023-10-02 11:49 21阅读 0赞

### 背景 ###

大家对MVCC能不能解决幻读问题各持己见，都有一套自己的说辞；最骚的是有些面试官就认为它不能，你说能他就认为你说的不对。那么到底能不能呢？我们今天从各个方面去深度分析一把：什么情况下能，什么情况不能。

### 验证 ###

我们的验证基于MySQL InnoDB引擎的默认事务隔离级别`REPEATABLE-READ`。  
**查询事务隔离级别的方式：**

#### 一、前戏 ####

开始之前，我们先把自动提交事务给关了。  
**（1）查询是否开启了自动提交事务：**

从代码来看，MySQL是默认开始自动提交事务的。

**（2）关闭自动提交事务：**

*  值为 0 和值为 OFF：关闭事务自动提交。如果关闭自动提交，用户将会一直处于某个事务中，只有提交或回滚后才会结束当前事务，重新开始一个新事务。
 *  值为 1 和值为 ON：开启事务自动提交。如果开启自动提交，则每执行一条 SQL 语句，事务都会提交一次。

#### 二、创建数据库和表结构 ####

**（1）创建test数据库：**

create database test;

**（2）创建person表：**

CREATE TABLE `person` (
      `id` int(11) NOT NULL,
      `name` varchar(32) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

**（3）基础数据准备：**

insert into person(id, name, age) values(1, "a", 18);
    insert into person(id, name, age) values(2, "b", 19);
    insert into person(id, name, age) values(5, "e", 19);

#### 三、图解幻读问题流程 ####

我们开启两个MySQL窗口：左边的是事务A，右边的是事务B。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16]  
**（1）先分别开启事务，然后执行一次快照读操作**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
**（2）事务B中插入一条记录，但不提交事务；事务A、事务B中再分别执行一次读操作**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
此时我们发现，事务B中插入一条数据，事务A中并看不到这条数据，因为在可重复读事务隔离级别下，`数据快照`只在`事务开始的时候创建`。也就是`事务A`执行的还是`快照读`，`事务B`此时因为执行了inset操作，所以是针对id=3这行数据是`当前读`，本质上还是ReadView的可见性规则决定。

**（3）我们在事务A中修改ID为1的数据，然后再执行一次读操作**  
上面我们发现当执行当前读之后，事务中可以获取到最新的数据。那我们在事务A中更新ID为1的记录之后，是不是也可以看到在事务B（未提交）中新插入的那条ID为3的记录？  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 3]  
咦，不对呀，还是看不到ID为3的那条数据。什么情况！！！  
其实我们可以发现第(2)步中事务B的当前读是针对ID=3的那条记录；而在当前步骤中，事务A的当前读是针对id=1的记录（ReadView可见性规则决定），对其他记录依旧是快照读，即此处依旧是快照读。所以我们看不到ID=3的那条记录，即`不会出现幻读问题`。

**那么问题来了：怎么会出现幻读呢？**  
**（4）我们在事务A修改age为19的数据，然后再执行读操作**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 4]

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

咦，怎么更新不了啊。我看看一下报错，它的意思是我们获取不到锁。因为事务B还没有commit，锁被它占用了。我们把事务B commit即可。

**（5）提交事务B，再在事务中修改age为19的数据，接着再执行读操作**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 5]  
因为在事务B（已提交）中插入记录的age为19，所以我们在事务A中更新完age为19的记录，会针对age为19的数据进行当前读。此时我们在事务A中可以看到ID=3这条数据，也就`出现了幻读`问题。

**（6）事务A提交，再执行查询操作**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 6]  
两个事务结束。

#### 四、命令时间轴 ####

<table> 
 <thead> 
  <tr> 
   <th>时间</th> 
   <th>事务1</th> 
   <th>事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>begin；</td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>T1</td> 
   <td>select * from person;3个结果</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td></td> 
   <td>insert into person(id, name, age) values(3,‘saint’,19);commit;</td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td>select * from person;3个结果</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td>update person set name=‘aaaa’ where age =19;此时看到影响的行数为3</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T5</td> 
   <td></td> 
   <td>commit;</td> 
  </tr> 
  <tr> 
   <td>T6</td> 
   <td>select * from pereson;4个结果</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

### 结论 ###

如果`事务中都是用快照读`，那么`不会产生幻读`的问题；  
但是快照读和当前读一起使用的时候就会产生幻读。

### 幻读的解决方案 ###

**（1）使用串行化(SERIERLIZED)事务隔离级别**

set session transaction isolation level serializable;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 7]

**（2）采用间隙锁的机制**  
即select时加锁：select … for update;

<table> 
 <thead> 
  <tr> 
   <th>时间</th> 
   <th>事务1</th> 
   <th>事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>begin；</td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>T1</td> 
   <td>select * from person where age =19 for update;2个结果</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td></td> 
   <td>insert into person(id, name, age) values(3,‘saint’,19);<br><strong>此时会阻塞等待锁</strong></td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td>select * from person;2个结果</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

### 设置隔离级别 ###

**用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：**

SET \[SESSION | GLOBAL\] TRANSACTION ISOLATION LEVEL \{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE\}  
注意：默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

**查询全局和会话事务隔离级别：**

*  select @@global.transaction\_isolation;
 *  select @@session.transaction\_isolation;

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ce912a8e5d9e46c49f93da3d874fc040.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qKm5Zue546W5p-S,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/c577459f94d84f07a8104142556093b9.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qKm5Zue546W5p-S,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/a6ecf0d68c4745ba811c742d5216262e.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qKm5Zue546W5p-S,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/d2a875937ebd4d32b7d639b25bfb3327.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qKm5Zue546W5p-S,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/3616d4f190714dc49a583405c1595d8c.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qKm5Zue546W5p-S,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/310abc85a59a4c1b9f8eb5734a9145b9.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qKm5Zue546W5p-S,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://img-blog.csdnimg.cn/445fb7b1f7f94341ac07be54e1ebe106.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qKm5Zue546W5p-S,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm5Zue546W5p-S_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://img-blog.csdnimg.cn/014bd88d7a4043f1a7668b63c6e830ff.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5qKm5Zue546W5p-S,size_20,color_FFFFFF,t_70,g_se,x_16