如何定位和解决MySQL中的幻读问题？-mysql教程-PHP中文网

答案：MySQL中幻读指事务内多次查询因其他事务插入而看到新行，REPEATABLE READ下可通过Next-Key Locks（如SELECT ... FOR UPDATE）避免，或升级至SERIALIZABLE级别。

如何定位和解决mysql中的幻读问题？

MySQL中的幻读问题，通常发生在事务在同一范围内多次读取数据时，由于其他事务的插入操作，导致后续读取看到了之前未出现的新行。在MySQL的InnoDB存储引擎中，尤其是在

REPEATABLE READ

登录后复制

隔离级别下，幻读的出现往往与对锁定读的理解和使用不当有关，或者是在特定复杂场景下MVCC未能完全覆盖的情况。解决它，核心在于理解并恰当利用InnoDB的行锁、间隙锁（Next-Key Locks）机制，或者在极端一致性要求下，考虑提升事务隔离级别。

解决方案

要解决MySQL中的幻读问题，尤其是在

REPEATABLE READ

登录后复制

隔离级别下，最直接且推荐的方法是利用InnoDB的Next-Key Locks。这通过在涉及范围查询的

SELECT

登录后复制

语句后加上

FOR UPDATE

登录后复制

或

LOCK IN SHARE MODE

登录后复制

来实现。

当一个事务执行

SELECT ... WHERE range_condition FOR UPDATE

登录后复制

时，InnoDB不仅会锁定满足

range_condition

登录后复制

的现有行（记录锁），还会锁定这些行之间的“间隙”（间隙锁），以及范围的开始和结束点（如果适用），从而形成Next-Key Locks。这意味着在当前事务提交之前，其他事务无法在这些被锁定的间隙中插入新的行，也无法修改或删除被锁定的现有行。这样，无论当前事务在同一范围内执行多少次

SELECT

登录后复制

查询，它都将看到相同的结果集，从而有效避免了幻读。

对于那些对一致性要求极高，且对并发性能有一定容忍度的场景，将事务隔离级别提升到

SERIALIZABLE

登录后复制

也是一个彻底的解决方案。在该级别下，所有普通的

SELECT

登录后复制

语句都会被隐式转换为

SELECT ... LOCK IN SHARE MODE

登录后复制

，所有DML操作都会使用

FOR UPDATE

登录后复制

类似的锁，确保了最高的隔离性，完全杜绝了幻读，但代价是显著降低了并发性能。

如何确认我的MySQL数据库正在经历幻读？

在我看来，很多时候我们提及的“幻读”问题，其实可能只是对事务隔离级别或MVCC机制的误解。真正的幻读，是指在一个事务内部，当你基于某个条件多次查询数据时，第二次或之后的查询看到了第一次查询时尚不存在的新行。这通常发生在范围查询中。

要确认是否正在经历幻读，你需要模拟一个特定的并发场景：

准备数据： 创建一个简单的表，并插入几行数据。
启动事务A： 设置隔离级别为
```
REPEATABLE READ
```
登录后复制
（MySQL默认就是），然后执行一个范围查询，例如
```
SELECT COUNT(*) FROM your_table WHERE id > 10 AND id < 20;
```
登录后复制
记录下结果。
启动事务B： 在事务A尚未提交的情况下，插入一行满足事务A查询条件的新数据，例如
```
INSERT INTO your_table (id, name) VALUES (15, 'new_record');
```
登录后复制
并提交事务B。
回到事务A： 再次执行与步骤2完全相同的查询。
观察结果： 如果事务A第二次查询的结果（例如
```
COUNT(*)
```
登录后复制
）比第一次查询增加了，那么恭喜你，你可能就遇到了幻读。

诊断关键点：

隔离级别： 务必确认你的会话隔离级别是
```
REPEATABLE READ
```
登录后复制
。你可以通过
```
SELECT @@transaction_isolation;
```
登录后复制
来查看。
查询类型： 幻读最常出现在范围查询中。
并发操作： 必须有另一个事务在第一个事务的两次查询之间插入了符合条件的新数据。
应用逻辑： 检查你的应用代码，看是否有基于第一次查询结果进行后续操作，而这些操作又会被新插入的行所影响的逻辑。例如，你查询了一个范围内的可用库存，然后根据这个结果去扣减库存，但在此期间有新的商品被添加到这个范围内。

