MySQL | 事务隔离级别详解

Mr.Dabao2022年11月23日大约 12 分钟

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 的锁机制,通过实践学习不同事务隔离级别下的加锁情况。

Loading...