Mysql在可重复读事务隔离级别下怎么解决幻读的

喜欢ヅ旅行 2024-04-03 12:24 53阅读 0赞

#### 目录 ####

*  前言
 *  并发事务产生的问题
 *   *  更新丢失
     *   *  回滚丢失
         *  覆盖丢失
     *  脏读
     *  不可重复读
     *  幻读
 *  快照读和当前读
 *  幻读验证
 *   *  快照读如何避免幻读
     *  当前读如何避免幻读
     *  可重复读隔离级别发生幻读情况
 *  小结

## 前言 ##

Mysql在可重复读(REPEATABLE READ)隔离级别下，如何解决部分幻读问题?本文将对这个问题进行探究。

## 并发事务产生的问题 ##

先创建一张用户表，用作数据验证：

CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(20) DEFAULT NULL COMMENT '姓名',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB COMMENT='用户表';

事务并发处理可能会带来一些问题，比如：更新丢失、脏读、不可重复读、幻读等。

*  更新丢失：

当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖。

> 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。  
> 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。

*  脏读： 一个事务读取到了另一个事务修改但未提交的数据。
 *  不可重复读： 一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致。
 *  幻读： 一个事务中多次按相同条件查询，结果不一致。后续查询的结果和面前查询结果不同，多了或少了几行记录。  
    ![在这里插入图片描述][f0a694fb63384266bba4d4ce84a791d3.png]

### 更新丢失 ###

#### 回滚丢失 ####

> 一个事务回滚操作，把其他事务已提交的数据给覆盖了。

<table> 
 <tbody> 
  <tr> 
   <td colspan="1"> <p>开启事物A</p> </td> 
   <td colspan="1"> <p>开启事物B</p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p>查询id = 1的数据name= 张三</p> </td> 
   <td colspan="1"> <p></p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p></p> </td> 
   <td colspan="1"> <p>更新id为1的数据保存name= 李四</p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p>更新id为1的数据保存name= 王二</p> </td> 
   <td colspan="1"> <p>提交事物</p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p>回滚事物</p> </td> 
   <td colspan="1"> <p></p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p>name恢复张三，更新丢失</p> </td> 
   <td colspan="1"> <p></p> </td> 
  </tr> 
 </tbody> 
</table>

**在MySQL数据库，任何隔离级别不会出现第一类更新丢失**。

#### 覆盖丢失 ####

> 一个事务提交操作，把其他事务已提交的数据给覆盖了。

<table> 
 <tbody> 
  <tr> 
   <td colspan="1"> <p>开启事物A</p> </td> 
   <td colspan="1"> <p>开启事物B</p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p>查询id = 1的数据name= 张三</p> </td> 
   <td colspan="1"> <p></p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p></p> </td> 
   <td colspan="1"> <p>更新id为1的数据保存name= 李四</p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p>更新id为1的数据保存name= 王二</p> </td> 
   <td colspan="1"> <p>提交事物</p> </td> 
  </tr> 
  <tr> 
   <td colspan="1"> <p>提交事物</p> </td> 
   <td colspan="1"> <p>name被修改为王二，更新丢失</p> </td> 
  </tr> 
 </tbody> 
</table>

### 脏读 ###

> 一个事务读到其他事务未提交的数据。

<table> 
 <thead> 
  <tr> 
   <th>事物A</th> 
   <th>事物B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>第一次查询：select name from user where id =1;结果：张三</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>update user set name=‘李四’ where id = 1;</td> 
  </tr> 
  <tr> 
   <td>第二次查询：select name from user where id =1;结果：李四</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>comit;</td> 
  </tr> 
 </tbody> 
</table>

### 不可重复读 ###

> 一个事务读取到其他事务修改过的数据。

<table> 
 <thead> 
  <tr> 
   <th>事物A</th> 
   <th>事物B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>第一次查询：select name from user where id =1;结果：张三</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>update user set name=‘李四’ where id = 1;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>comit;</td> 
  </tr> 
  <tr> 
   <td>第二次查询：select name from user where id =1;结果：李四</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

从上面的示例中，可以看出，在事务B修改完数据，并提交事务后。事务A第二次查询已经读到事务B最新修改的数据，这种情况就属于不可重复读。

### 幻读 ###

> 一个事务读取到其他事务最新插入的数据。

<table> 
 <thead> 
  <tr> 
   <th>事物A</th> 
   <th>事物B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>第一次查询：select name from user where id &gt;0;结果：张三</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into user (name) values(‘李四’) ;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>comit;</td> 
  </tr> 
  <tr> 
   <td>第二次查询：select name from user where id &gt;0;结果：张三 李四</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

## 快照读和当前读 ##

> 快照读： 读取数据的历史版本，不对数据加锁。

例如：

select

> 当前读： 读取数据的最新版本，并对数据进行加锁。

例如：

insert、update、delete、select for update、select lock in share mode

## 幻读验证 ##

**MySQL在Repeatable Read（可重复读）隔离级别下，有没有解决幻读的问题？**