需要强调的是，对于普通的

SELECT

登录后复制

语句，InnoDB在

REPEATABLE READ

登录后复制

级别下通过MVCC（多版本并发控制）机制，通常能有效避免幻读。事务A在开始时会获得一个快照，后续的普通

SELECT

登录后复制

会一直读取这个快照的数据，即使有新行插入，也不会被看到。所以，如果你在普通

SELECT

登录后复制

中看到了幻读，那可能需要深入检查是否是快照版本管理出了问题，或者你的操作并非简单的

SELECT

登录后复制

，而是

SELECT ... FOR UPDATE

登录后复制

等锁定读，而你又没有正确理解其锁定范围。真正的幻读问题，往往出现在你需要锁定一个范围以防止新数据插入，从而保证后续操作的正确性的场景。

在

REPEATABLE READ

登录后复制

隔离级别下，如何有效避免幻读？

在MySQL的

REPEATABLE READ

登录后复制

隔离级别下，避免幻读的核心策略是利用InnoDB的Next-Key Locks。这是一种组合锁，由记录锁（Record Lock）和间隙锁（Gap Lock）构成。当你在一个

SELECT

登录后复制

语句后面加上

FOR UPDATE

登录后复制

或

LOCK IN SHARE MODE

登录后复制

时，InnoDB会根据你的

WHERE

登录后复制

子句，不仅锁定符合条件的现有行，还会锁定这些行之间的“间隙”，以及可能存在的边界间隙，从而阻止其他事务在这些被锁定的间隙中插入新行。

具体实践：

PHP与MySQL程序设计3

本书是全面讲述PHP与MySQL的经典之作，书中不但全面介绍了两种技术的核心特性，还讲解了如何高效地结合这两种技术构建健壮的数据驱动的应用程序。本书涵盖了两种技术新版本中出现的最新特性，书中大量实际的示例和深入的分析均来自于作者在这方面多年的专业经验，可用于解决开发者在实际中所面临的各种挑战。本书内容全面深入，适合各层次PHP和MySQL开发人员阅读，既是优秀的学习教程，也可用作参考手册。

253

查看详情

使用
```
SELECT ... FOR UPDATE
```
登录后复制
：这是最常用的方法，它会在查询到的所有记录上添加排他锁（X锁），同时也会在这些记录所在的索引范围内的间隙上添加间隙锁。这意味着其他事务不能修改或删除这些记录，也不能在这些间隙中插入新的记录。
```
START TRANSACTION;
-- 锁定ID在10到20之间的现有记录，并锁定这个ID范围内的所有间隙。
-- 这样，其他事务就不能在这个范围内插入新的ID。
SELECT * FROM products WHERE id > 10 AND id < 20 FOR UPDATE;

-- 应用程序逻辑处理...

-- 再次查询，确保结果一致
SELECT * FROM products WHERE id > 10 AND id < 20 FOR UPDATE;

COMMIT;
```
登录后复制
关键点：
```
FOR UPDATE
```
登录后复制
的锁是排他性的，会阻塞其他事务对锁定范围内的读写操作。适用于你需要读取数据并基于此数据进行修改或删除，且不希望有新数据干扰的场景。
使用
```
SELECT ... LOCK IN SHARE MODE
```
登录后复制
：这个语句会在查询到的所有记录上添加共享锁（S锁），同样也会在间隙上添加间隙锁。共享锁允许其他事务读取这些记录，但阻止它们修改或删除，也阻止其他事务在间隙中插入新行。
```
START TRANSACTION;
-- 锁定ID在10到20之间的现有记录（共享锁），并锁定这个ID范围内的所有间隙。
-- 其他事务可以读取这些记录，但不能修改或插入新行。
SELECT * FROM orders WHERE amount > 100 AND status = 'pending' LOCK IN SHARE MODE;

-- 应用程序逻辑处理...

COMMIT;
```
登录后复制
关键点：
```
LOCK IN SHARE MODE
```
登录后复制
的锁是共享的，允许多个事务同时持有共享锁。适用于你需要读取数据并确保其在事务期间不被修改或有新数据插入，但又希望其他事务也能读取这些数据的场景。

