关于在 MySQL 的可重复读隔离级别下出现幻读详解

在 MySQL 的可重复读隔离级别下，幻读通常发生在当前读（如 INSERT、UPDATE、DELETE 或带锁的 SELECT）场景中，而普通 SELECT 查询由于 MVCC 的保护不会出现幻读。以下是详细解释和解决方案：

1. 幻读发生的具体场景

场景一：当前读导致的幻读

• 步骤：

  1. 事务 T1 执行 SELECT ... FOR UPDATE 查询符合条件的记录（如 age > 20）。
  2. 事务 T2 插入一条新记录（如 age=25）并提交。
  3. 事务 T1 再次执行相同的 SELECT ... FOR UPDATE，此时会看到 T2 插入的新记录，产生幻读。

• 关键点：
  SELECT ... FOR UPDATE 属于当前读，读取的是最新数据而非快照。若其他事务在两次当前读之间插入了新记录，第二次读取时会看到这些“幻影”记录。

场景二：插入意向锁（Insert Intention Lock）导致的幻读

• 步骤：

  1. 事务 T1 删除部分记录（如 DELETE FROM users WHERE age > 20），但未提交。
  2. 事务 T2 插入一条新记录（如 age=25），获取插入意向锁（间隙锁的一种）。
  3. 事务 T1 再次执行 DELETE FROM users WHERE age > 20，发现删除行数比第一次多，产生幻读。

• 关键点：
  插入意向锁允许并发插入不同间隙，但当前读（如 DELETE）会重新评估条件，导致前后操作的记录数不一致。

2. 解决方案

方案一：使用 Next-Key Lock 锁定间隙

MySQL 的可重复读隔离级别默认使用 Next-Key Lock（行锁 + 间隙锁），阻止其他事务在锁定范围内插入新记录。
示例：

BEGIN;
-- 使用 FOR UPDATE 锁定记录及间隙
SELECT * FROM users WHERE age > 20 FOR UPDATE;  -- 锁定 (20, +∞) 的间隙
-- 此时其他事务无法插入 age > 20 的记录
COMMIT;

方案二：索引优化

• 原理：
  若查询条件有索引，Next-Key Lock 会精确锁定索引范围；若无索引，会锁定全表。因此，确保查询条件字段有索引可减小锁粒度。
  示例：

  -- 为 age 字段添加索引
  CREATE INDEX idx_age ON users (age);
  

方案三：升级隔离级别到串行化

将隔离级别设置为 SERIALIZABLE，所有 SELECT 语句会隐式添加 LOCK IN SHARE MODE，强制事务串行执行。
示例：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM users WHERE age > 20;  -- 隐式添加 LOCK IN SHARE MODE
COMMIT;

3. 总结与最佳实践

推荐做法：
优先使用 索引 + Next-Key Lock（如 SELECT ... FOR UPDATE），并在业务层优化查询逻辑，减少长事务持有锁的时间。仅在必要时（如金融交易）才使用 SERIALIZABLE 隔离级别。