> 部分解决了幻读问题。

事务查询和设置：

#查询全局事务隔离级别
    show global variables like '%isolation%';  
    SELECT @@global.tx_isolation;
    #设置全局事务隔离级别 
    set global transaction isolation level REPEATABLE READ;
    #设置完成后，只对之后新起的 session 才起作用，对已经启动 session 无效。
    
    #查询会话事务隔离级别
    show session variables like '%isolation%';
    SELECT @@session.tx_isolation; 
    SELECT @@tx_isolation;
    #设置会话事务隔离级别
    set session transaction isolation level REPEATABLE READ;

### 快照读如何避免幻读 ###

<table> 
 <thead> 
  <tr> 
   <th>事物A</th> 
   <th>事物B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>第一次查询：select name from user where id &gt;0;结果：张三</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into user (name) values(‘李四’) ;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>comit;</td> 
  </tr> 
  <tr> 
   <td>第二次查询：select name from user where id &gt;0;结果：张三</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

可重复读隔离级是由 MVCC（多版本并发控制）实现的，从上面的示例中，可以看出，事务A的两次查询，得到的结果一致，并没有查到事务B最新插入的数据。原因是，第一次快照读的时候，生成了一个`读视图(Read View)`。后续的查询语句通过这个 `Read View` 就可以在 `undo log 版本链`找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，所以两次查询得到的结果一致。

**undo log**

> undo log是InnoDB的事务日志。undo log是回滚日志，记录的是行数据的修改记录，即哪些行被修改成怎样，提供回滚操作。事务的操作记录会被记录到undo log中，用于事务进行回滚操作。

**版本链**  
在InnoDB中，每个行记录都隐藏着两个字段：

> 1）trx\_id：事务id。该字段用于记录修改当前行记录的事务的id。  
> 2）roll\_pointer：回滚指针。该字段用于记录修改当前行记录的undo log地址。

**ReadView主要包括四个部分：**

> 1.  m\_ids:表示在生成ReadView时当前系统中活跃的读写事务的id。
> 2.  min\_trx\_id:表示在生成ReadView时当前系统中活跃的读写事务的最小id。
> 3.  max\_trx\_id:表示在生成ReadView时系统应该分配给下一个事务的id值。
> 4.  creator\_trx\_id:表示生成该ReadView的事务的id。

**读已提交在每次读数据前都会生成一个ReadView，这样可以保证每次都能读到其他事务已提交的数据。可重复读只在第一次读取数据时生成一个ReadView，这样就能保证后续读取的结果一致。**

### 当前读如何避免幻读 ###

#事物A
    begin;
    select * FROM user1 WHERE id > 0 for update;
    SELECT SLEEP(20);
    select * FROM user1 WHERE id > 0 for update;

#事物B
    BEGIN;
    insert into user1(name) VALUES ('三省同学');
    commit;

<table> 
 <thead> 
  <tr> 
   <th>事物A</th> 
   <th>事物B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>第一次查询：select name from user where id &gt;0 for update;结果：张三</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into user (name) values(‘三省同学’) ;【被阻塞】</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>comit;</td> 
  </tr> 
  <tr> 
   <td>第二次查询：select name from user where id &gt;1 for update;结果：张三</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

**Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁。读提交隔离级别，是没有间隙锁的，只有记录锁。**

> next-key lock = 间隙锁+记录锁

### 可重复读隔离级别发生幻读情况 ###

示例1：

<table> 
 <thead> 
  <tr> 
   <th>事物A</th> 
   <th>事物B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td>第一次查询：select name from user where id &gt;0;结果：张三</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into user (name) values(‘三省同学’) ;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>comit;</td> 
  </tr> 
  <tr> 
   <td>第二次查询：select name from user where id &gt;1 for update;结果：张三 三省同学</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

第二次获取当前最新数据，整个事务也发生了幻读。

示例2：

<table> 
 <thead> 
  <tr> 
   <th>事物A</th> 
   <th>事物B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>第一次查询：select name from user where id &gt;0;结果：张三</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into user (id, name) values(2, ‘三省同学’) ;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>comit;</td> 
  </tr> 
  <tr> 
   <td>update user set name = ‘三省同学’ WHERE id =2;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>第二次查询：select name from user where id &gt;1;结果：张三 三省同学</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 2的记录并提交。接着，事务 A 对 id =2这条记录进行了更新操作，在这个时刻，这条新记录的 trx\_id 隐藏列的值就变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

## 小结 ##

MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级）下避免幻读：

> 快照读通过 MVCC 方式解决了幻读。  
> 当前读通过 next-key lock解决了幻读。

![在这里插入图片描述][5e1b731011d04f1b8b5bb34319048090.png]

点赞 收藏 关注  
踔厉奋发，勇毅前行

[f0a694fb63384266bba4d4ce84a791d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/c6063ba32ca44b659a7c8fa901346e02.png
[5e1b731011d04f1b8b5bb34319048090.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/e85d1664db114e86bbed586c8fac8083.png