Mysql 幻读和不可重复读

MySQL 幻读（Phantom Read）详解

幻读是 MySQL 事务隔离级别中的一个重要概念，指在同一个事务内，连续执行两次相同的查询，第二次查询看到了第一次查询未出现的"幻影行"（新插入的行）。

幻读的定义

幻读发生在：

• 一个事务内多次执行相同的 SELECT 查询
• 两次查询之间，另一个事务插入（或删除）了符合查询条件的新行
• 第二次查询返回了第一次查询未出现的行（或第一次查询返回的行在第二次查询中消失了）

幻读示例

-- 事务1
START TRANSACTION;
SELECT * FROM users WHERE age > 30; -- 第一次查询，返回2条记录

-- 此时事务2插入了一条新记录
-- INSERT INTO users VALUES (4, 'John', 35);
-- COMMIT;

SELECT * FROM users WHERE age > 30; -- 第二次查询，返回3条记录（出现了幻影行）
COMMIT;

幻读与不可重复读的区别

特性	不可重复读 (Non-repeatable Read)	幻读 (Phantom Read)
关注点	更新或删除已存在的行	插入或删除新行
现象	同一事务内两次读取同一行数据不同	同一事务内两次查询返回不同行数
避免隔离级别	READ COMMITTED 及以上	SERIALIZABLE

MySQL 隔离级别与幻读

MySQL 的四种隔离级别对幻读的处理：

1. READ UNCOMMITTED：

   • 允许幻读
   • 性能最好，一致性最差

2. READ COMMITTED：

   • 允许幻读
   • 解决了脏读问题

3. REPEATABLE READ（InnoDB 默认）：

   • 理论上允许幻读，但 InnoDB 通过多版本并发控制（MVCC）和间隙锁（Gap Lock）实际避免了大多数幻读情况
   • 对于当前读（SELECT ... FOR UPDATE/LOCK IN SHARE MODE）会加间隙锁防止幻读

4. SERIALIZABLE：

   • 完全避免幻读
   • 通过强制事务串行执行实现
   • 性能最差，一致性最强

InnoDB 如何避免幻读

InnoDB 在 REPEATABLE READ 隔离级别下通过以下机制减少幻读：

1. 多版本并发控制 (MVCC)：

   • 为事务提供数据快照
   • 保证事务内看到一致的数据视图

2. 间隙锁 (Gap Lock)：

   • 锁定索引记录之间的间隙
   • 防止其他事务在间隙中插入新记录
   • 例如：SELECT * FROM users WHERE age > 30 FOR UPDATE 会锁定所有 age > 30 的记录及其间隙

3. 临键锁 (Next-Key Lock)：

   • 记录锁 + 间隙锁的组合
   • 锁定记录及其前面的间隙

验证幻读的实验

-- 实验准备
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), age INT);
INSERT INTO users VALUES (1, 'Alice', 25), (2, 'Bob', 35), (3, 'Charlie', 45);

-- 窗口1（事务1）
START TRANSACTION;
SELECT * FROM users WHERE age > 30; -- 返回Bob(35), Charlie(45)

-- 窗口2（事务2）
START TRANSACTION;
INSERT INTO users VALUES (4, 'David', 38); -- 尝试插入符合条件的记录
COMMIT; -- 如果事务1使用SELECT ... FOR UPDATE，这里会阻塞

-- 窗口1（事务1继续）
SELECT * FROM users WHERE age > 30; -- 在REPEATABLE READ下，不加锁会看到新记录（幻读）
-- 如果使用 SELECT * FROM users WHERE age > 30 FOR UPDATE，则不会看到新记录
COMMIT;

实际应用建议

1. 如果业务需要完全避免幻读，使用 SERIALIZABLE 隔离级别
2. 在大多数情况下，InnoDB 的 REPEATABLE READ 配合适当的锁机制已足够
3. 对于需要精确控制幻读的场景，显式使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE
4. 合理设计索引，使间隙锁能更有效地工作

幻读是数据库并发控制中的重要概念，理解它有助于设计更健壮的事务处理逻辑。