InnoDB在RR隔离级别下到底有没有解决幻读问题？

一根甜苦瓜

已于 2024-10-11 13:21:05 修改

阅读量611

点赞数 27

分类专栏： MYSQL技术文章标签： mysql

于 2024-10-11 13:18:06 首次发布

本文链接：https://blog.csdn.net/qq_34481955/article/details/142813086

版权

MYSQL技术专栏收录该内容

2 篇文章 0 订阅

订阅专栏

更多博客：香蕉粗的黄鳝
大家好，我是一根甜苦瓜。
今天就来聊一个面试时经常会被问到的问题（老八股了）

请问InnoDB在RR级别(可重复读)下，完全解决了幻读问题吗？

1. 什么是幻读

简单来说，幻读就是在一个事务内多次执行SELECT COUNT(*) 查询结果不一致。造成的原因大概率是因为其他事务在这个事务执行期间插入/删除了数据，导致查询记录数量不一致。

比如下面的场景：

事务 A：执行了一次查询，查找所有 age > 18 的用户，得到 10 条记录。
事务 B：在事务 A 运行期间，插入了一条age = 20的用户记录。
事务 A：再次查询 age > 18 的用户，此时得到了 11 条记录(事务B插入的数据也被统计进来了)。

在这个过程中，事务 A 的两次查询得到了不一致的结果，第二次多了一条记录，这种现象就是幻读。

2. InnoDB在RR级别下解决了幻读问题吗

先不聊结论，先弄点数据测试一下

1. 创建表

使用下面的SQL创建一个表，并插入2条测试数据：

CREATE TABLE `user` (
  `id` int NOT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test`.`user` (`id`, `age`) VALUES (1, 11);
INSERT INTO `test`.`user` (`id`, `age`) VALUES (2, 22);

此时user表的情况如下：
在这里插入图片描述
下面就来模拟幻读的场景，不过在开始之前请确保下面的操作完成:

首先所有的事务都是在RR级别下(可以使用SHOW VARIABLES LIKE 'transaction_isolation';来查询当前事务的隔离级别)，并且每个事务都执行 set autocommit = 0; 以防止每执行一条SQL语句就自动提交事务。

1. 多个事务同时快照读

分别启动两个事务A和B，A事务负责查询表中的记录数，B事务负责插入一条记录。他们的执行顺序如下图（从上到下）：

在这里插入图片描述

那么上图中两个【?】分别得出的结果是多少？

答案是 2 和 2，没有出现幻读场景

由于普通的查询语句 select * from user 是快照读。

快照读就意味着在A事务开启的时候，A事务对表中的数据 拍摄了一个快照，这个快照就像一个照片一样，只记录了A事务开启瞬间整个数据库的情况，后续的其他事务对数据库的修改，对于这张“照片”来说，都是无法被感知的。

所以在B事务插入数据的时候，A事务的快照中并没有感知到这条数据，根据MVCC机制，A事务无论进行多少次 select * from user，都读不到B事务插入的新数据。

下面验证一下：

阶段一： A事务查询数据，简单的快照读，读出2条数据
阶段二： B事务插入一条数据，没有冲突，提交成功
阶段三：A事务再次查询数据，读取的是 read-view中的数据，所以依然读出2条数据

结论一：在可重复读隔离级别下(RR)，普通的查询语句是一种快照读，由于MVCC机制的存在，对于事务执行期间新插入的数据并不可见，所以不会出现幻读问题。

2. 先当前读再快照读

下面换一个思路，将A事务的第一次查询改为当前读，执行顺序如下图（从上到下）：
在这里插入图片描述

此时上图中两个**“?”**分别得出的结果是多少？

答案是，第一个"?“是 3，第二个”?"被阻塞

由于A事务的第一次查询 select * from user lock in share mode;是一个当前读（lock in share mode意味着加了读锁）。

当前读意味着在A事务查询的时候，对表中的数据加了共享锁，所以在B事务插入数据的时候，B事务被阻塞了无法插入数据，直到A事务提交或者回滚。

阶段一： A事务查询数据，加了读锁，读出3条数据
阶段二： B事务插入一条数据，被阻塞

可以使用下面的命令来查看当前数据库的上锁情况

select * from performance_schema.data_locks;

如下图，其实就是加了 next-key 锁（记录锁+GAP 锁），会锁住所有数据之间的间隙，这会阻止其他事务往锁定范围内插入数据。(由于存在唯一索引，所以记录锁+GAP 锁会退化成 RECORD LOCK。)
在这里插入图片描述
结论二：对于当前读来说，通过 next-key 锁的方式解决了幻读问题。如果有其他事务在 next-key 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入。所以也规避了幻读问题。