事务隔离级别

发表于 更新于

本文简单介绍一下数据库的事务隔离级别，所用数据库采用 MySQL，MySQL 提供了两种事务性的存储引擎 InnoDB 和 NDB Cluster，本文示例采用 InnoDB 存储引擎。

查看当前数据的事务隔离级别

1
2
3
4
5
6
7
8
-- 查看当前数据库事务隔离级别
mysql> SHOW VARIABLES LIKE '%transaction_isolation%';
-- 或者
mysql> SELECT @@transaction_isolation;
-- 低版本的 MySQL 查看事务隔离级别的属性是不一样的
mysql> SHOW VARIABLES LIKE '%tx_isolation%';
-- 或者
mysql> SELECT @@tx_isolation;

不同的事务隔离级别所面对的问题

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
读已提交（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否

  • 脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

  • 不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。

  • 幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

脏读的场景

客户端 A 先开启事务，查询用户余额

1
2
3
4
5
6
7
8
9
10
11
12
-- 客户端A设置当前会话级别为`read-uncommitted`
mysq> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 客户端A开启事务
mysq> START TRANSACTION;
-- 客户端A查询用户余额
mysq> SELECT * FROM wallet WHERE user_id = 1;
-- 结果如下：
# +----+---------+-----------+
# | id | user_id | balance   |
# +----+---------+-----------+
# |  1 |       1 | 100000000 |
# +----+---------+-----------+

客户端 B 开启事务，并修改用户余额

1
2
3
4
5
6
-- 客户端B设置当前会话级别为`read-uncommitted`
mysq> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 客户端B开启事务
mysq> START TRANSACTION;
-- 客户端B修改用户余额，
mysql> UPDATE wallet SET balance = balance + 100 WHERE user_id = 1;

客户端 A 查询用户余额

1
2
3
4
5
6
7
8
-- 客户端A获取用户余额（此时由于B的事务并未提交，故A事务读到了B事务已经更新后的数据，此时发生脏读）
mysq> SELECT * FROM wallet WHERE user_id = 1;
-- 结果如下：
# +----+---------+-----------+
# | id | user_id | balance   |
# +----+---------+-----------+
# |  1 |       1 | 100000100 |
# +----+---------+-----------+

客户端 B 回滚事务

1
2
-- 客户端B回滚事务
mysql> ROLLBACK;

客户端 A 修改用户余额

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 客户端A修改用户余额
mysq> UPDATE wallet SET balance = balance - 500 WHERE user_id = 1;

-- 客户端A查询用户余额
mysq> SELECT * FROM wallet WHERE user_id = 1;
-- 结果如下：
# +----+---------+----------+
# | id | user_id | balance  |
# +----+---------+----------+
# |  1 |       1 | 99999500 |
# +----+---------+----------+
-- 对于A客户端来讲，此时的结果是很奇怪的。
-- 客户端A提交事务
mysql> COMMIT;

不可重复读的场景

客户端 A 开启事务，查询用户余额

1
2
3
4
5
6
7
8
9
10
11
12
-- 客户端A设置当前会话级别为`read-committed`
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 客户端A开启事务
mysql> BEGIN;
-- 客户端A查询用户余额
SELECT * FROM wallet WHERE user_id = 1;
-- 结果如下:
# +----+---------+---------+
# | id | user_id | balance |
# +----+---------+---------+
# |  1 |       1 |     200 |
# +----+---------+---------+

客户端 B 开启事务，并修改用户余额

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 客户端B设置当前会话级别为`read-committed`
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 客户端B开启事务
mysql> BEGIN;
-- 客户端B修改用户余额
mysql> UPDATE wallet SET balance = balance + 100 WHERE user_id = 1;
-- 客户端B查询用户余额
mysql> SELECT * FROM wallet WHERE user_id = 1;
-- 结果如下：
# +----+---------+---------+
# | id | user_id | balance |
# +----+---------+---------+
# |  1 |       1 |     300 |
# +----+---------+---------+

客户端 A 查询用户余额

1
2
3
4
5
6
7
8
9
-- 客户端A查询用户余额
mysql> SELECT * FROM wallet WHERE user_id = 1;
-- 结果如下：
# +----+---------+---------+
# | id | user_id | balance |
# +----+---------+---------+
# |  1 |       1 |     200 |
# +----+---------+---------+
-- 此时客户端B的事务还没有提交，故客户端A不能查询到B事务已经更新的数据，解决了脏读的问题

客户端 B 提交事务

1
2
-- 客户端B提交事务
mysql> COMMIT;

客户端 A 查询用户余额

1
2
3
4
5
6
7
8
9
10
11
-- 客户端A查询用户余额
mysql> SELECT * FROM wallet WHERE user_id = 1;
-- 结果如下：
# +----+---------+---------+
# | id | user_id | balance |
# +----+---------+---------+
# |  1 |       1 |     300 |
# +----+---------+---------+
-- 此时客户端B的事务还已经提交，结果与上一步不一致，即产生了不可重复读的问题。
-- 客户端A提交事务
mysql> COMMIT;

幻读的场景

客户端 A 开启事务，查询所有用户

1
2
3
4
5
6
7
8
9
10
11
12
-- 客户端A设置当前会话级别为`repeatable-read`
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 客户端A开启事务
mysql> BEGIN;
-- 客户端A查询所有用户余额
SELECT * FROM wallet;
-- 结果如下:
# +----+---------+---------+
# | id | user_id | balance |
# +----+---------+---------+
# |  1 |       1 |     100 |
# +----+---------+---------+

客户端 B 开启事务，并插入一条记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 客户端B设置当前会话级别为`repeatable-read`
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 客户端B开启事务
mysql> BEGIN;
-- 客户端B查询所有用户余额
SELECT * FROM wallet;
-- 结果如下:
# +----+---------+---------+
# | id | user_id | balance |
# +----+---------+---------+
# |  1 |       1 |     100 |
# +----+---------+---------+
-- 客户端B插入一条记录
mysq> INSERT INTO wallet (`id`, `user_id`, `balance`) VALUES (2, 2, 200);
-- 客户端B查询所有的用户余额
mysql> SELECT * FROM wallet;
-- 结果如下
# +----+---------+---------+
# | id | user_id | balance |
# +----+---------+---------+
# |  1 |       1 |     100 |
# |  2 |       2 |     200 |
# +----+---------+---------+

客户端 A 查询所有用户余额

1
2
3
4
5
6
7
8
-- 客户端A查询所有用户余额, 此时发生幻读。
SELECT * FROM wallet;
-- 结果如下:
# +----+---------+---------+
# | id | user_id | balance |
# +----+---------+---------+
# |  1 |       1 |     100 |
# +----+---------+---------+

客户端 B 提交事务

1
2
-- 客户端B提交事务
mysq> COMMIT;

客户端 A 插入一条记录

1
2
3
4
5
6
-- 客户A插入id为2的记录
mysql> INSERT INTO wallet (`id`, `user_id`, `balance`) VALUES (2, 2, 200);
-- 结果失败了，
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
-- 客户端A回滚
mysql> ROLLBACK;