作者:小林coding
八股文网站:xiaolincoding.com
大家好,我是小林。
之前有位小伙伴美团三面,一直被追求「幻读是否被 MySQL 可重复度隔离级别彻底解决了?」
之前我也提到过,MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」,但是它很大程度上避免幻读现象(并不是完全解决了),解决的方案有两种:
这次,我会举例两个实验场景来说明 MySQL InnoDB 引擎的可重复读隔离级别发生幻读的问题。
好了,发车!
首先来看看 MySQL 文档是怎么定义幻读(Phantom Read)的:
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
翻译:当同一个查询在不同的时间产生不同的结果集时,事务中就会出现所谓的幻象问题。例如,如果 SELECT 执行了两次,但第二次返回了第一次没有返回的行,则该行是“幻像”行。
举个例子,假设一个事务在 T1 时刻和 T2 时刻分别执行了下面查询语句,途中没有执行其他任何语句:
SELECT * FROM t_test WHERE id > 100;
只要 T1 和 T2 时刻执行产生的结果集是不相同的,那就发生了幻读的问题,比如:
当多个事务并发执行时可能会遇到「脏读、不可重复读、幻读」的现象,这些现象会对事务的一致性产生不同程序的影响。
这三个现象的严重性排序如下:
图片
SQL 标准提出了四种隔离级别来规避这些现象,隔离级别越高,性能效率就越低,这四个隔离级别如下:
针对不同的隔离级别,并发事务时可能发生的现象也会不同。
图片
也就是说:
所以,要解决脏读现象,就要升级到「读提交」以上的隔离级别;要解决不可重复读现象,就要升级到「可重复读」的隔离级别,要解决幻读现象不建议将隔离级别升级到「串行化」。
不同的数据库厂商对 SQL 标准中规定的 4 种隔离级别的支持不一样,有的数据库只实现了其中几种隔离级别,我们讨论的 MySQL 虽然支持 4 种隔离级别,但是与SQL 标准中规定的各级隔离级别允许发生的现象却有些出入。
MySQL 在「可重复读」隔离级别下,可以很大程度上避免幻读现象的发生(注意是很大程度避免,并不是彻底避免),所以 MySQL 并不会使用「串行化」隔离级别来避免幻读现象的发生,因为使用「串行化」隔离级别会影响性能。
MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」,但是它很大程度上避免幻读现象(并不是完全解决了),解决的方案有两种:
可重复读隔离级是由 MVCC(多版本并发控制)实现的,实现的方式是启动事务后,在执行第一个查询语句后,会创建一个 Read View,后续的查询语句利用这个 Read View,通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据,所以事务过程中每次查询的数据都是一样的,即使中途有其他事务插入了新纪录,是查询不出来这条数据的,所以就很好了避免幻读问题。
做个实验,数据库表 t_stu 如下,其中 id 为主键。
然后在可重复读隔离级别下,有两个事务的执行顺序如下:
从这个实验结果可以看到,即使事务 B 中途插入了一条记录,事务 A 前后两次查询的结果集都是一样的,并没有出现所谓的幻读现象。
MySQL 里除了普通查询是快照读,其他都是当前读,比如 update、insert、delete,这些语句执行前都会查询最新版本的数据,然后再做进一步的操作。
这很好理解,假设你要 update 一个记录,另一个事务已经 delete 这条记录并且提交事务了,这样不是会产生冲突吗,所以 update 的时候肯定要知道最新的数据。
另外,select ... for update
这种查询语句是当前读,每次执行的时候都是读取最新的数据。
接下来,我们假设select ... for update
当前读是不会加锁的(实际上是会加锁的),在做一遍实验。
这时候,事务 B 插入的记录,就会被事务 A 的第二条查询语句查询到(因为是当前读),这样就会出现前后两次查询的结果集合不一样,这就出现了幻读。
所以,Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题,就引出了间隙锁。(额外提一句,读提交隔离级别,是没有间隙锁的,只有记录锁)
假设,表中有一个范围 id 为(3,5)间隙锁,那么其他事务就无法插入 id = 4 这条记录了,这样就有效的防止幻读现象的发生。
举个具体例子,场景如下:
事务 A 执行了这条当前读语句后,就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock(next-key lock 是间隙锁+记录锁的组合)。
然后,事务 B 在执行插入语句的时候,判断到插入的位置被事务 A 加了 next-key lock,于是事物 B 会生成一个插入意向锁,同时进入等待状态,直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。
可重复读隔离级别下虽然很大程度上避免了幻读,但是还是没有能完全解决幻读。
我举例两个可重复读隔离级别发生幻读现象的场景。
还是以这张表作为例子:
事务 A 执行查询 id = 5 的记录,此时表中是没有该记录的,所以查询不出来。
# 事务 A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_stu where id = 5;
Empty set (0.01 sec)
然后事务 B 插入一条 id = 5 的记录,并且提交了事务。
# 事务 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_stu values(5, '小美', 18);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
此时,事务 A 更新 id = 5 这条记录,对没错,事务 A 看不到 id = 5 这条记录,但是他去更新了这条记录,这场景确实很违和,然后再次查询 id = 5 的记录,事务 A 就能看到事务 B 插入的纪录了,幻读就是发生在这种违和的场景。
# 事务 A
mysql> update t_stu set name = '小林coding' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t_stu where id = 5;
+----+--------------+------+
| id | name | age |
+----+--------------+------+
| 5 | 小林coding | 18 |
+----+--------------+------+
1 row in set (0.00 sec)
整个发生幻读的时序图如下:
在可重复读隔离级别下,事务 A 第一次执行普通的 select 语句时生成了一个 ReadView,之后事务 B 向表中新插入了一条 id = 5 的记录并提交。接着,事务 A 对 id = 5 这条记录进行了更新操作,在这个时刻,这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id,之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了,于是就发生了幻读。
因为这种特殊现象的存在,所以我们认为 MySQL Innodb 中的 MVCC 并不能完全避免幻读现象。
除了上面这一种场景会发生幻读现象之外,还有下面这个场景也会发生幻读现象。
要避免这类特殊场景下发生幻读的现象的话,就是尽量在开启事务之后,马上执行 select ... for update 这类当前读的语句,因为它会对记录加 next-key lock,从而避免其他事务插入一条新记录。
MySQL InnoDB 引擎的可重复读隔离级别(默认隔离级),根据不同的查询方式,分别提出了避免幻读的方案:
我举例了两个发生幻读场景的例子。
第一个例子:对于快照读, MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录,那么事务 A 前后两次查询的记录条目就不一样了,所以就发生幻读。
第二个例子:对于当前读,如果事务开启后,并没有执行当前读,而是先快照读,然后这期间如果其他事务插入了一条记录,那么事务后续使用当前读进行查询的时候,就会发现两次查询的记录条目就不一样了,所以就发生幻读。
所以,MySQL 可重复读隔离级别并没有彻底解决幻读,只是很大程度上避免了幻读现象的发生。
要避免这类特殊场景下发生幻读的现象的话,就是尽量在开启事务之后,马上执行 select ... for update 这类当前读的语句,因为它会对记录加 next-key lock,从而避免其他事务插入一条新记录。