深入浅出MYSQL的事务隔离

水深无声 2022-12-10 14:46 111阅读 0赞

### 深入浅出MYSQL的事务隔离 ###

Innodb 数据库引擎的数据库或表支持事务，MyISAM不支持事务，这也是MYSQL默认引擎换成Innodb的重要原因。MySQL 事务主要用于处理操作量大，复杂度高的数据，事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

#### ACID–事务四大特征 ####

事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RpbmdkaW5nZG9kbw_size_16_color_FFFFFF_t_70_pic_center]

*  **原子性 Atomicity**：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
 *  **一致性 Consistency**：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
 *  **隔离性 Isolation**：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
 *  **持久性 Durability**：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，即便系统发生故障也不会丢失。

#### 事务隔离 ####

多个事务并发处理时，会带来诸多问题：

*  更新丢失：A和B事务并发执行，A事务执行更新后，提交；B事务在A事务更新后，B事务结束前也做了对该行数据的更新操作，然后回滚，则两次更新操作都丢失了
 *  脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
 *  不可重复读：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。**不可重复读的重点是修改**：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样
 *  幻读：一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。**幻读的重点在于新增或者删除**：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样

脏读是指读取了未修改完的记录，不可重复读指因为被其它事务修改了记录导致某事务两次读取记录不一致，而幻读是指因为其它事务对表做了增删导致某事务两次读取的表记录数不一致问题

为了解决并发事务的问题，就有了数据库事务的隔离级别。首先需要明确的是：**隔离越严格，效率就会越低**。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

*  读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
 *  读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
 *  可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。**InnoDB默认是可重复读级别的**
 *  串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读（Dirty Read）</th> 
   <th>不可重复读（NonRepeatable Read）</th> 
   <th>幻读（Phantom Read）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>未提交读（Read uncommitted）</td> 
   <td>可能</td> 
   <td>可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>已提交读（Read committed）</td> 
   <td>不可能</td> 
   <td>可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>可重复读（Repeatable read）</td> 
   <td>不可能</td> 
   <td>不可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>可串行化（Serializable ）</td> 
   <td>不可能</td> 
   <td>不可能</td> 
   <td>不可能</td> 
  </tr> 
 </tbody> 
</table>

用例子说明几种不同的隔离级别：

##### **读未提交：** #####

一个事务可以读取另一个未提交事务的数据。

例子：小A给小B转账，转账时不小心按错了数字，本来转100呢结果转了1000，不过这是一个事件，还没有提交，所以小A赶紧回滚了该事务，然后又转了100。但是因为是读未提交的隔离级别，所以虽然事件还没有commit，但是小B看到了你只转去了1000，然后过了一会发现怎么最后转的是100。

也就是说，该事件还没提交，但是别的事务可以看到它的未提交的数据，也就是读了脏数据。

##### 读已提交： #####

一个事务只能读取另一个事务已经提交的数据，可以避免脏数据。

例子：小A卡里有1000块，在消费时，小A的老婆也用了他的卡买了东西，由于是读已提交，因此虽然小A的老婆已经付过钱，但是此时还没有提交，所以小A看到的仍然是1000块。但是小A付账前，小A老婆完成了提交，此时小A付帐前查看余额发现余额已经不足，支付失败，非常郁闷。

读已提交解决了脏数据的问题，但是没有解决不可重复读的问题，即一个事务的相同查询返回不同的数据。

##### 可重复读： #####

一个事务不一定能够立马读到另一个事务已经提交的数据，事务内的对同一数据的多次读取应该是一致的。

例子：小A卡里有1000块，小A的事件先读取了余额，小A的老婆想用了他的卡买东西，由于小A的事件已经读取了余额，因此在该事件提交前，小A的老婆不能对该记录进行修改了，因此小A的老婆无法完成提交。

可重复读解决了脏读和不可重复读的问题，但仍有出现幻读的可能。

##### 串行化： #####

串行化是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，串行化会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

##### 不同隔离级别 #####

例子：

一个很简单的数据表，只有一行数据，该数据值为1，两个事务按如下方式进行：

<table> 
 <thead> 
  <tr> 
   <th align="center">事务A</th> 
   <th align="center">事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">开始事务</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">开始事务</td> 
  </tr> 
  <tr> 
   <td align="center">查询得到该数据值为1</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">查询得到该数据值为1</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">将1改为2</td> 
  </tr> 
  <tr> 
   <td align="center">查询得到该数据值为X</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="center">提交事务</td> 
  </tr> 
  <tr> 
   <td align="center">查询得到该数据值为Y</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">提交事务</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">查询得到该数据值为Z</td> 
   <td align="center"></td> 
  </tr> 
 </tbody> 
</table>

在不同的隔离级别下，事务 A 会有哪些不同的返回结果：

*  若隔离级别是“读未提交”，则虽然事务B修改1为2后没有提交，但是A已经可以读取到该修改，所以事务A的X、Y、Z值均为2.
 *  若隔离级别是“读提交”，则虽然事务B已经修改了该值，但是提交前事务A是不可看见的，所以X的值应该是1，Y和Z的值是2
 *  若隔离级别是“可重复读”，则事务A在执行期间看到的数据必须保持一致的，因此X和Y都是1，Z的值是2
 *  若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住，因为事务A正在读取该数据，不允许对该数据进行修改的，所以直到事务 A 提交后，事务 B 才可以继续执行，也就是说真正的执行顺序会发生一些变化。因此X和Y的值是1，Z的值是2

> 在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

##### 事务隔离的实现 #####

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RpbmdkaW5nZG9kbw_size_16_color_FFFFFF_t_70_pic_center 1]

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

通过回滚日志，用于实现一致性。系统会在该回滚日志不再需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除，也就是当系统里没有比这个回滚日志更早的 read-view 时。

所以尽量避免使用长事务，长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间，同时，长事务还占用锁资源，对数据库性能影响比较大。

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx\_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx\_id 和一致性视图确定数据版本的可见性。对于可重复读，查询只承认在事务启动前就已经提交完成的数据；对于读提交，查询只承认在语句启动前就已经提交完成的数据；而当前读，总是读取已经提交完成的最新版本。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RpbmdkaW5nZG9kbw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/c60b5a156b8e498bbd173a576cf6f634.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RpbmdkaW5nZG9kbw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/b43c080ae351447faae4c47184c1074a.png