MySQL 事务隔离实验-认识:脏读、不可重复读、幻读

6,694 阅读6分钟

0x00 前言

大家也许听说过 MySQL 的事务在高并发执行的时候可能会发生脏读不可重复读幻读等问题。对于有处理高并发经验的老鸟,可能认知会更深一些所以觉得 so easy~「老鸟请点红叉离开,或者发起友好评论O(∩_∩)O哈哈~」,不过对于像我这种难以接触到高并发业务场景的初学者来说,也就只能看几篇博文,了解一下概念,纸上谈兵/(ㄒoㄒ)/~~。不过本着「打破砂锅问到底」的精神,决定通过做实验来提高对其理解,顺便增强记忆(起码找工作被问到还能说两句)。

0x01 MySql 事务隔离级别

MySql 事务隔离级别和允许并发副作用,分别如下表:

事务隔离级别 脏读 不可重复读 幻读
读未提交(read uncommitted)
不可重复读(read committed)
可重复读(repeatable read)
串行化(serializable)

由上表可知,MySQL 共支持四种事务隔离级别。表由上到下允许并发副作用越来越弱,似乎我们只要选择串行化(serializable)的事务隔离级别就不会发生脏读不可重复读幻读等问题了,但是选择串行化(serializable)却会带来一定的性能下降。所以关于如何选择事务隔离级别我们需要对脏读不可重复读幻读有一定认知,并确定这几种副作用对应用的影响,然后选择合适的隔离级别。

MySQL 的默认事务隔离级别为 可重复读(repeatable read) 所以我们不用担心「脏读」和「不可重复读」。

查询 MySQL 事务隔离级别的语句如下:

select @@tx_isolation;
/*
输出结果:
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
*/

设置事务隔离级别:

-- 设置事务隔离级别为 read committed,仅在本次会话中生效
set session transaction isolation level read committed;

或者可以修改 my.cnf 配置文件使其永久生效。

[mysqld]
transaction-isolation = REPEATABLE-READ

0x02 实验环境

本次实验采用 MySql 5.7.21 版本(储存引擎为 Innodb),测试数据表结构如下:

/*
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | <null>  | auto_increment |
| name  | char(20) | NO   |     | <null>  |                |
| money | float    | NO   |     | 0       |                |
+-------+----------+------+-----+---------+----------------+
*/

0x03 脏读

脏读的概念如下:

事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称作脏读。

个人认为脏读的副作用是最大的,现在通过实验证明脏读的危害。

实验users表如下:

/*
+----+------+--------+
| id | name | money  |
+----+------+--------+
| 1  | 小王 | 1000.0 |
| 2  | 小明 |    0.0 |
+----+------+--------+
*/

实验步骤表:

时间 客户端 A 客户端 B
T1 设置事务隔离级别为 read uncommitted 设置事务隔离级别为 read uncommitted
T2 开始事务 A
begin;
T3 小王转款给小明 500 元
update users set money=money-500 where id = 1;
update users set money=money+500 where id = 2;
T4 开始事务 B
begin;
T5 查询小明账户余额
select * from users where id = 2;
查询结果为 500 元,余额充足则执行支付逻辑
T6 小明账户扣款 100 元
update users set money=money-100 where id = 2;
本条语句将会阻塞
T7 事务 A 回滚
rollback;
语句执行完毕
T8 事务 B 提交
commit;

最后我们查询users表,结果如下:

/*
+----+------+--------+
| id | name | money  |
+----+------+--------+
| 1  | 小王 | 1000.0 |
| 2  | 小明 | -100.0 |
+----+------+--------+
*/

令人惊讶的结果,小明的余额变成了 -100 元!这就是脏读的危害,我们重点看上表的 T5,发现在事务 A 还未提交之时事务 B 便已经读取到了事务 A 更新后的结果,这直接导致了我们程序判断余额充足从而执行了扣款的逻辑。如果事务 A 成功提交那么程序结果就是正确的,但是事务 A 最后没有成功提交而是进行了回滚,这就导致了用户余额被扣款为负数的灾难。

0x04 不可重复读

不可重复读的概念如下:

一个事务开始时,只能看见已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。但是两次执行同样的查询,可能会得到不一样的结果。

实验users表如下:

/*
+----+------+--------+
| id | name | money  |
+----+------+--------+
| 1  | 小王 | 1000.0 |
| 2  | 小明 |    0.0 |
+----+------+--------+
*/

实验步骤表:

时间 客户端 A 客户端 B
T1 设置事务隔离级别为 read committed 设置事务隔离级别为 read committed
T2 开始事务 A
begin;
T3 查询小明余额
select * from users where id = 2;
余额为 0 元
T4 开始事务 B
begin;
T5 小明账户充值100元
update users set money=money+100 where id = 2;
T6 事务 B 提交
commit;
T7 查询小明余额
select * from users where id = 2;
余额为 100 元
T8 事务 A 提交
commit;

不可重复读表现在于在同一个事务之中,两个相同的查询得到的查询结果却不同。这是由于两个查询结果之间,出现另外一个事务修改了包含之前查询结果的记录,导致第二次查询与第一次查询结果不同。它与脏读的区别在于修改记录的事务 B 必须提交成功,查询事务 A 才能读取到修改后的记录,如果事务 B 回滚了,事务 A 的查询结果还是一样的。

0x05 幻读

幻读概念如下:

所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行。InnoDB存储引擎通过多版本并发控制(MVCC)解决了幻读的问题。

经过本人测试发现在 可重复读(repeatable read)的事务隔离级别下,MySQL 不会产生幻行但是可以通过写入一行数据来证明幻读问题的存在。

实验users表如下:

/*
+----+------+--------+
| id | name | money  |
+----+------+--------+
| 1  | 小王 | 1000.0 |
| 2  | 小明 |    0.0 |
+----+------+--------+
*/

实验步骤表:

时间 客户端 A 客户端 B
T1 设置事务隔离级别为 repeatable read 设置事务隔离级别为 repeatable read
T2 开始事务 A
begin;
T3 开始事务 B
begin;
T4 插入一行
insert into users(id, name, money) values (3, "小红",1000);
T5 事务 B 提交
commit;
T6 查询users
select * from users;
并无 id 为 3 的记录
T7 插入一行
insert into users(id, name, money) values (3, "小红",1000);
T8 出现报错:(1062, u"Duplicate entry '3' for key 'PRIMARY'")

对于事务 A 来说出现的报错就像见鬼了一样,因为事务 A 在查询 users 表的结果并不存在 id 为 3 的行!而在插入该行时却出现了该行已存在的报错……也许这就是叫幻读的原因吧。

0x06 总结

网上已有很多这种类型的文章,本文也参考了许多内容,之所以还要「老调重弹」是因为「纸上得来终觉浅,绝知此事要躬行」,实践才是检验真理的唯一标准,当然本文也可能出现谬误,欢迎指正。内心 OS:数据库真的后端的一块大头,不想整天 CRUD 就要更深入的学啊。感觉《高性能 MySQL》这本书不错,有空要研读一下,最后觉得很有必要学习关于 MySQL 锁相关的内容。