MySQL事务隔离级别理解_解读MYSQL的可重复读、幻读及实现原理

ゞ浴缸里的玫瑰 2023-10-02 19:34 12阅读 0赞

#### 前言 ####

提到事务，你肯定不会陌生，最经典的例子就是转账，甲转账给乙100块，当乙的账户中到账100块的时候，甲的账户就应该减去100块，事务可以有效的做到这一点。

![img][]

在MySQL中，事务支持实在引擎层实现的，MySQL是一个支持多引擎的系统，但并不是所有引擎都支持事务。比如MySQL原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被取代的原因之一。

#### 隔离性 ####

事务的四大特性ACID（Atomicity原子性、Consistency一致性、Isolation隔离性、Durability持久性），今天就来讲讲其中的隔离性。当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了事务隔离级别这个概念。

首先你要知道，隔离得越结实，效率就会越低。因此需要我们考虑到这两点，不能只顾一边。SQL的标准事务隔离级别包括：

*  读未提交（read uncommitted）：一个事务还没有提交时，它做的变更就能被别的事务看到。
 *  读提交（read committed）：一个事物提交之后，它做的变更才会被其他事务看到。
 *  可重复读（repeatable read）：一个事物执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。未提交变更对其他事务也是不可见的。
 *  串行化（serializable）：对于同一行记录，写会加“写锁”，读会加“读锁”，当出现锁冲突时，后访问的事务需要等前一个事务执行完成，才能继续执行。

其中，“读提交”和“可重复读”比较难理解，这里用一个例子说明一下。

假设数据表中 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

create table T(c int) engine = InnoDB;insert into T(c) values(1);

![img][img 1]

在不同的隔离级别下，事务A查到的结果是不一样的：

*  若隔离级别是“读未提交”，则 v1 的值是2，这时候事务 B 虽然还没有提交，但是结果已经被事务 A 看到了，因此 v2、v3 也都是2。
 *  若隔离级别是“读提交”，则 v1 是1，v2 的值是 2 ，事务 B 的更新在提交后才被事务 A 看到。所以 v3 的值也是 2。
 *  若隔离级别是“可重复读”，则 v1、v2是 1，v3 是2，之所以 v2 还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
 *  若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住，直到事务 A 提交后，事务 B 才可以继续执行，所以从事务 A 的角度看，v1、v2的值是1，v3的值是2。

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

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle 数据库的默认隔离级别其实是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。配置的方式是：将启动参数 transaction-isolation 的值设置成 READ-COMMITTED，你可以用 show variables 来查看当前的值。

![img][img 2]

每个事务隔离级别都有存在的意义，都有自己的场景，下面来一个“可重复读”的场景：

假设你在管理一个个人银行账户表，一个表存了每个月月底的余额，一个表存了账单明细，这时候你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便，事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

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

理解了事务隔离级别，我们再来看看事务隔离具体是怎么实现的，这里我们展开说明“可重复读”。在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作，记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

![img][img 3]

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

同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C对应的事务是不会冲突的。那么回滚日志什么时候删除呢？答案是，再不需要的时候才删除，也就是说，系统会判断，当没有事务在需要用到这些回滚日志时，回滚日志才会被删除。什么时候才是不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

基于上面的说明，我们来讨论一下为什么建议你尽量不要使用长事务。

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

在MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小，作者见过数据只有20GB，而回滚段有200GB 的库，最终只好为了清理回滚段，重新建个库。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，这个我们会在后面讲锁的时候展开。

#### 事务的启动方式 ####

如前面所述，长事务有这些潜在风险，我当然建议读者尽量避免。其实很多业务开发的同学并不是有意使用长事务，通常是由于误用所致。MySQL 的事务启动方式有以下几种：

1.  显式启动事务语句，begin 或者 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2.  set autocommit = 0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动 commit 或者 rollback 语句，或者断开连接。

有些客户端连接框架会默认连接成功后先执行一个 set autocommit = 0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务，因此，建议读者总是使用 set autocommit = 1，通过显式语句的方式来启动事务。

但是有的开发同学会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务。第二种方式每个事务在开始时都不需要主动执行一次“begin”，减少了语句的交互次数。如果读者也有这个顾虑，建议使用 commit work and chain 语法。

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

你可以在 information\_schema 库的 innodb\_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

#### 小结 ####

这篇文章主要介绍了MySQL的事务隔离几倍的现象和实现，根据实现原理分析了长事务存在的风险，以及如何用正确的方式避免长事务。

--------------------

## 解读MYSQL的可重复读、幻读及实现原理 ##

**事务隔离级别有四种，mysql默认使用的是可重复读，mysql是怎么实现可重复读的？为什么会出现幻读？是否解决了幻读的问题？**

### 一、事务的隔离级别 ###

Read Uncommitted（未提交读）  
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读（Dirty Read）。该级别用的很少。

Read Committed（提交读）  
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变，换句话说就是事务提交之前对其余事务不可见。这种隔离级别也支持不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select查询可能返回不同结果。

Repeatable Read（可重复读）  
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题  
**（mysql彻底解决了幻读问题？请往下看）**。

Serializable（可串行化）  
这是最高的隔离级别，它强制事务都是串行执行的，使之不可能相互冲突，从而解决幻读问题。换言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

<table> 
 <thead> 
  <tr> 
   <th>事务隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</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>

在MySQL的众多存储引擎中，只有InnoDB支持事务，所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

