MySQL可重复读级别解决幻读问题

在 MySQL 中，可重复读（REPEATABLE READ）级别的幻读（Phantom Read）问题可以通过以下方法解决：

什么是幻读？

幻读是指 同一事务中执行相同的 SELECT 语句，但因其他事务的 INSERT 或 DELETE 使得数据发生变化，导致查询结果前后不一致。

场景

• 事务A 开始，并执行 SELECT 语句查询满足条件的记录。
• 事务B 在事务A未提交之前，插入或删除满足条件的新数据。
• 事务A 再次执行相同的查询，但查询结果发生变化，这就是 幻读。

示例

-- 事务 A 开始
START TRANSACTION;
SELECT * FROM emp WHERE deptno = 10; -- 查询出 3 条数据

-- 事务 B 插入新数据
INSERT INTO emp (empno, ename, deptno) VALUES (999, 'NewEmp', 10);
COMMIT;  -- 事务 B 提交

-- 事务 A 再次查询
SELECT * FROM emp WHERE deptno = 10; -- 现在查询出 4 条数据（幻读）

在 REPEATABLE READ 级别下，普通 SELECT 不能防止 幻读，但可以使用 行级锁或表级锁 解决。

解决方案

方式一：使用 SERIALIZABLE 事务隔离级别

• SERIALIZABLE 是 MySQL 最高级别的事务隔离，可以防止 幻读，但会导致并发性能降低。
• 事务中的 SELECT 语句会使用表级锁（锁住整个表），避免其他事务插入或删除数据。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM emp WHERE deptno = 10;

缺点

• 由于 所有读取都变成串行化执行，会大幅降低并发性能。
• 适用于强一致性要求的系统，如银行交易系统。

方式二：使用 SELECT ... LOCK IN SHARE MODE**

• LOCK IN SHARE MODE 使得查询的行被共享锁锁定，防止其他事务修改或删除这些行，但 不能防止 INSERT 造成的幻读。

START TRANSACTION;
SELECT * FROM emp WHERE deptno = 10 LOCK IN SHARE MODE;

适用场景

• 适用于 只希望防止数据修改（UPDATE/DELETE），但允许插入新数据的情况。

不能防止 INSERT

• 其他事务仍然可以 INSERT 新数据，导致 幻读问题仍然存在。

方式三：使用 SELECT ... FOR UPDATE**

• FOR UPDATE 让 查询的行加上排他锁（X 锁），防止其他事务 INSERT、UPDATE、DELETE，从而解决幻读。

START TRANSACTION;
SELECT * FROM emp WHERE deptno = 10 FOR UPDATE;

适用于

• 需要防止幻读，同时允许高并发的场景。
• 适用于 并发事务修改同一数据集 的情况，如 订单处理 或 库存管理。

限制

• 只锁定 已存在的记录，无法阻止 INSERT 新记录 造成的幻读。
• 可能会出现间隙锁（Gap Lock），如果 innodb_locks_unsafe_for_binlog 启用，可能导致锁失效。

方式四：使用 InnoDB 的间隙锁（Gap Lock）

• MySQL InnoDB 默认在 REPEATABLE READ 级别使用间隙锁（Gap Lock），它不仅锁定现有记录，还会锁住索引范围内的空隙，防止新数据插入，解决幻读问题。

START TRANSACTION;
SELECT * FROM emp WHERE deptno = 10 FOR UPDATE;

• MySQL 默认 REPEATABLE READ 级别 + FOR UPDATE 会自动启用间隙锁（Gap Lock），防止 INSERT。

• 但如果 innodb_locks_unsafe_for_binlog = ON，MySQL 可能不会加 Gap Lock，导致幻读问题依然存在！

适用于

• 事务需要保证数据查询前后一致性的业务场景，如 库存管理、银行账户交易。

解决方案对比

结论

• 如果允许并发，但想防止幻读 ➝ SELECT ... FOR UPDATE（推荐）
• 如果完全禁止 INSERT 导致的幻读 ➝ InnoDB 默认 REPEATABLE READ + Gap Lock 即可防止。
• 如果你需要最高的事务隔离 ➝ 使用 SERIALIZABLE，但会影响并发性能。

在 MySQL 默认的 REPEATABLE READ 级别 下，使用 SELECT ... FOR UPDATE 即可防止幻读，而 不必强制升级到 SERIALIZABLE。

最佳实践

START TRANSACTION;
SELECT * FROM emp WHERE deptno = 10 FOR UPDATE;
COMMIT;