mysql数据库事务隔离级别

蔚落 2023-02-22 10:52 21阅读 0赞

### 文章目录 ###

*   *   *  一.事务的四个特征(ACID)
         *  二.事务隔离级别
         *   *  1.Read uncommitted(读未提交)
             *  2.Read committed(读已提交)
             *  3.Repeatable read(可重复读)
             *  4.Serializable(可串行化)
         *  三.深入理解事务隔离
         *   *  1.基本概念
             *   *  ①版本链
                 *  ②read view
                 *  ③ innodb中的锁
                 *  ④行级锁的三种算法
                 *  ⑤快照读和当前读
                 *  ⑥LBCC和MVCC
             *  2.具体事务隔离级别分析
             *   *  ①Read uncommitted分析
                 *  ②Read committed分析
                 *  ③Repeatable read分析
                 *  ④Serializable分析

### 一.事务的四个特征(ACID) ###

*  **原子性(Atomicity):** 事务不可分割，要么全部发生，要么全部不发生。  
    eg:A给B转钱，A少了钱，B多了钱。两个都要发生，不能只发生一个。
 *  **一致性(Consistency):** 事务操作前后的状态要一致。  
    eg:A给B转钱200，A少了200，B多了200，不能多300或者其它数字。
 *  **隔离性(Isolation):** 事务之间不能影响,相互隔离。  
    eg:A给B转钱和C给B转钱是分别两个事务，不能相互影响。
 *  **持久性(Durability):** 事务被提交到数据库中了，就是永久的。数据库发生故障也不应该对它有影响.  
    eg:A给B转钱200，事务提交后，B多了200，数据库故障了再回复后B依然要是多200。

### 二.事务隔离级别 ###

![在这里插入图片描述][20200703112338659.png]  
常用命令:

1.  查看当前session数据库隔离级别 (@代表用户自定义变量,@@代表系统变量 ([mysql @和@@][mysql]))

select @@transaction_isolation;

1.  修改当前session数据库隔离级别

set session transaction isolation level xxx;

1.  开始事务

start transaction;

1.  提交事务

commit;

1.  关闭事务自动提交

set autocommit = 0;

#### 1.Read uncommitted(读未提交) ####

所有事务都可以看到其他事务未提交的事务的执行结果。  
如A,B事务，B事务插入一条数据，未提交，A事务就可以读到该未提交的数据，如果B事务回滚后，该未提交的数据不存在了，A事务刚刚读到的数据就是’脏数据’,这一现象也叫`脏读`。

<table> 
 <thead> 
  <tr> 
   <th align="center">事务A</th> 
   <th align="center">事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><strong>1.设置事务隔离等级为read uncommited</strong><img src="https://img-blog.csdnimg.cn/20200703103232214.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center"><strong>2.开启事务，插入一条数据并且不提交事务</strong><img src="https://img-blog.csdnimg.cn/20200703103459674.png" alt="在这里插入图片描述"></td> 
  </tr> 
  <tr> 
   <td align="center"><strong>3.未提交读隔离等级下读到了该未提交的数据</strong><img src="https://img-blog.csdnimg.cn/20200703103649507.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center"><strong>4.将刚刚未提交的数据回滚</strong><img src="https://img-blog.csdnimg.cn/20200703103908565.png" alt="在这里插入图片描述"></td> 
  </tr> 
  <tr> 
   <td align="center"><strong>5.刚刚读到的数据消失(读到了<code>脏数据</code>)</strong><img src="https://img-blog.csdnimg.cn/20200703104022826.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

#### 2.Read committed(读已提交) ####

大多数的数据库默认隔离等级为此等级，如Oracle、Sql Server。该隔离等级下读不到未提交的事务的执行结果，不会发生`脏读`。  
但是如果A开启了事务，第一次读到了数据，在这个时候，事务B修改了这条数据并提交了事务，事务A再读，发现读到了事务B修改的数据结果，这样导致了一个事务内读同一条数据是不同的结果，这种现象就叫做`不可重复读`。

<table> 
 <thead> 
  <tr> 
   <th align="center">事务A</th> 
   <th align="center">事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><strong>1.设置事务隔离级别为read committed</strong><img src="https://img-blog.csdnimg.cn/20200703110747974.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"><strong>2.开启事务读取到一条数据</strong> <img src="https://img-blog.csdnimg.cn/20200703111106674.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center"><strong>3.开启事务将该条数据修改并提交</strong><img src="https://img-blog.csdnimg.cn/20200703111424833.png" alt="在这里插入图片描述"></td> 
  </tr> 
  <tr> 
   <td align="center"><strong>4.一个事务下读取相同数据，数据却发生了变化(<code>不可重复读</code>)</strong><img src="https://img-blog.csdnimg.cn/20200703111709846.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

#### 3.Repeatable read(可重复读) ####

