侧边栏壁纸
博主头像
码森林博主等级

一起走进码森林，享受编程的乐趣，发现科技的魅力，创造智能的未来!

  • 累计撰写 146 篇文章
  • 累计创建 74 个标签
  • 累计收到 4 条评论
标签搜索

目 录CONTENT

文章目录
深入理解事务 数据库

MySQL 事务隔离级别详解

码森林
码森林
2022-02-10 / 0 评论 / 0 点赞 / 550 阅读 / 4,012 字 / 正在检测是否收录...
温馨提示：
本文最后更新于 2022-08-26，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

从 《事务的基本概念》 一文中我们了解了什么是事务以及并发事务带来的问题，数据库通过不同的事务隔离级别来解决这些问题，本文主要详细讲解 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 的锁机制，通过实践学习不同事务隔离级别下的加锁情况。

参考

事务隔离级别：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

0
MySQL 事务
版权归属： 码森林
本文链接： https://www.masenlin.com/archives/mysql事务隔离级别详解
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区