mysql 事物隔离级别
MySQL 事务隔离级别
SQL:1992 标准定义了四种事务隔离级别：
- 读未提交（Read Uncommitted）
- 读已提交（Read Committed）
- 可重复读（Repeatable Read）
- 串行化（Serializable）
各种隔离级别和数据库事务并发时存在的问题对应情况如下：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | | —————- | —- | ———- | —- | | Read Uncommitted | √ | √ | √ | | Read Committed | × | √ | √ | | Repeatable Read | × | × | √ | | Serializable | × | × | × |
- 读未提交（Read Uncommitted） 允许脏读，就是在该隔离级别下，可能读到其他会话未提交事务修改的数据，存在脏读、不可重读读、幻读的问题。
- 读已提交（Read Committed） 只能查询到已提交的数据。这是 Oracle 数据库默认的事务隔离级别。存在不可重读读、幻读的问题。
- 可重复读（Repeatable Read） 就是在一个事务里相同条件下，无论何时查到的数据都和第一次查到的数据一致。这是 MySQL 数据库 InnoDB 引擎默认的事务隔离级别。在范围查询时存在幻读的问题。
- 串行化（Serializable） 是最高的事务隔离级别，它严格服从 ACID 特性的隔离级别。所有的事务依次逐个执行，事务之间互不干扰，该级别可以防止脏读、不可重复读以及幻读。但每个事务读数据时都需要获取表级的共享锁，导致读和写都会阻塞，性能极低。
- 脏读: A 事务读取到了终端 B 事务还未提交的数据。
- 不可重复读: 终端 A 事务两次读取的数据不一致。
- 幻读: 范围查询中， 终端 A 事务两次查到的数据不一致。
如何设置 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 (多版本并发控制) 实现的。
支付宝打赏 
微信打赏