MySQL的RR隔离级别与幻读问题

2019-11-03 本文已影响0人 BigFish__

最近在网上看了不少mysql锁的文章，不少文章都提到InnoDB的RR隔离级别（Repeatable Read）无法解决幻读的问题。对此问题作者亲自做了一些实验，将实验结论记录在此。

本次实验的mysql版本为5.7.22。

理解本次实验所需具备的基础知识

此篇文章的重点在于通过实验的形式解释清楚InnoDB的RR隔离级别是否解决了幻读问题。所以文中将不会对一些相关的概念进行解释，默认读者已经具备相关知识。如果读者对于以下的知识点不甚清楚，最好自行查阅相关资料，理解清楚之后再阅读接下来的实验内容，以免造成困惑。

进行此次实验需要具备的知识点（包括但不限于）：

InnoDB的事务
InnoDB的MVVC（Multi-Version Concurrency Control，多版本并发控制）
InnoDB存储引擎的四种隔离级别
脏读、可重复读和幻读的概念
InnoDB的锁机制（共享锁S，排它锁X，表锁，行锁）
next-key lock和gap lock

测试用数据表

创建表结构：

CREATE TABLE tmp_table (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255));

创建两条数据：

INSERT INTO tmp_table (name) values ('andy'),('jerry');

最终的表数据如下：

id	name
1	andy
2	jerry

进行实验

打开两个终端，连上mysql，分别启动事务a和事务b。

在事务a和事务b上面分别执行如下命令：

事务a	事务b
start transaction;	start transaction;
insert into tmp_table (name) values ('newa');	--
--	select * from tmp_table;
select * from tmp_table;	--

查询出来的结果如下：
事务a：

id	name
1	andy
2	jerry
3	newa

事务b:

id	name
1	andy
2	jerry

很明显事务b没有查询到事务a未提交的新插入数据。原因也很简单，因为普通的select语句是快照读，而事务b启动时，它的快照数据就已经被版本锁定了。

如果事务b进行当前读是否能够读取到事务a未提交的新插入数据呢？

那么我们在事务b里面执行如下命令来看看执行结果：

select * from tmp_table lock in share mode;

执行完成之后我们发现事务b此时会block住，原因是事务a的insert语句排它锁住了id为3的新插入数据，而事务b想请求所有行的共享锁，肯定是需要等待的。

那么此时事务b当前读id为1或2的数据（非事务a新插入数据）是否可行呢？
结论是可行的，因为tmp_table存在唯一键，且事务a的insert语句只是锁住了id为3的行。所以其他事务获取其他行的共享锁是可行的。读者可以自行测试，这里就不做演示了。

事务a提交之后，事务b此时能否读取到事务a新插入的数据呢？

事务a和事务b执行如下命令：

事务a	事务b
commit;	--
--	select * from tmp_table;

事务b打印的结果：

id	name
1	andy
2	jerry

还是一样，因为普通select是快照读，事务b还是读取到的是快照数据，所以不包含事务a提交之后的新数据。

如果此时事务b使用当前读，能否获取到事务a已提交的新插入数据呢？

让我们在事务b下面使用共享锁查看当前版本数据：

select * from tmp_table lock in share mode;

结果如下：

id	name
1	andy
2	jerry
3	newa

可以查询到事务a已提交的新数据，所以此时使用当前读就产生了幻读。

另一种情况的幻读

还有另一种情况也会产生幻读，并且只需要执行普通的select语句。下面请看演示。

在事务b下面执行如下两条语句：

update tmp_table set name='bbb' where id = 3;  
select * from tmp_table;

第一条命令使用update更新了事务a已提交的新数据，第二条命令通过普通的select语句查看快照数据。

打印结果如下：

id	name
1	andy
2	jerry
3	bbb

可以看到事务a已提交的新数据被事务b使用update语句更新了，并且通过普通的select语句给查询出来了，很显然，出现了幻读。

结论

所以说InnoDB的RR隔离级别没有或者解决了幻读问题都不太准确。应该说它并没有完全解决幻读的问题。

如果在同一个事务里面，只是总是执行普通的select快照读，是不会产生幻读的。

但是如果在这个事务里面通过当前读或者先更新然后快照读的形式来读取数据，就会产生幻读。

参考

Phantom Rows
Innodb 中 RR 隔离级别能否防止幻读？