MySQL 事务隔离级别详解

## MySQL 事务隔离级别

SQL:1992 标准定义了四种事务隔离级别，分别是读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。不同的隔离级别下会产生脏读、幻读、不可重复读等相关问题，因此在选择隔离级别的时候要根据应用场景来决定，使用合适的隔离级别。

MySQL 中的 InnoDB 存储引擎提供了 SQL 标准所描述的四种事务隔离级别，默认事务隔离级别是可重复读（Repeatable Read）。

各种隔离级别和数据库事务并发时存在的问题对应情况如下：

| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
| --- | --- | --- | --- |
| Read Uncommitted | √ | √ | √ |
| Read Committed | × | √ | √ |
| Repeatable Read | × | × | √ |
| Serializable | × | × | × |

-   读未提交（Read Uncommitted）允许脏读，就是在该隔离级别下，可能读到其他会话未提交事务修改的数据，存在脏读、不可重读读、幻读的问题。
    
-   读已提交（Read Committed）只能查询到已提交的数据。这是 Oracle 数据库默认的事务隔离级别。存在不可重读读、幻读的问题。
    
-   可重复读（Repeatable Read）就是在一个事务里相同条件下，无论何时查到的数据都和第一次查到的数据一致。这是 MySQL 数据库 InnoDB 引擎默认的事务隔离级别。在范围查询时存在幻读的问题。
    
-   串行化（Serializable）是最高的事务隔离级别，它严格服从 ACID 特性的隔离级别。所有的事务依次逐个执行，事务之间互不干扰，该级别可以防止脏读、不可重复读以及幻读。但每个事务读数据时都需要获取表级的共享锁，导致读和写都会阻塞，性能极低。

> 因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是**读已提交（Read Committed）**，但是 MySQL 中 InnoDB 存储引擎默认使用**可重复读（Repeatable Read）**并不会有任何性能损失。

## 如何设置 MySQL 事务隔离级别

1、可以在 MySQL 的配置文件 my.cnf、my.ini 中设置：

transaction-isolation\=READ-UNCOMMITTED # 读未提交  
或  
transaction-isolation\=READ-COMMITTED # 读已提交  
或  
transaction-isolation\=REPEATABLE-READ # 可重复读  
或  
transaction-isolation\=SERIALIZABLE # 串行化

2、可以在连接 MySQL 服务端命令行用 --transaction-isolation；

3、可以使用 SET TRANSACTION 命令改变单个或者所有新连接的事务隔离级别：

SET \[SESSION | GLOBAL\] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}  
  
\# 如  
set session transaction isolation level read committed;

-   不带 GLOBAL 或 SESSION 关键字表示设置下一个事务的隔离级别；
    
-   使用 GLOBAL 关键字表示对全局设置事务隔离级别，设置后的事务隔离级别对所有新的数据库连接生效；
    
-   使用 SESSION 关键字表示对当前的数据库连接设置事务隔离级别，只对当前连接生效；
    
-   任何客户端都可以自由改变当前会话的事务隔离级别，可以在事务中间改变，也可以改变下一个事务的隔离级别。

## 如何查看 MySQL 事务隔离级别

\# MySQL 8.0 之前  
SELECT @@global.tx\_isolation;  
SELECT @@session.tx\_isolation;  
SELECT @@tx\_isolation;  
  
\# MySQL8.0  
SELECT @@global.transaction\_isolation;  
SELECT @@session.transaction\_isolation;  
SELECT @@transaction\_isolation;

> 在 MySQL 8.0 之前的版本中 `tx_isolation` 是作为 `transaction_isolation` 的别名被应用的，新版本已经弃用了，需要把 `tx_isolation` 换成 `transaction_isolation`，否则会出现 `1193 - Unknown system variable 'xx_isolation'` 错误。

## MySQL 事务隔离级别实践

在 MySQL 中创建一个 test 数据库，在 test 数据库中创建一个 account 账户数据表作为测试使用：