`mysql默认采用该隔离级别`.该隔离级别下，在一个事务内，即使有事务修改了数据并且提交，它也不会读到刚刚修改的数据，而是原来的数据，不会发生`不可重复读`。  
但是如果A开启事务，第一次读到了数据，在这个时候B开启事务，插入了一条数据，事务A再次读取数据，会发现刚刚插入的数据也被读取到了,导致一个事务内读取的数据数量发生了变化，像是产生了幻觉一样，这种现象就叫`幻读`。(`幻读`与`不可重复读`有点类似，不同的是幻读主要是做**增加**或者**删除**操作,产生了新数据，数据数量发生了变化。`不可重复读`则是做**修改**操作，是原来的某条数据发生了变化)

<table> 
 <thead> 
  <tr> 
   <th align="center">事务A</th> 
   <th align="center">事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><strong>1.mysql默认隔离级别为repeatable read</strong> <br> <img src="https://img-blog.csdnimg.cn/20200703114051546.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"><strong>2.开启事务，读取数据</strong><br><img src="https://img-blog.csdnimg.cn/2020070311492771.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center"><strong>3.开启事务，插入一条数据，并提交事务</strong><img src="https://img-blog.csdnimg.cn/20200703114701759.png" alt="在这里插入图片描述"></td> 
  </tr> 
  <tr> 
   <td align="center"><strong>4.再次读取数据，发现并没有读到刚刚插入的数据</strong><img src="https://img-blog.csdnimg.cn/20200703115022965.png" alt="在这里插入图片描述"><br>这里并没有发生<code>幻读</code>，原因之后再解释</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"><strong>5.加锁的方式再次读取数据，读到了刚刚插入的数据(<code>幻读</code>)</strong> <br><img src="https://img-blog.csdnimg.cn/20200703124920944.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

#### 4.Serializable(可串行化) ####

可串行化是最高的事务隔离等级。事务隔离等级其实就是并发与事务的权衡。在这个隔离等级下，事务很严格，每个操作都会加锁,不会出现`脏读`、`不可重复读`、`幻读`的现象。虽然效率可能低一点但是用于事务性很强的情况，比如银行业务。

<table> 
 <thead> 
  <tr> 
   <th align="center">事务A</th> 
   <th align="center">事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><strong>1.设置事务隔离等级为Serializable</strong><br><img src="https://img-blog.csdnimg.cn/20200703155720322.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"><strong>2.开启事务，读取数据</strong><br><img src="https://img-blog.csdnimg.cn/20200703155824987.png" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center"><strong>3.开启事务，插入一条数据，发现执行插入的sql语句被阻塞(数据插入不了，即解决了<code>幻读</code>)</strong><br><img src="https://img-blog.csdnimg.cn/20200703160116749.png" alt="在这里插入图片描述"></td> 
  </tr> 
 </tbody> 
</table>

### 三.深入理解事务隔离 ###

#### 1.基本概念 ####

##### ①版本链 #####

> Internally, InnoDB adds three fields to each row stored in the database. A 6-byte DB\_TRX\_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte DB\_ROLL\_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB\_ROW\_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB\_ROW\_ID column does not appear in any index.

mysql每行有三个隐藏字段:

*  **DB\_TRX\_ID**  
    记录事务唯一标识，每个事务都有ID叫做TRX\_ID,DB\_TRX\_ID就是记录操纵这行数据的事务的ID。  
    可以通过`select * from information_schema.innodb_trx \G`查看当前事务的TRX\_ID。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70]
 *  **DB\_ROLL\_PTR**  
    回滚指针，当某条聚集索引记录被修改后，旧的版本都会写入undo log中，通过回滚指针形成链表，可以找到以前的版本。
 *  **DB\_ROW\_ID**  
    记录唯一标识，mysql每张表都会有主键。(该字段与事务隔离无关，只是顺便提及)
    
    1.  用户指定某列为主键
    2.  如果用户没有手动指定主键，则将`非空唯一索引`作为主键
    3.  如果以上情况都没有，则改记录增加一列DB\_ROW\_ID作为记录唯一标识

了解了**DB\_TRX\_ID**与**DB\_ROLL\_PTR**也就大致理解了版本链。

**假设有一条记录，插入这条记录的事务TRX\_ID为100,事务A TRX\_ID为200，事务B TRX\_ID为300**

<table> 
 <thead> 
  <tr> 
   <th align="center"><strong>事务A(200)</strong></th> 
   <th align="center"><strong>事务B(300)</strong></th> 
   <th align="center"><strong>版本链</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center"><strong>1.事务A修改name为sjj</strong><img src="https://img-blog.csdnimg.cn/20200703153025200.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70" alt="在这里插入图片描述"></td> 
   <td align="center"></td> 
   <td align="center"><img src="https://img-blog.csdnimg.cn/20200703153318567.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70" alt="在这里插入图片描述"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center"><strong>2.事务B修改name为dhh</strong><img src="https://img-blog.csdnimg.cn/2020070315354326.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70" alt="在这里插入图片描述"></td> 
   <td align="center"><img src="https://img-blog.csdnimg.cn/20200703154223652.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70" alt="在这里插入图片描述"></td> 
  </tr> 
 </tbody> 
