解决MySQL幻读？可重复读隔离级别背后的工作原理

时间：2024-04-08 16:00:36

什么是当前读和快照读

  1. 当前读：又称为 "锁定读"，它会读取记录的最新版本（也就是最新的提交结果），并对读取到的数据加锁，其它事务不能修改这些数据，直到当前事务提交或回滚。"select … for update" 和 "update"、"delete"、"insert" 这类修改数据的语句都属于当前读。

  2. 快照读：又称为 "一致性读"，它会读取记录在事务开始时的版本，也就是它会读取一个快照。"select" 语句一般就是快照读。

MySQL 可重复读隔离级别(RR)

在MySQL可重复读事务隔离级别下可以解决大部分幻读问题，而不是全部幻读问题。如果一个事务在执行过程中既有快照读又有当前读就会发生幻读问题。

在 MySQL 中，默认的事务隔离级别是 “可重复读（RR）”。
如何查询当前事务隔离级别？

SELECT @@transaction_isolation;

COPY

file

在可重复读（RR）隔离级别下，同一个事务中的快照读取同样的记录时，总是能够得到同样的结果，但是无法防止其他事务在此期间插入新的行（新增数据），这种现象被称为幻读。MySQL RR隔离级别下会使用MVVC和Next-Key Lock来防止幻读。

下面的示例展示了幻读现象：
整体逻辑为：

事务A 事务B
开启事务，查询数量大于10的订单，此时只能查到2条记录，因为此时另一个事务还没有进行插入，sql语句：SELECT * FROM trade_order WHERE quantity > 10;（快照读） !

file
事务B启动并插入一条数量为30的订单并提交事务。SQL语句：INSERT INTO trade_order (item, quantity) VALUES ('pear', 30); 

file
事务A再次执行查询数量大于10的订单，依然只有2条，因为是快照读。sql语句：SELECT * FROM trade_order WHERE quantity > 10;

file
事务A执行快照读,这时候查询到了3条发生幻读，SQL语句：SELECT * FROM trade_order WHERE quantity > 10 for update;（*当前读） 

file

假设我们有一个包含以下数据的 "trade_order" 表：


CREATE TABLE trade_order (
    id INT PRIMARY KEY AUTO_INCREMENT,
    item VARCHAR(100),
    quantity INT
);
-- 插入3条数据,两条数量大于10，一条小于等于10
INSERT INTO trade_order (item, quantity) VALUES
('apple', 10),
('banana', 20),
('cherry', 15);

## 事务SQL执行步骤
注意： SQL在两个命令行窗口下执行，目前Windows Powershell可以很容易安装MySQL客户端，所以可以新建两个PowerShell窗口来执行。
```sql
-- Transaction A
START TRANSACTION;
-- 查询 quantity > 10 的记录。因为是快照读，它会读取该事务开始时的记录快照
SELECT * FROM trade_order WHERE quantity > 10;
-- +----+--------+----------+
-- | id | item   | quantity |
-- +----+--------+----------+
-- | 21 | banana |       20 |
-- | 22 | cherry |       15 |
-- +----+--------+----------+
-- 2 rows in set (0.00 sec)

-- Transaction B
START TRANSACTION;
-- 插入一条新的记录，quantity > 10
INSERT INTO trade_order (item, quantity) VALUES ('pear', 30);
COMMIT;

-- Transaction A
-- 再次查询 quantity > 10 的记录。因为是快照读，它会读取该事务开始时的记录快照
SELECT * FROM trade_order WHERE quantity > 10;
-- +----+--------+----------+
-- | id | item   | quantity |
-- +----+--------+----------+
-- | 21 | banana |       20 |
-- | 22 | cherry |       15 |
-- +----+--------+----------+
-- 2 rows in set (0.00 sec)

-- 再次执行当前读取操作（记录最新的数据，并对这些记录加锁）
SELECT * FROM trade_order WHERE quantity > 10 FOR UPDATE;

-- +----+--------+----------+
-- | id | item   | quantity |
-- +----+--------+----------+
-- | 21 | banana |       20 |
-- | 22 | cherry |       15 |
-- | 23 | pear   |       30 |
-- +----+--------+----------+
-- 3 rows in set (3.79 sec)
-- 这就是幻读问题，因为这次查询返回了比之前更多的行。

COMMIT;

COPY

所以说，在可重复读隔离级别下，MySQL 通过使用快照读（MVCC，多版本并发控制）确保了在一个事务里多次读取同样的记录能得到一致的结果，但是对于新增的行，即出现在事务开始后的新行，就无法做到一致性读取，这种现象被称为“幻读”。

结语

在大多数情况下，MySQL的可重复读（RR）事务隔离级别为绝大多数业务场景提供了适当的一致性保证，并有效地通过MVCC和Next-Key Locks机制解决了幻读问题。尽管如此，RR隔离级别可能会由于锁机制而导致某些性能问题，特别是在密集型的写操作或高并发场景时。

如果业务需求对数据的实时一致性要求不是特别严格或者你可以在应用层处理这些问题，并且更加关注于系统性能，可以考虑将事务隔离级别降低到读提交（RC）。读提交隔离级别在某些情况下可以提供更高的并发性，因为它只在必要时锁定数据行，以此减少锁争用。但是在你切换到RC隔离级别时你得评估带来的问题：比如：不可重复读和幻读，别到时候应用各种问题。

参考

1. 快分清MySQL当前读、快照读和幻读关系 – FOF编程网

相关文章