CREATE TABLE \`account\` (  
  \`id\` int NOT NULL AUTO\_INCREMENT COMMENT '账户id',  
  \`name\` varchar(32) COLLATE utf8\_bin DEFAULT NULL COMMENT '姓名',  
  \`balance\` int DEFAULT '0' COMMENT '余额',  
  PRIMARY KEY (\`id\`)  
) ENGINE\=InnoDB DEFAULT CHARSET\=utf8 COLLATE\=utf8\_bin;

在 account 表中插入测试数据：

INSERT INTO test.account (name, balance)   
VALUES  
('熊大',300),  
('熊二',400),  
('光头强',500)

此时熊大、熊二、光头强的账户余额分别为 300 元、400 元、500元。

### 读未提交（read uncommitted）

开启两个终端分别为 A 和 B，登录 MySQL，并将当前终端的事务隔离级别设置为读未提交 read uncommitted：

mysql\> set session transaction isolation level read uncommitted;  
Query OK, 0 rows affected (0.01 sec)  
   
mysql\> select @@session.transaction\_isolation;  
+---------------------------------+  
| @@session.transaction\_isolation |  
+---------------------------------+  
| READ\-UNCOMMITTED                |  
+---------------------------------+  
1 row in set (0.00 sec)

在终端 A 开启事务并查询熊二的账户余额：

mysql\> begin;  
Query OK, 0 rows affected (0.00 sec)  
   
mysql\> select \* from account where id \= 2;  
+----+--------+---------+  
| id | name   | balance |  
+----+--------+---------+  
|  2 | 熊二 |     400 |  
+----+--------+---------+  
1 row in set (0.00 sec)

此时熊二的账户余额为 400 元。

在终端 B 开启事务并向熊二的账户余额添加 50 元：

mysql\> begin;  
Query OK, 0 rows affected (0.01 sec)  
   
mysql\> update account set balance \= balance + 50 where id \= 2;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0

此时在终端 A 查询熊二的账户余额：

mysql\> select \* from account where id \= 2;  
+----+--------+---------+  
| id | name   | balance |  
+----+--------+---------+  
|  2 | 熊二 |     450 |  
+----+--------+---------+  
1 row in set (0.00 sec)

可以发现，终端 A 事务读取到了终端 B 事务还未提交的数据，这个问题就是**脏读**。终端 A 事务两次读取的数据不一致，这个问题就是**不可重复读**。

如果此时终端 B 事务回滚，而终端 A 事务对熊二账户余额进行减 50 元，结果会是什么样？

终端 B 事务回滚并查询到余额为 400 元：

mysql\> rollback;  
Query OK, 0 rows affected (0.01 sec)  
  
mysql\> select \* from account where id \= 2;  
+----+--------+---------+  
| id | name   | balance |  
+----+--------+---------+  
|  2 | 熊二 |     400 |  
+----+--------+---------+  
1 row in set (0.00 sec)

终端 A 事务修改并提交，查询到余额为 350 元：

mysql\> update account set balance \= balance \- 50 where id \= 2;  
Query OK, 1 row affected (0.01 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
mysql\> commit;  
Query OK, 0 rows affected (0.02 sec)  
  
mysql\> select \* from account where id \= 2;  
+----+--------+---------+  
| id | name   | balance |  
+----+--------+---------+  
|  2 | 熊二 |     350 |  
+----+--------+---------+  
1 row in set (0.00 sec)

以下是整个事件的时间线：

| 时间线 | 终端 A 事务 | 终端 B 事务 |
| --- | --- | --- |
| ① | begin; |  |
| ② | select \* from account where id = 2; 读到熊二的账户余额为 400 元 |  |
| ③ |  | begin; |
| ④ |  | update account set balance = balance + 50 where id = 2; |
| ⑤ | select \* from account where id = 2; 读到熊二的账户余额为 450 元 **（脏读+不可重复读）** |  |
| ⑥ |  | rollback; |
| ⑦ |  | select \* from account where id = 2; 读到熊二的账户余额为 400 元 |
| ⑧ | update account set balance = balance - 50 where id = 2; |  |
| ⑨ | commit; |  |
| ⑩ | select \* from account where id = 2; 读到熊二的账户余额为 350 元 **(不受影响)** |  |

那么脏读有什么影响呢？

在应用程序中，如果一个事务读到脏数据，并作为其他业务逻辑的依据或者进行其他处理，但其并不知道其他会话回滚了事务，那么后续的整个逻辑处理都可能存在问题。

### 读已提交（read committed）

开启两个终端分别为 A 和 B，登录 MySQL，并将当前终端的事务隔离级别设置为读已提交 read committed：

mysql\> set session transaction isolation level read committed;  
Query OK, 0 rows affected (0.00 sec)  
   
mysql\> select @@session.transaction\_isolation;  
+---------------------------------+  
| @@session.transaction\_isolation |  
+---------------------------------+  
| READ\-COMMITTED                  |  
+---------------------------------+  
1 row in set (0.00 sec)

在终端 A 开启事务并查询光头强的账户余额：

mysql\> begin;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql\> select \* from account where id \= 3;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  3 | 光头强 |     500 |  
+----+-----------+---------+  
1 row in set (0.00 sec)

此时光头强的账户余额为 500 元。

在终端 B 开启事务，为光头强的账户余额增加 100 元：

mysql\> begin;  
Query OK, 0 rows affected (0.01 sec)  
   
mysql\> update account set balance \= balance + 100 where id \= 3;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0

此时终端 A 事务再次查询光头强的账户余额，查询到余额仍为 500 元，**说明不存在脏读问题**：

mysql\> select \* from account where id \= 3;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  3 | 光头强 |     500 |  
+----+-----------+---------+  
1 row in set (0.00 sec)

在终端 B 提交事务：

mysql\> commit;  
Query OK, 0 rows affected (0.01 sec)

此时终端 A 事务再次查询光头强的账户余额，查询到余额为 600 元：

mysql\> select \* from account where id \= 3;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  3 | 光头强 |     600 |  
+----+-----------+---------+  
1 row in set (0.00 sec)

可以发现终端 A 事务相同条件下两次查询的结果不一致，这个问题就是**不可重读读**。

以下是整个事件的时间线：

| 时间线 | 终端 A 事务 | 终端 B 事务 |
| --- | --- | --- |
| ① | begin; |  |
| ② | select \* from account where id = 3; 读到光头强的账户余额为 500 元 |  |
| ③ |  | begin; |
| ④ |  | update account set balance = balance + 100 where id = 3; |
| ⑤ | select \* from account where id = 3; 读到光头强的账户余额为 500 元 **（不存在脏读）** |  |
| ⑥ |  | commit; |
| ⑦ | select \* from account where id = 3; 读到光头强的账户余额为 600 元 **（不可重复读）** |  |
| ⑧ | commit; |  |

### 可重复读（repeatable read）

开启两个终端分别为 A 和 B，登录 MySQL，并将当前终端的事务隔离级别设置为可重复读 repeatable read：

mysql\> set session transaction isolation level repeatable read;  
Query OK, 0 rows affected (0.00 sec)  
   
mysql\> select @@session.transaction\_isolation;  
+---------------------------------+  
| @@session.transaction\_isolation |  
+---------------------------------+  
| REPEATABLE\-READ                 |  
+---------------------------------+  
1 row in set (0.00 sec)  
  
mysql\> select \* from account;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  1 | 熊大    |     300 |  
|  2 | 熊二    |     350 |  
|  3 | 光头强 |     600 |  
+----+-----------+---------+  
3 rows in set (0.00 sec)

此时查询熊大、熊二、光头强的账户余额分别为 300 元、350 元、600 元。

在终端 A 开启事务并查询光头强的账户余额：

mysql\> begin;  
Query OK, 0 rows affected (0.01 sec)  
   
mysql\> select \* from account where id \= 3;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  3 | 光头强 |     600 |  
+----+-----------+---------+  
1 row in set (0.00 sec)

此时光头强的账户余额为 600 元。

在终端 B 开启事务，为光头强的账户余额增加 100 元：

mysql\> begin;  
Query OK, 0 rows affected (0.00 sec)  
   
mysql\> update account set balance \= balance + 100 where id \= 3;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0

此时终端 A 事务再次查询光头强的账户余额，查询到余额仍为 600 元，**说明不存在脏读问题**：

mysql\> select \* from account where id \= 3;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  3 | 光头强 |     600 |  
+----+-----------+---------+  
1 row in set (0.00 sec)

在终端 B 提交事务：

mysql\> commit;  
Query OK, 0 rows affected (0.01 sec)

此时终端 A 事务再次查询光头强的账户余额，查询到余额仍为 600 元，**说明不存在不可重复读问题**：：

mysql\> select \* from account where id \= 3;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  3 | 光头强 |     600 |  
+----+-----------+---------+  
1 row in set (0.00 sec)

以下是整个事件的时间线：

| 时间线 | 终端 A 事务 | 终端 B 事务 |
| --- | --- | --- |
| ① | begin; |  |
| ② | select \* from account where id = 3; 读到光头强的账户余额为 600 元 |  |
| ③ |  | begin; |
| ④ |  | update account set balance = balance + 100 where id = 3; |
| ⑤ | select \* from account where id = 3; 读到光头强的账户余额为 600 元 **（不存在脏读）** |  |
| ⑥ |  | commit; |
| ⑦ | select \* from account where id = 3; 读到光头强的账户余额为 600 元 **（不存在不可重复读）** |  |
| ⑧ | commit; |  |

**但在可重复读的事务隔离级别下，仍然存在幻读问题。**下面我们来复现一下。

在终端 A 开启事务并查询 id 大于 2 的账户信息：

mysql\> begin;  
Query OK, 0 rows affected (0.01 sec)  
   
mysql\> select \* from account where id \> 2;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  3 | 光头强 |     700 |  
+----+-----------+---------+  
1 row in set (0.00 sec)

可以看到查询得到的结果是 1 条数据。

在终端 B 开启事务，插入一条数据并提交：

mysql\> begin;  
Query OK, 0 rows affected (0.00 sec)  
   
mysql\> insert into account (name,balance) values('吉吉国王',400);  
Query OK, 1 row affected (0.00 sec)  
   
mysql\> commit;  
Query OK, 0 rows affected (0.01 sec)

此时终端 A 再次查询 id 大于 2 的账户信息：

mysql\> select \* from account where id \> 2;  
+----+-----------+---------+  
| id | name      | balance |  
+----+-----------+---------+  
|  3 | 光头强 |     700 |  
+----+-----------+---------+  
1 row in set (0.00 sec)

得到的结果仍然是相同的 1 条数据。为什么？说好的幻读呢？其实这是 MySQL 为了提高性能，使用了基于乐观锁的 MVCC（多版本并发控制）机制来避免了幻读问题，但幻读问题仍然存在。

如果终端 A 事务执行把 id 大于 2 的账户余额都修改为 800：

mysql\> update account set balance \= 800 where id \> 2;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 2  Changed: 1  Warnings: 0  
   
mysql\> select \* from account where id \> 2;  
+----+--------------+---------+  
| id | name         | balance |  
+----+--------------+---------+  
|  3 | 光头强    |     800 |  
|  4 | 吉吉国王 |     800 |  
+----+--------------+---------+  
2 rows in set (0.00 sec)

此时可以看到查询得到的结果是 2 条数据。

以下是整个事件的时间线：

| 时间线 | 终端 A 事务 | 终端 B 事务 |
| --- | --- | --- |
| ① | begin; |  |
| ② | select \* from account where id > 2; 读到账户信息数据是 1 条 |  |
| ③ |  | begin; |
| ④ |  | insert into account (name,balance) values('吉吉国王',400); |
| ⑤ |  | commit; |
| ⑥ | select \* from account where id > 2; 读到账户信息数据是 1 条 |  |
| ⑦ | update account set balance = 800 where id > 2; |  |
| ⑧ | select \* from account where id > 2; 读到账户信息数据是 2 条 **（存在幻读）** |  |
| ⑨ | commit; |  |

幻读无法通过行级锁来解决，需要使用串行化的事务隔离级别，但这种事务隔离级别会极大的降低数据库的并发能力。

### 串行化（serializable）

开启两个终端分别为 A 和 B，登录 MySQL，并将当前终端的事务隔离级别设置为串行化 serializable：

mysql\> set session transaction isolation level serializable;  
Query OK, 0 rows affected (0.01 sec)  
   
mysql\> select @@session.transaction\_isolation;  
+---------------------------------+  
| @@session.transaction\_isolation |  
+---------------------------------+  
| SERIALIZABLE                    |  
+---------------------------------+  
1 row in set (0.00 sec)

在终端 A 开启事务并查询 id 大于 2 的账户信息：

mysql\> begin;  
Query OK, 0 rows affected (0.00 sec)  
   
mysql\> select \* from account where id \> 2;  
+----+--------------+---------+  
| id | name         | balance |  
+----+--------------+---------+  
|  3 | 光头强    |     800 |  
|  4 | 吉吉国王 |     800 |  
+----+--------------+---------+  
2 rows in set (0.00 sec)

可以看到查询得到的结果是 2 条数据。

在终端 B 开启事务，插入一条数据：

mysql\> begin;  
Query OK, 0 rows affected (0.01 sec)  
   
mysql\> insert into account (name,balance) values('蹦蹦',600);  
1205 \- Lock wait timeout exceeded; try restarting transaction

可以看到，在终端 B 事务执行新增操作时，会发生阻塞，锁超时后会抛出 `1205 - Lock wait timeout exceeded; try restarting transaction` 错误，避免了幻读。可以通过 `select * from performance_schema.data_locks;` 查看事务的锁信息，从 `supremum pseudo-record` 获知，通过添加间隙锁解决幻读问题。此处本文不详细展开，后续单独讲解。

> **supremum pseudo-record**
> 
> 相当于比索引中所有值都大，但却不存在索引中，相当于最后一行之后的间隙锁。

## 总结

本节主要介绍了 MySQL 的事务隔离级别，通过实战演示讲解了不同隔离级别解决的问题以及存在的问题，从中我们可以初步了解 MySQL 事务的隔离机制是通过锁机制和 MVCC (多版本并发控制) 实现的。

下一篇文章我们主要介绍 MySQL 的锁机制，通过实践学习不同事务隔离级别下的加锁情况。

## 参考

事务隔离级别：[dev.mysql.com/doc/refman/…](https://link.juejin.cn/?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Finnodb-transaction-isolation-levels.html)

作者：朝雾轻寒 链接：[https://juejin.cn/post/7136112451959848991](https://juejin.cn/post/7136112451959848991) 来源：稀土掘金 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。