### 二、mysql怎么实现的可重复读 ###

MVCC多版本并发控制(Multi-Version Concurrency Control)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读取隔离级别。

在《高性能MySQL》中对MVCC的解释如下

![img][img 4]

### 举例说明MVCC的实现 ###

新建一张表test\_zq如下

#### MVCC逻辑流程-插入 ####

在插入数据的时候，假设系统的全局事务ID从1开始，以下SQL语句执行分析参考注释信息：

begin;-- 获取到全局事务ID
    insert into `test_zq` (`id`, `test_id`) values('5','68');
    insert into `test_zq` (`id`, `test_id`) values('6','78');
    commit;-- 提交事务

当执行完以上SQL语句之后，表格中的内容会变成：

可以看到，插入的过程中会把全局事务ID记录到列 DB\_TRX\_ID 中去

#### MVCC逻辑流程-删除 ####

对上述表格做删除逻辑，执行以下SQL语句（假设获取到的事务逻辑ID为 3）

begin；--获得全局事务ID = 3
    delete test_zq where id = 6;
    commit;

执行完上述SQL之后数据并没有被真正删除，而是对删除版本号做改变，如下所示：

#### MVCC逻辑流程-修改 ####

修改逻辑和删除逻辑有点相似，修改数据的时候 会先复制一条当前记录行数据，同事标记这条数据的数据行版本号为当前是事务版本号，最后把原来的数据行的删除版本号标记为当前是事务。

执行以下SQL语句：

begin;-- 获取全局系统事务ID 假设为 10
    update test_zq set test_id = 22 where id = 5;
    commit;

执行后表格实际数据应该是：

#### MVCC逻辑流程-查询 ####

此时，数据查询规则如下：

*  查找**数据行版本号早于当前事务版本号**的数据行记录
    
    也就是说，数据行的版本号要小于或等于当前是事务的系统版本号，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据
 *  查找**删除版本号**要么为NULL，要么**大于当前事务版本号**的记录
    
    这样确保查询出来的数据行记录在事务开启之前没有被删除

根据上述规则，我们继续以上张表格为例，对此做查询操作

begin;-- 假设拿到的系统事务ID为 12
    select * from test_zq;
    commit;

执行结果应该是：

这样，同一个事务中，就实现了可重复读。

### 三、幻读 ###

什么是幻读，如下：

![img][img 5]

**InnoDB实现的RR通过mvcc机制避免了这种幻读现象。**

另一种幻读：

![img][img 6]

姑且把左边的事务命名为事务A，右边的命名为事务B。  
事务B执行后，在事务A中查询没有查到B添加的数据行，这就是可重复读。  
但是，在事务A执行了update后，再查询时就查到了事务A中添加的数据，这就是幻读。  
这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决是不彻底的。

原以为这也是一种幻读，但经过多次研究资料，这只是对数据修改的操作(update、insert、delete)当前读产生的结果，他其实不是幻读。

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

出现了上面的情况我们需要知道为什么会出现这种情况。在查阅了一些资料后发现在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

select 快照读

当执行select操作是innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。**在执行这几个操作时会读取最新的版本号记录，写操作后把版本号改为了当前事务的版本号，所以即使是别的事务提交的数据也可以查询到**。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。也正是因为这样所以才导致幻读。

### 四、如何解决幻读 ###

在快照读情况下，mysql通过mvcc来避免幻读。

在当前读情况下，mysql通过X锁或next-key来避免其他事务修改:

*  使用串行化读的隔离级别
 *  (update、delete)当where条件为主键时，通过对主键索引加record locks(索引加锁/行锁)处理幻读。
 *  (update、delete)当where条件为非主键索引时，通过next-key锁处理。next-key是record locks(索引加锁/行锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)的结合。

![img][img 7]

Next-Key Lock即在事务中select时使用如下方法加锁，这样在另一个事务对范围内的数据进行修改时就会阻塞（为什么有共享锁会阻塞？不能在有共享锁的记录上加X锁）：

select * from table where id<6 lock in share mode;--共享锁select * 
    from table where id<6 for update;--排他锁

关于next-key locks请参考https://www.cnblogs.com/zhoujinyi/p/3435982.html

[img]: https://img-blog.csdnimg.cn/2018121723512891.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hjeTExOTMwNjg2Mzk=,size_16,color_FFFFFF,t_70
[img 1]: https://img-blog.csdnimg.cn/20181218212158153.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hjeTExOTMwNjg2Mzk=,size_16,color_FFFFFF,t_70
[img 2]: https://img-blog.csdnimg.cn/20181218215027819.png
[img 3]: https://img-blog.csdnimg.cn/20181218220002776.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hjeTExOTMwNjg2Mzk=,size_16,color_FFFFFF,t_70
[img 4]: https://img-blog.csdnimg.cn/20190515143938256.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nhbnl1ZXNhbjAwMDA=,size_16,color_FFFFFF,t_70
[img 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTE3NDcxMC8yMDE5MDEvMTE3NDcxMC0yMDE5MDEyODIwMTAyMTYwNi0xMDg5OTgwMjc5LnBuZw?x-oss-process=image/format,png
[img 6]: https://img-blog.csdnimg.cn/20190515105659107.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nhbnl1ZXNhbjAwMDA=,size_16,color_FFFFFF,t_70
[img 7]: https://img-blog.csdn.net/20180712163139265?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FBQTgyMQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70