MySQL在REPEATABLE READ隔离级别下解决幻读了吗？

爬蜥

发布于 2022-03-07 15:25:14

5540

发布于 2022-03-07 15:25:14

文章被收录于专栏：爬蜥的学习之旅爬蜥的学习之旅

REPEATABLE READ是InnoDB的默认隔离级别。MySQL对它有如下的支持：

同一个事务内的一致性读是读取的第一次读数据时产生的快照。这意味着在同一个事务内，如果多次执行非锁的Select语句，他们彼此的结果是一致
对于加锁读、更新、删除语句，加锁取决于语句是否使用了唯一索引作为查询条件还是范围类型查找条件
1. 如果是使用了唯一查询条件，InnoDB仅给找到的索引记录加锁，而不使用gap
2. 其它的查询条件，InnoDB会给索引扫描范围加锁。通过使用 gap锁或者next-key锁来阻塞其它会话在查询范围所覆盖的间隙进行插入

幻读的定义

在不同时间内执行相同的查询产生了不同的结果集。

基于MySQL 5.7在RR隔离级别上的测试

测试准备工作

准备一张测试表，刚开始的时候没有任何数据

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

它的隔离级别为 RR（InnoDB默认）

测试1：非锁Select在多个事务存在情况下RR的幻读表现

时序	事务1	事务2
1	begin	begin
2	mysql> select * from test; Empty set (0.00 sec)
3		mysql> insert into test (name,age) value("t1",1);Query OK, 1 row affected (0.00 sec)
4	mysql> select * from test; Empty set (0.00 sec)
5		mysql> commit;Query OK, 0 rows affected (0.00 sec)
6	mysql> select * from test; Empty set (0.00 sec)
7	mysql> commit;Query OK, 0 rows affected (0.00 sec)

可以看到事务2执行insert后，无论是执行commit前还是commit后，事务1都无法读取到事务2的结果，在事务1commit后，再次读取，才拿到了最新的数据

mysql> select * from test;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | t1   |    1 |
+----+------+------+
1 row in set (0.00 sec)

这个结果是符合预期的，印证了同一个事务内的一致性读是读取的第一次读数据时产生的快照。这意味着在同一个事务内，如果多次执行非锁的Select语句，他们彼此的结果是一致

测试2：非无锁Select在多个事务存在情况下RR的幻读表现

在开始测试之前，先往test表中多插入几条记录

insert into test(name,age)values("t2",10),("t3",20);

测试时序如下

时序	事务1	事务2	事务3
1	begin	begin	begin
2	mysql> select name from test where age>=10 and age<=20\G;--- 1. row ---name: t2--- 2. row ---name: t32 rows in set (0.00 sec)
3	mysql> update test set name ="t_update" where age=20;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
4		mysql> insert into test (name,age) values('t4',15);此处发现有等待	mysql> insert into test (name,age) values('t5',5);Query OK, 1 row affected (0.00 sec)
5	mysql> select name from test where age>=10 and age<=20\G;--- 1. row ---name: t2--- 2. row ---name: t_update2 rows in set (0.00 sec)
6	commit;	事务1执行完commit后，事务2立马执行
7		commit;	commit;

三个事务的执行，可以看到如下现象

在同一个事务内的修改，后续的读取是能够拿到最新的结果
在多个事务同时执行的时候，先执行的事务如果执行了涉及加锁的语句，那么后执行的事务在索引的间隙处是无法执行的，而非间隙处能执行成功

能达到第2点的效果，实际上是MySQL利用了next-key locking,从而保证不会出现幻读

经验证，如果事务2插入的age值是 10或者21都无法执行成功，也就是说，索引的两边间隙都加了锁

但是值得注意的是现象1，当在事务内进行修改的时候，事务本身是能够读到这个修改后的值的

InnoDB一致性读

InnoDB一致性读是指InnoDB通过多版本控制，使得在一个时间节点，查询的是快照，这种方式使得查询能够看到在这个时间点之前的提交，但是无法看到时间点后的改动或者未提交的事务。这种方式有一个例外，就是在同一个事务内，能够查到之前语句做的修改。这个例外会导致有一个异常情况的出现：如果更新了表中的某些行，SELECT会查到更新行的最新版本，也会查到任意行的老版本；那么如果其它会话同时更新了相同的表，这种异常就会使得在原来的事务内能够看到之前从未存在的数据

测试3：在测试2的基础上调整事务2insert的执行顺序

时序	事务1	事务2
1	begin	begin
2	mysql> select * from test where age=100;Empty set (0.00 sec)
3		mysql> insert into test (name,age) values('t6',100);Query OK, 1 row affected (0.00 sec)
4		mysql> commit;
5	mysql> select * from test where age=100;Empty set (0.00 sec)
6	mysql> update test set name="t6_update" where age=100;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
7	mysql> select name from test where age=100\G;--1. row --name: t6_update1 row in set (0.00 sec)
8	commit;

这种情况下可以看到如下现象：

在事务2提交新的数据之后，事务1是无法读到事务2的结果的
在事务1如果执行更改事务2提交的数据的语句，是能执行成功，而且后续事务1就能查到这些数据

对此MySQL文档的本身解释如下：

数据库状态的快照仅适用于一个事务内的Select语句，对于DML(insert/update/delete/locking select)则不是。如果并发的事务插入或者修改了一些行，并且提交了，其它并发执行的事务中，即使刚提交的改动是查不到，但DELETE/UPDATE语句即使在RR隔离级别也会影响这样数据