</table>

##### ②read view #####

read view 类似于快照（[什么是快照？ 快照与备份有什么区别？][Link 1])，通过read view 这一数据结构和可见性比较算法来`判断当前版本链中的哪个版本是当前事务可见的`。  
对read view的具体判断方法可以参考以下两篇博客:

*  [mysql版本链和readView原理][mysql_readView]
 *  [MySQL中MVCC的正确打开方式（源码佐证）][MySQL_MVCC]

##### ③ innodb中的锁 #####

![在这里插入图片描述][20200703171704780.png]  
**1.共享锁(S)和排它锁(X)**

**共享锁和排它锁都是行级锁**，行级锁作用在索引而不是整条记录上的。

*  共享锁: 共享锁也叫读锁，允许事务读一行数据
 *  排它锁: 排它锁也叫写说，允许事务修改或者删除一行数据

**2.意向共享锁(IS)和意向排它锁(IX)**

**意向共享锁和意向排它锁都是表级锁**

*  意向共享锁: 事务想要获得一张表中某几行的共享锁 `select xxx from xxx lock in share mode`
 *  意向排它锁: 事务想要获得一张表中某几行的排它锁 `select * from xxx for update`

##### ④行级锁的三种算法 #####

假设表中有3个索引值: 10,20,30.

*  **Record Lock：** 单个行记录上的锁  
    eg:锁定范围为 \[10\],\[20\],\[30\]三个值
 *  **Gap Lock：** 锁定一个范围，但不包括记录本身  
    eg:锁定范围为(-∞,10),(10,20),(20,30),(30,+∞)
 *  **Next Lock:** Record Lock+Gap Lock  
    eg:锁定范围为(-∞,10\],(10,20\],(20,30\],(30,+∞)

##### ⑤快照读和当前读 #####

**快照读：** 读取的是快照版本，也就是历史版本，可能不是最新的版本。普通的`select xxx`都是快照读。  
**当前读：** 读取的都是最新的版本。`select xxx from xxx lock in share mode`、`select * from xxx for update`、`insert`、`delete`、`update`都是当前读。

##### ⑥LBCC和MVCC #####

*  **LBCC(Lock-Based Concurrent Control)：** 基于锁的并发控制,事务的每次操作都会加锁，事务串行化执行，避免了`脏读`、`不可重复读`、`幻读`。缺点是由于锁的存在，花销大，由于锁的竞争等可能出现事务的阻塞和死锁。
 *  **MVCC(Multiversion Concurrency Control)：** 多版本并发控制，**MVCC原理**基于之前提到的`隐藏字段(DB_TRX_ID,DB_ROLL_PTR)`\+`read view`\+`undo log`实现。  
    由隐藏字段和undo log形成版本链，形成多版本。在读取时根据read view选取当前事务可见的版本。

#### 2.具体事务隔离级别分析 ####

有了上面的基本概念，我们就可以针对具体的事务隔离等级进行分析了。

##### ①Read uncommitted分析 #####

Read uncommitted下每次读到的都是最新的数据，即当前读，所以才会出现`脏读`的现象。

##### ②Read committed分析 #####

Read committed 基于MVCC，也会创建版本链，但是在开始事务后的查询时，每次快照都会被重置，即每次查询都会生成read view.导致出现`不可重复读`.

##### ③Repeatable read分析 #####

Repeatable read基于**MVCC+Gap Lock**。

Repeatable read下的MVCC与Read committed下的MVCC不同的是，在开始事物后的查询时，只有第一次查询才会生成read view，这样才能`可重复读`.

我们之前说过Repeatable read下会出现`幻读`的现象.

但是我们在实际测试的时候，第一次却没有出现`幻读`的现象。![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70 1]  
但是我们在第二次查询时却出现了`幻读`的现象。  
![在这里插入图片描述][20200703124920944.png]  
对比这两次查询语句，很明显第一次是普通的select，即`快照读`,但是第二次的select xxx lock in share mode 加了S锁，为`当前读`。

所以`MVCC只解决了快照读下的幻读，并不能解决当前读下的幻读`。

但是在RR隔离级别下可以通过`MVCC+Gap Lock`解决`当前读下的幻读`。

##### ④Serializable分析 #####

串行化下每次操作都加了锁，通过next-key即可以解决`幻读`。

[20200703112338659.png]: https://img-blog.csdnimg.cn/20200703112338659.png
[mysql]: https://blog.csdn.net/qq_41154871/article/details/98497622
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020070314455628.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70
[Link 1]: https://blog.csdn.net/rex_wust/article/details/96349878
[mysql_readView]: https://blog.csdn.net/nmjhehe/article/details/98470570
[MySQL_MVCC]: https://blog.csdn.net/Waves___/article/details/105295060
[20200703171704780.png]: https://img-blog.csdnimg.cn/20200703171704780.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200704001341146.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDUzMjY2,size_16,color_FFFFFF,t_70
[20200703124920944.png]: https://img-blog.csdnimg.cn/20200703124920944.png