mysql-分析并解决可重复读隔离级别发生的删除幻读问题

在 MySQL 的 InnoDB 存储引擎中，快照读和当前读的行为会影响事务的一致性。让我们详细分析一下隔离级别味可重复读的情况下如何解决删除带来的幻读。
场景描述
假设有一个表 orders，其中包含以下数据：

事务 A 执行快照读

START TRANSACTION;
SELECT * FROM orders WHERE customer_id = 200; -- 快照读

事务 B 执行当前读并删除记录

START TRANSACTION;
DELETE FROM orders WHERE id = 10; -- 当前读，尝试删除
COMMIT;

分析

• 快照读（事务 A）
• 快照读：事务 A 执行的是快照读，它基于事务开始时的数据快照，不加锁。
• 数据快照：事务 A 会看到事务开始时的数据快照，即 customer_id = 200 的记录为 (3, 200, 150) 和 (10,200, 250)。
• 当前读（事务 B）
• 当前读：事务 B 执行的是当前读操作，它会尝试获取 id = 10 的记录的行锁。
• 删除操作：事务 B 成功删除 id = 10 的记录，并提交事务。

影响分析
事务 A 第一次查询：
事务 A 查询 customer_id = 200 的记录，结果为 (3, 200, 150) 和 (10, 200, 250)。
事务 B 删除记录：
事务 B 删除 id = 10 的记录，并提交事务。
事务 A 第二次查询：
事务 A 再次查询 customer_id = 200 的记录，结果为 (3, 200, 150)，缺少了 (10, 200, 250)。

结论
幻读：这是因为事务 B 在事务 A 之间删除了一条记录。

解决方案
使用可重复读（Repeatable Read）隔离级别 + 间隙锁

1.设置隔离级别：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

2.使用当前读操作：

• 在事务 A 中，使用 SELECT … FOR UPDATE 或 SELECT … FOR SHARE 来获取行锁和间隙锁。
• 这样可以确保在事务 A 的整个生命周期内，查询结果保持一致。

示例
事务 A 执行以下操作：

START TRANSACTION;
SELECT * FROM orders WHERE customer_id = 200 FOR UPDATE; -- 当前读，加锁并获取间隙锁
-- 进行业务逻辑处理
SELECT * FROM orders WHERE customer_id = 200; -- 再次查询
COMMIT;

事务 B 执行以下操作：

START TRANSACTION;
DELETE FROM orders WHERE id = 10; -- 当前读，尝试删除
COMMIT;

由于事务 A 持有 customer_id = 200 范围内的所有记录的行锁和间隙锁，事务 B 的删除操作将被阻塞，直到事务 A 提交或回滚。这样可以确保事务 A 的查询结果在整个事务期间保持一致。

总结
当前读：事务 A 第一次执行的时候使用的是当前读因此会对该行进行加锁，所以其他事务无法对该行进行删除或者更新操作。
快照读：事务 A 第二次执行的时候此时事务并未提交因此使用快照读仍然能读取到该行。
当前读：事务 B 执行当前读并删除记录，由于事务 A 持有锁，导致事务B处于阻塞状态直到事物A释放 。