从 《事务的基本概念》 一文中我们了解了什么是事务以及并发事务带来的问题,数据库通过不同的事务隔离级别来解决这些问题,本文主要详细讲解 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
评论区