Mysql幻读与事务隔离级别

---

MySQL幻读的原因与事务隔离级别的关系

一、幻读（Phantom Read）的定义

幻读指在同一事务中，多次执行相同的范围查询时，由于其他事务的 插入（INSERT）或删除（DELETE） 操作，导致返回的行数不一致。
示例：

-- 事务A
BEGIN;
SELECT * FROM users WHERE age > 20; -- 返回2条记录（id=1, id=2）

-- 事务B
INSERT INTO users (id, age) VALUES (3, 25); -- 提交

-- 事务A再次查询
SELECT * FROM users WHERE age > 20; -- 返回3条记录（id=1, 2, 3） → 出现幻读

---

二、幻读的根本原因

1. 范围查询的漏洞

• • 普通的行锁只能锁定已存在的记录，无法阻止其他事务在范围内插入新数据或删除已有数据。

2. 快照读与当前读的差异

• • 快照读（普通SELECT）：基于MVCC读取历史版本数据，不锁定记录。
  • 当前读（SELECT ... FOR UPDATE、UPDATE、DELETE）：读取最新数据并加锁，可能感知其他事务的插入/删除。

---

三、事务隔离级别与幻读的关系

隔离级别	是否允许幻读	实现机制
READ UNCOMMITTED	✔️	无锁机制，直接读取最新数据（包括未提交的修改）。
READ COMMITTED	✔️	MVCC快照读，每次查询生成新快照，可能看到其他事务已提交的插入/删除。
REPEATABLE READ	✖️（InnoDB）	通过**临键锁（Next-Key Lock）**锁定范围间隙，阻止其他事务插入新数据。
SERIALIZABLE	✖️	所有读操作加共享锁，完全串行化，彻底禁止并发修改。

---

四、InnoDB如何在REPEATABLE READ下避免幻读

1. 临键锁（Next-Key Lock）

• 作用范围：锁定索引记录的行本身 + 前一个间隙（Gap）。
• 示例：

-- 假设索引为age，现有记录age=20和age=30
SELECT * FROM users WHERE age > 20 FOR UPDATE;

• • 锁范围：(20, 30]（锁定age=30的行及间隙） + (30, +∞)（锁定正无穷间隙）。
  • 效果：阻止其他事务插入age > 20的新数据（如age=25）。

2. 间隙锁（Gap Lock）

• 作用范围：仅锁定索引记录之间的间隙（不锁定行本身）。
• 示例：

-- 假设索引为age，现有记录age=20和age=30
SELECT * FROM users WHERE age = 25 FOR UPDATE; -- 记录不存在

• • 锁范围：(20, 30)（锁定间隙）。
  • 效果：阻止插入age=25的数据。

3. MVCC与快照读

• 普通SELECT：

• • 基于事务开始的ReadView读取快照数据，不会看到其他事务的插入/删除。
  • 示例：

-- 事务A
BEGIN;
SELECT * FROM users WHERE age > 20; -- 快照读，返回id=1,2

-- 事务B插入id=3并提交

-- 事务A再次快照读
SELECT * FROM users WHERE age > 20; -- 仍返回id=1,2（无幻读）

---

五、幻读的残留场景

在REPEATABLE READ下，以下场景仍可能感知到幻读：

1. 当前读操作

-- 事务A
BEGIN;
SELECT * FROM users WHERE age > 20; -- 快照读，返回id=1,2

-- 事务B插入id=3（age=25）并提交

-- 事务A执行当前读
SELECT * FROM users WHERE age > 20 FOR UPDATE; -- 返回id=1,2,3（感知幻读）

• 原因：FOR UPDATE触发当前读，强制读取最新数据并加锁，此时会看到事务B的插入。

2. 混合操作（先快照读，后当前读）

-- 事务A
BEGIN;
SELECT * FROM users WHERE age > 20; -- 快照读，返回id=1,2

-- 事务B插入id=3（age=25）并提交

-- 事务A执行UPDATE（触发当前读）
UPDATE users SET name = 'test' WHERE age > 20; -- 修改id=1,2,3

• 结果：事务A的UPDATE操作会修改新插入的id=3，导致逻辑上的幻读。

---

六、如何彻底避免幻读？

1. 升级隔离级别到SERIALIZABLE：

• • 所有读操作加共享锁，完全禁止并发写入，但性能极低，仅适合特殊场景。

2. 显式加锁（FOR UPDATE） ：

BEGIN;
SELECT * FROM users WHERE age > 20 FOR UPDATE; -- 加临键锁
-- 其他事务插入age>20的数据会被阻塞
COMMIT;

• • 在事务开始时直接锁定范围，阻止其他事务插入。

---

七、总结

关键点	说明
幻读的本质	范围查询因其他事务插入/删除导致结果集变化。
InnoDB的解决方案	通过临键锁（Next-Key Lock）在REPEATABLE READ下避免幻读，但需注意当前读的残留问题。
隔离级别的选择	默认REPEATABLE READ适合大多数场景，SERIALIZABLE仅用于强一致性需求。
实际开发建议	对关键范围查询使用SELECT ... FOR UPDATE显式加锁，确保操作的原子性和一致性。

通过理解InnoDB的锁机制与隔离级别的关系，可以更好地设计事务逻辑，避免幻读对业务逻辑的影响。