MySQL | MySQL 事务隔离级别和多版本并发控制MVCC

╰+攻爆jí腚メ 2022-11-05 12:55 318阅读 0赞

# MySQL 事务隔离级别和多版本并发控制MVCC #

MySQL 提供了两种事务型的存储引擎：InnoDB 和 NDB Cluster 。另外还有一些第三方存储引擎也支持事务

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

### 文章目录 ###

*  MySQL 事务隔离级别和多版本并发控制MVCC
 *  1. 数据库事务
 *  2. 事务并发可能出现的问题
 *   *  2.1 脏读
     *  2.2 不可重复读
     *  2.3 幻读
 *  3. 事务隔离级别
 *   *  3.1 读已提交 （READ COMMITTED）是如何工作的
     *  3.2 可重复读 （REPEATABLE READ）
     *  3.3 串行化 （SERIALIZABLE）
 *  4. 多版本并发控制(MVCC)
 *   *  4.1 InnoDB 的MVCC
 *  5. 混合存储引擎下的事务问题
 *  6. 查看和设置隔离级别
 *   *  6.1 查看隔离级别
     *  6.2 修改隔离级别
     *  6.3 适用 JDBC 修改当前连接的隔离级别

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

# 1. 数据库事务 #

数据库事务指的是一组数据操作，事务内的操作要么就是全部成功，要么就是全部失败。

例如在转账的流程下，张三给李四转账 2000，第一步在账单账户下扣除 2000，第二步在李四账户下增加 2000，这两步可以视为一个事务。如果两步都成功则转账成功，如果其中任意一步失败，则撤回转账操作

事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）四个特性，简称 ACID。

这里主要说的是 **隔离性**

# 2. 事务并发可能出现的问题 #

为什么要有隔离级别，主要为了防止在事务并发中出现的一系列问题

假设我们的表结构和数据如下：

create table movie (
       id int unsigned not null auto_increment primary key,
       name varchar(30) not null comment '电影名称',
       price int not null comment '票价'
    )ENGINE=InnoDB comment='电影';
    
    INSERT INTO movie (id, name, price) VALUES (1, '唐探3', 70);
    INSERT INTO movie (id, name, price) VALUES (2, '你好，李焕英', 60);

## 2.1 脏读 ##

脏读指的是读到了其他事务未提交的数据。在事务中未提交的数据有可能是临时数据或者可能需要回滚的数据，本身可能并不会存到数据库中，这就是脏读。

在事务开始前，唐探 3 的价格为 70 元。

事务一先开启事务，然后事务二后开启事务，因为事务二当时处理的快点，先把价格改成了 55 元，此时事务还未提交或者回滚，这时事务一要读这条数据，如果读到事务二已经改动但未提交的数据，即 55 元，这时就发生了脏读

<table> 
 <thead> 
  <tr> 
   <th>时间、事务</th> 
   <th align="left">事务一</th> 
   <th align="left">事务二</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="left">begin</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="left"></td> 
   <td align="left">begin</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="left"></td> 
   <td align="left">update movie set price = 55 where id = 1;</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="left">select price from movie where id = 1;如果这里读到了 55 则出现了脏读</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="left">commit;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td align="left"></td> 
   <td align="left">rollback;</td> 
  </tr> 
 </tbody> 
</table>

## 2.2 不可重复读 ##

不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如在第一次读数据和第二次读数据时，中间其他事务也对这个数据进行了修改，导致同一事务对同一条数据读取发生前后不一致。这种情况通常发生在数据\*\*更新（UPDATE）\*\*操作。

<table> 
 <thead> 
  <tr> 
   <th>时间、事务</th> 
   <th align="left">事务一</th> 
   <th align="left">事务二</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="left">begin</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="left"></td> 
   <td align="left">begin</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="left">select price from movie where id = 1; 此时为 70</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="left"></td> 
   <td align="left">update movie set price = 55 where id = 1;</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="left">select price from movie where id = 1;如果这里读到了 55 则出现不可重复读</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="left">commit;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td align="left"></td> 
   <td align="left">rollback;</td> 
  </tr> 
 </tbody> 
</table>

## 2.3 幻读 ##

幻读是指一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

<table> 
 <thead> 
  <tr> 
   <th>时间、事务</th> 
   <th>事务一</th> 
   <th>事务二</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td></td> 
   <td>begin</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>select name from movie where id &lt;= 70; 此时应该查询到 2 条数据为 1. 唐探3， 2. 你好，李焕英</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>INSERT INTO movie (id, name, price) VALUES (3, ‘刺杀小说家’, 50);</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>select name from movie where id &lt;= 70; 此时查询到 3 条数据为 1. 唐探3， 2. 你好，李焕英 3. 刺杀小说家</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>commit;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td></td> 
   <td>rollback;</td> 
  </tr> 
 </tbody> 
</table>

# 3. 事务隔离级别 #

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

1.  读未提交（READ UNCOMMITTED）
2.  读提交 （READ COMMITTED）
3.  可重复读 （REPEATABLE READ）
4.  串行化 （SERIALIZABLE）

其中每个隔离级别来解决对应的问题

<table> 
 <thead> 
  <tr> 
   <th align="left">隔离级别 | 问题</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">读未提交（READ UNCOMMITTED）</td> 
   <td>未解决</td> 
   <td>未解决</td> 
   <td>未解决</td> 
  </tr> 
  <tr> 
   <td align="left">读提交 （READ COMMITTED）</td> 
   <td>解决</td> 
   <td>未解决</td> 
   <td>未解决</td> 
  </tr> 
  <tr> 
   <td align="left">可重复读 （REPEATABLE READ）</td> 
   <td>解决</td> 
   <td>解决</td> 
   <td>未解决</td> 
  </tr> 
  <tr> 
   <td align="left">串行化 （SERIALIZABLE）</td> 
   <td>解决</td> 
   <td>解决</td> 
   <td>解决</td> 
  </tr> 
 </tbody> 
