可重复读无法避免幻读的详细示例

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

首先明确核心前提：MySQL 的 InnoDB 存储引擎中，可重复读（Repeatable Read） 是默认隔离级别，其核心作用是保证「同一事务内，多次读取同一数据，结果始终一致」，可解决「不可重复读」问题，但无法解决「幻读」问题。

幻读的定义：同一事务内，两次执行相同的范围查询（如 WHERE 条件匹配一批数据），第二次查询的结果中，出现了第一次查询没有的新数据（或消失了第一次查询有的数据），这种“凭空出现/消失”的现象，就是幻读。

下面通过「员工工资查询」的具体场景，完整演示幻读的产生过程，所有操作基于 MySQL InnoDB，隔离级别设为可重复读（默认）。

一、环境准备

1. 创建员工表（emp），包含员工ID（id）、姓名（name）、工资（salary）字段，插入初始数据：

-- 创建表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    salary INT NOT NULL
);

-- 插入初始数据（3条员工记录，工资均为5000）
INSERT INTO emp (name, salary) VALUES ('张三', 5000), ('李四', 5000), ('王五', 5000);

二、幻读产生的完整步骤（两个事务交互）

我们设定两个事务：事务A（执行范围查询，查看工资=5000的员工）、事务B（插入/删除工资=5000的员工记录），分步演示幻读的产生。

步骤1：设置隔离级别为可重复读（默认，可省略，但明确演示）

-- 查看当前隔离级别
SELECT @@transaction_isolation; -- 结果：REPEATABLE-READ

-- 若不是，手动设置（两个事务都需确保此隔离级别）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

步骤2：开启事务A，执行第一次范围查询（查询工资=5000的员工）

-- 开启事务A
START TRANSACTION;

-- 第一次查询：范围查询（工资=5000的所有员工）
SELECT * FROM emp WHERE salary = 5000;

-- 执行结果（3条记录）：
-- id | name | salary
-- 1  | 张三 | 5000
-- 2  | 李四 | 5000
-- 3  | 王五 | 5000

此时，事务A的第一次查询结果为3条记录，可重复读隔离级别会保证：事务A后续再次查询相同条件，结果应与此次一致（理论上）。

步骤3：开启事务B，插入一条工资=5000的新员工，提交事务B

-- 开启事务B（新的会话窗口）
START TRANSACTION;

-- 插入一条新员工，工资=5000（符合事务A的查询条件）
INSERT INTO emp (name, salary) VALUES ('赵六', 5000);

-- 提交事务B（关键：事务B提交后，数据永久写入数据库）
COMMIT;

此时，数据库中实际已有4条工资=5000的员工记录（张三、李四、王五、赵六）。

步骤4：事务A执行第二次范围查询（相同条件），出现幻读

-- 事务A中，再次执行相同的范围查询
SELECT * FROM emp WHERE salary = 5000;

-- 执行结果（依然是3条记录，与第一次一致）：
-- id | name | salary
-- 1  | 张三 | 5000
-- 2  | 李四 | 5000
-- 3  | 王五 | 5000

这里看似没有幻读？别急，这是可重复读的「快照读」机制在起作用——事务A读取的是自己启动时的“数据快照”，不会读取事务B新增的记录，以此保证“可重复读”。但幻读的核心的是「数据修改/新增后，事务A的后续操作会感知到新数据」，继续看步骤5。

步骤5：事务A尝试修改“所有工资=5000的员工”，触发幻读

-- 事务A中，修改所有工资=5000的员工，工资涨1000
UPDATE emp SET salary = 6000 WHERE salary = 5000;

-- 执行结果提示：影响行数 4 行（重点！）
-- Query OK, 4 rows affected (0.01 sec)
-- Rows matched: 4  Changed: 4  Warnings: 0

这里出现了幻读的关键现象：

1. 事务A两次查询（步骤2和步骤4），都只看到3条工资=5000的记录，符合“可重复读”；

2. 但执行UPDATE操作时，却修改了4条记录——这说明事务A“感知到了”事务B新增的那条记录（赵六），这种“查询不到、但能修改到”的情况，就是幻读的典型表现。

步骤6：事务A第三次查询，确认幻读结果

-- 事务A中，第三次查询工资=6000的员工（刚修改后的结果）
SELECT * FROM emp WHERE salary = 6000;

-- 执行结果（4条记录，包含事务B新增的赵六）：
-- id | name | salary
-- 1  | 张三 | 6000
-- 2  | 李四 | 6000
-- 3  | 王五 | 6000
-- 4  | 赵六 | 6000

此时，事务A的查询结果中，凭空出现了“赵六”这条记录——这就是完整的幻读：同一事务内，范围查询的结果行数发生了变化，且是“查询不到但能操作到”的隐性幻读（最常见的幻读类型）。

三、幻读无法避免的核心原因

可重复读隔离级别，通过「MVCC（多版本并发控制）」实现“快照读”：事务启动时，会生成一个全局快照（read view），后续的普通查询（如SELECT）都会读取这个快照的数据，因此看不到其他事务新增/删除的数据，以此保证可重复读。

但「写操作（UPDATE/DELETE/INSERT）」不会使用快照读，而是会直接读取当前数据库的最新数据（当前读）：

1. 事务B提交后，新数据已写入数据库，成为最新数据；

2. 事务A执行UPDATE时，会触发“当前读”，读取最新的所有符合条件的数据（包括事务B新增的），因此修改了4条记录；

3. 修改后，事务A的快照会被刷新，第三次查询时会读取到最新数据，从而看到新增的记录，形成幻读。

四、补充：删除导致的幻读（另一种场景）

除了“新增数据”，“删除数据”也会导致幻读，步骤类似：

1. 事务A第一次查询：工资=5000的员工有3条；

2. 事务B删除一条工资=5000的员工（如王五），提交事务；

3. 事务A第二次查询：依然看到3条（快照读）；

4. 事务A执行DELETE FROM emp WHERE salary = 5000，提示影响2行（实际只剩2条）；

5. 事务A第三次查询：只剩2条记录，出现“凭空消失”的幻读。

五、总结

可重复读的核心是「保证同一事务内，普通查询（快照读）的结果可重复」，但无法阻止「写操作（当前读）感知到其他事务新增/删除的记录」，进而导致后续查询出现“幻读”。

若要彻底避免幻读，需将隔离级别提升到「串行化（Serializable）」，但会牺牲并发性能；实际开发中，可通过“加行锁/表锁”“乐观锁”等方式，在可重复读级别下规避幻读的影响。

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花