注意事项：

索引至关重要： Next-Key Locks是基于索引工作的。如果你的
```
WHERE
```
登录后复制
子句没有用到索引，或者索引不完整，InnoDB可能会退化为表锁，这将严重影响并发性能。确保你的查询条件能够有效利用索引。
事务的粒度： 尽量保持事务的短小精悍。长时间运行的事务持有锁的时间越长，对并发的影响就越大，死锁的风险也越高。
死锁风险： 引入锁定读会增加死锁的可能性。你需要设计好事务的执行顺序，并准备好处理死锁的机制（例如，重试事务）。
非锁定读的幻读： 再次强调，对于简单的
```
SELECT
```
登录后复制
（非锁定读），
```
REPEATABLE READ
```
登录后复制
级别下通过MVCC通常能避免幻读。如果你在使用普通
```
SELECT
```
登录后复制
时遇到了幻读，那很可能是对事务边界、会话隔离级别或MySQL版本特性有误解。幻读问题在
```
REPEATABLE READ
```
登录后复制
中，主要针对的是锁定读场景，即你需要确保一个范围在你的事务中是“固定”的，不被其他事务插入新数据。

SERIALIZABLE

登录后复制

隔离级别是解决幻读的终极方案吗？它有哪些利弊？

从理论上讲，

SERIALIZABLE

登录后复制

（串行化）隔离级别确实是解决包括幻读在内所有并发问题的“终极方案”。它通过强制事务串行执行，确保了最高的隔离性，使得并发事务的执行结果与它们按某种顺序串行执行的结果完全一致。这意味着在

SERIALIZABLE

登录后复制

级别下，你根本不需要担心幻读、不可重复读或脏读等问题。数据库系统会自动处理所有的并发控制，确保数据的一致性。

优点：

绝对的数据一致性： 这是它最显著的优点。在
```
SERIALIZABLE
```
登录后复制
级别下，你不需要担心任何并发导致的数据不一致问题。数据库会像单线程一样处理事务，保证了数据的最高完整性。
简化应用开发： 由于数据库层面提供了最强的隔离保证，应用开发者在处理并发时可以少考虑很多复杂性，减少了编写复杂的锁定逻辑或重试机制的需要。
彻底消除幻读： 所有的
```
SELECT
```
登录后复制
语句都会被隐式转换为
```
SELECT ... LOCK IN SHARE MODE
```
登录后复制
（共享锁），所有的DML操作都会使用排他锁，从而彻底锁定了所有相关的记录和间隙，完全杜绝了幻读的发生。

缺点：

极低的并发性能： 这是
```
SERIALIZABLE
```
登录后复制
级别最大的痛点。为了实现串行化，数据库会进行大量的锁定操作，导致事务之间的等待时间大大增加，从而显著降低了系统的并发处理能力和吞吐量。在高并发的OLTP（在线事务处理）系统中，这几乎是不可接受的。
高死锁风险： 由于锁的粒度大且持有时间长，死锁发生的概率会大大增加。应用程序需要准备更健壮的死锁处理机制，例如事务重试。
不适合读多写少的场景： 即使是简单的
```
SELECT
```
登录后复制
查询，也会获取共享锁，这会阻塞其他写入操作。在读操作远多于写操作的系统中，这种锁机制会成为性能瓶颈。

何时考虑使用

SERIALIZABLE

登录后复制

：

尽管有上述缺点，

SERIALIZABLE

登录后复制

级别并非一无是处。它在某些特定场景下仍然有其价值：

对数据一致性有极端要求的批处理任务： 例如，月末结算、财务报表生成、数据审计等，这些任务通常在系统负载较低时运行，且对数据一致性要求极高，可以牺牲部分性能来换取绝对的准确性。
调试复杂的并发问题： 当你在
```
REPEATABLE READ
```
登录后复制
或其他较低隔离级别下遇到难以诊断的并发问题时，暂时切换到
```
SERIALIZABLE
```
登录后复制
可以帮助你确认问题是否与隔离级别有关，或者是否是应用逻辑本身的错误。
低并发环境： 对于并发度非常低的系统，
```
SERIALIZABLE
```
登录后复制
的性能影响可能不那么明显。