</table>

从这个表中可以看到隔离级别越高则解决的问题越多，同时级别越高对并发性能的影响也就越大

其中 **读未提交（READ UNCOMMITTED）** 则什么都没有解决，这里不在讨论这种隔离级别

## 3.1 读已提交 （READ COMMITTED）是如何工作的 ##

**读已提交解决了脏读问题**

在读已提交中，主要是针对 update 语句进行处理，首先再看一下，出现脏读的场景，其中前 6 步是刚才模拟发生脏读的情况，在读已提交的隔离级别下：在第 4 步中需要执行发现需要执行查询操作，但是因为发现在事务二中该数据已经被修改，则需要等到事务二完成（提交或者回滚）才能进行查询，因此事务一的第 4，5 步需要等到事务二中 6 执行完成后才能执行

<table> 
 <thead> 
  <tr> 
   <th>时间、事务</th> 
   <th align="left">事务一</th> 
   <th align="left">事务二</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="left">begin</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="left"></td> 
   <td align="left">begin</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="left"></td> 
   <td align="left">update movie set price = 55 where id = 1;</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="left">select price from movie where id = 1;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="left">commit;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td align="left"></td> 
   <td align="left">rollback;</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="left">第 4 步中的 sql 要等到这里才能执行</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td align="left">第 5 步中的 sql 要等到这里才能执行</td> 
   <td align="left"></td> 
  </tr> 
 </tbody> 
</table>

## 3.2 可重复读 （REPEATABLE READ） ##

**可重复读解决了脏读和不可重复读的问题**

<table> 
 <thead> 
  <tr> 
   <th>时间、事务</th> 
   <th align="left">事务一</th> 
   <th align="left">事务二</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="left">begin</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="left"></td> 
   <td align="left">begin</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="left">select price from movie where id = 1; 此时为 70</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="left"></td> 
   <td align="left">update movie set price = 55 where id = 1;</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="left">select price from movie where id = 1; 此时读到还是 70</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="left">commit;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td align="left"></td> 
   <td align="left">commit;</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td align="left">select price from movie where id = 1; 此时读到是 55</td> 
   <td align="left"></td> 
  </tr> 
 </tbody> 
</table>

Q: 为什么对事务二中对 id = 1 的数据进行了写（update）而事务一步需要阻塞还能读到呢？

A: 因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。

## 3.3 串行化 （SERIALIZABLE） ##

这个模式就相对简单了，它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行，解决了脏读、不可重复读和幻读，当然性能也是最差的。

# 4. 多版本并发控制(MVCC) #

MySQL 大部分事务型存储引擎并不是简单的行级锁。基于提升并发行的考虑，它们一般都同时实现了多版本并发控制 MVCC.

## 4.1 InnoDB 的MVCC ##

InnoDB 的 MVCC ，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间，一个保存行的过期时间(或删除时间)，当然存储的并不是真正的时间，而是系统版本号。每开始一个事务，系统版本号就会自动递增，事务开始时刻的版本号作为当前事务的版本号，用来和查询到的每行记录的版本号就行比较。

以下是 REPEATABLE READ 的隔离级别下具体操作：

*  SELECT
    
    InnoDB 会根据以下两个条件检查每行记录：
    
    a. InnoDB 只查询版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版号），这样可以确保事务读取的行，要么是在事务开始前的已经存在的，要么是事务自身插入或者修改过的。  
    b. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
    
    只有符合上述两个条件的记录，才能返回作为查询结果
 *  INSERT
    
    InnoDB 为新插入的每一行保存当前系统版本号作为行版本号
 *  DELETE
    
    InnoDB 为删除的每一行保存当前系统版本号作为行删除标识
 *  UPDATE  
    InnoDB 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

保存着两个额外的系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行

# 5. 混合存储引擎下的事务问题 #

MySQL 在服务层不管理事务，事务由下层的存储引擎实现，所以在不同的存储引擎中处理同一个事务是不可靠的。  
例如： table\_a 使用 innodb 引擎， table\_b 使用 MyISAM 引擎(不支持事务)  
假设以下操作在一个事务中：

insert table_a ...    # 1
    insert table_b ...    # 2
    update table_a ...    # 3

假设在 执行 \#3 时出现了异常，这时事务要回滚，因为 table\_b 并不支持事务，这就导致 table\_b 的修改无法回滚，导致违反事务的 一致性 和 原子性

# 6. 查看和设置隔离级别 #

## 6.1 查看隔离级别 ##

**查看当前的隔离级别**

-- 方式一
    show variables like '%tx_isolation%';
    
    -- 方式二
    select @@tx_isolation;

**查看会话的隔离级别**

SELECT @@session.tx_isolation;

**查看全局的隔离级别**

SELECT @@global.tx_isolation;

以上的方式适用 MySQL 8.0 以前版本，在 MySQL 8.0.3 中将 tx\_isolation 替换成了 transaction\_isolation 变量替换了, 将上面的 tx\_isolation

## 6.2 修改隔离级别 ##

MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别。语法格式如下：

`SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}`

其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：

*  SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
 *  GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
 *  如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

任何用户都能改变会话的事务隔离级别，但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。

## 6.3 适用 JDBC 修改当前连接的隔离级别 ##

在 `connection` 中提供了 `setTransactionIsolation()` 来修改当前连接的隔离级别，使用方法如下：

connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5bmRldg_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p5bmRldg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221023/b89af2204c5e43928fe42fd3bec33b2f.png