MySQL如何解决幻读？

原创 已于 2025-12-11 16:28:29 修改 · 3.5k 阅读 · 28 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

于 2024-11-15 17:19:23 首次发布

目录

一、什么是幻读？

1.1 幻读的定义

1.2 幻读的示例

1.3 幻读产生的原因？

1.4 读已提交（Read Committed）

1.4.1 确定事务等级

1.4.2 非锁定读取

准备

示例

结论

1.4.3 锁定读取

准备

示例

分析

结论

1.5 可重复读（Repeatable Read）

1.5.1 确定事务等级

1.5.2 非锁定读取

准备

示例

结论

1.5.3 锁定读取

准备

示例

分析

结论

二、怎么解决幻读？

2.1 提高事务隔离等级

2.2 间隙锁（Gap Locking）

三、间隙锁

3.1 定义

3.2 工作原理

无间隙锁

有间隙锁

3.3 间隙锁的类型

范围查询

相等查询

参考资料

一、什么是幻读？

1.1 幻读的定义

幻读（Phantom Read） 是数据库事务中的一种并发问题，指的是在一个事务执行过程中，另一个事务插入、删除或修改了符合查询条件的记录，导致前一个事务读取到不同的数据集，从而产生“幻觉”般的读操作。

1.2 幻读的示例

具体来说，事务A执行过程中，事务B插入或者修改了记录，导致事务A在相同查询条件下读取到的结果发生了改变。

假设有用户表users，包含列username、password、state。

1.事务A，查询所有状态启用的用户

select count(id) from users where state=1;

2.事务B，插入新用户，state=1

insert into users(username, password, state) values('test11', 123456, 1);

3.事务A，再次执行相同的sql

select count(id) from users where state=1;

事务A两次查询，得到了不同的结果。

1.3 幻读产生的原因？

1.对表进行了插入或删除

2.事务隔离等级

幻读（Phantom Read）通常在较低的事务隔离等级中产生（如 读已提交（READ COMMITTED）、可重复读（REPEATABLE READ））。

读已提交（Read Committed）：可以读取到已提交的记录，但无法保证查询过程中数据不被改变，因此会发生幻读；

可重复读（Repeatable Read）：可以保证事务中多次查询的结果一致，但仍然可能出现幻读，因为在非锁定读取时，允许新记录的插入（即事务 A 查询的范围可能会变化）。

两种隔离等级下，产生幻读的情况看下文具体示例。

1.4 读已提交（Read Committed）

1.4.1 确定事务等级

查看当前事务隔离等级

 SELECT @@transaction_isolation;

将事务隔离等级设为【读已提交】

#设置事务隔离等级

SET SESSION TRANSACTION ISOLATION LEVEL {level};

#{level}可选项:：

#读未提交: READ UNCOMMITTED·

#读已提交: READ COMMITTED

#可重复读: REPEATABLE READ

#可串行化: SERIALIZABLE

1.4.2 非锁定读取

准备

准备一组数据，进行以下测试：事务A执行查询，事务B插入一条记录，事务A再次执行查询。

示例

1.事务A执行查询

select * from tmp where id>=2;

2.事务B插入数据，并提交

insert into `tmp`(`id`,`value`) values (4, 'dd');

3.事务A再次执行查询

结论

事务A两次查询结果不一致，可以读取到事务B已提交的记录，出现【幻读】

1.4.3 锁定读取

准备

准备一组数据，进行以下测试：事务A执行查询，事务B插入一条记录，事务A再次执行查询。

示例

1.事务A进行锁定读取

select * from tmp where id >= 4 for update;

2.事务B，在间隙插入数据，并提交

insert into `tmp`(`id`,`value`) values (5, 'dd');

3.事务A再次执行查询，两次读取结果不一致，发现间隙并没有锁住

并且事务B插入的行已经落库。

分析

然后我们看MySQL文档解释

意思就是，在【读已提交】隔离等级下，针对锁定读取（共享锁or排它锁）、UPDATE语句和DELETE语句，innodb只锁定索引到的记录，锁定记录的前后都能插入新记录。也就说“间隙”并没有被锁住。

再看【间隙锁】这边的解释，间隙锁在读已提交隔离等级下，在查询和索引扫描被禁用了。

结论

        虽然使用了锁定读取，但读已提交（Read Committed）下，幻读依然存在。

1.5 可重复读（Repeatable Read）

1.5.1 确定事务等级

查看当前事务隔离等级

 SELECT @@transaction_isolation;

1.5.2 非锁定读取

准备

准备一组数据，进行以下测试：事务A执行查询，事务B插入一条记录，事务A再次执行查询。

示例

1.事务A执行查询

select * from tmp where id>=4;

2.事务B插入一条记录, 并commit

insert into `tmp`( `id`, `value` ) values (5, 'dd');

3.事务A再次执行查询

select * from tmp where id>=4;

事务B执行commit后，由于事务隔离等级缘故，事务A再次查询，结果不影响，具备可重复读特性；事务A结束后，再次查询多了一条数据（5, ‘dd’）。

结论

        在可重复读（Repeatable Read）隔离等级下，事务内部具备可重复读特性； 但由于“间隙”并未锁住，非锁定读取会出现“幻读”。

1.5.3 锁定读取

准备

准备一组数据，进行以下测试：事务A执行查询，事务B插入一条记录，事务A再次执行查询。

示例

事务A执行查询

select * from tmp where id>=4 for update;

事务B插入一条记录

insert into `tmp`( `id`, `value` ) values (5, 'dd');

这时，发现插入操作被阻塞了。

分析

我们看一下执行的查询语句

select * from tmp where id>=4 for update;

  1. Id=4 所在的行
  2. Id=4 与 id=6 之间的间隙
  3. Id=6 所在的行
  4. Id=6 之后的间隙

这就意味着，在间隙插入记录会被阻止，直到事务A结束。

共享锁与示例的排它锁相同，都是通过阻止其他会话变更，来避免对当前会话的影响。

UPDATE语句、DELETE语句与锁定读取（Locking Reads）同理，在执行时，会根据使用的查询条件进行加锁：

  1. 相等（=、in）：InnoDB只锁定找到的索引记录
  2. 范围（BETWEEN、>、<）：InnoDB锁定扫描的索引范围

要注意的是，对于使用唯一索引的语句，完全没必要进行加锁。

结论

        在可重复读（Repeatable Read）隔离等级下，针对锁定读取（共享锁or排它锁）、UPDATE语句和DELETE语句，不会出现“幻读”。

二、怎么解决幻读？

2.1 提高事务隔离等级

使用可串行化（Serializable），提事务隔离等级来避免。在可串行化隔离等级下,数据库会对查询和写进行加锁，确保事务的完全顺序执行，但是并发效率低下。

2.2 间隙锁（Gap Locking）

间隙锁是加在索引记录之间间隙的锁，又或者是在索引区间第一条记录之前、或最后一条记录之后。

示例：

 select * from users where age between 10 and 30 FOR UPDATE; 

age（10-30）范围内所有现有值之间的间隙都被锁定，防止其他事务将值等于15插入列age中，无论该列中是否已有该值。

三、间隙锁

3.1 定义

        间隙锁（Gap Lock）是一种数据库锁定机制，常见于支持行级锁的数据库（如 MySQL 的 InnoDB 存储引擎）中，主要用于解决并发事务中的“幻读”问题。

3.2 工作原理

假定有数据表

+----+-------+

| id | title |

+----+-------+

|  1 | aa |

|  2 | bb |

|  3 | cc |

+----+-------+

无间隙锁

        事务A查询id > 2 的记录并锁定这条记录，然后事务B插入一条id=4的新纪录，事务A再次查询时会得到不同的结果集。

有间隙锁

        事务A执行查询 id > 2时，这条查询会锁定 id > 2 的记录、所有行之间的间隙。

3.3 间隙锁的类型

范围查询

当事务执行范围查询（如BETWEEN、>、<等）时，数据库会在查询范围内加上间隙锁。

例如：

select * from `tmp` where `id` > 5 for update;

可重复读（Repeatable Read）隔离等级下，可能会锁定 id >5 的所有间隙.

相等查询

当事务执行相等查询（如 = ）时，数据库会锁定对应的索引记录。如果索引记录不存在，则会锁定对应的间隙，防止其他事务插入重复的记录。

示例：

+----+-------+

| id | value |

+----+-------+

|  2 | aa    |

|  4 | bb    |

|  6 | cc    |

+----+-------+

事务A, 执行查询, 锁住id=5的间隙

select * from tmp where id=5 for update;

事务B, 尝试在id=5插入数据

insert into `tmp`( `id`, `value` ) values ( 5, 'bb' );

发现事务B，在事务A未结束的情况下，一直被阻塞直至超时

参考资料

MySQL数据库事务隔离等级：The InnoDB Engine: Transaction Isolation Levels

InnoDB一致性非锁定读取：The InnoDB Engine | Consistent Nonlocking Reads

InnoDB幻影行（幻读）：The InnoDB Engine | Phantom Rows

InnoDB间隙锁：The InnoDB Engine | Gap locking

Mysql 是如何解决幻读问题的？
sinat_53467514的博客
12-28 1822
虽然 InnoDB 中通过间隙锁的方式解决了幻读问题，但是加锁之后一定会影响到并发性能，因此，如果对性能要求较高的业务场景中，可以把隔离级别设置成 RC，这个级别中不存在间隙锁。好了，今天的分享就到这里，如果你在面试中遇到了比较奇葩的问题，欢迎在评论区留言。
你知道MySQL是如何解决幻读的吗？
m0_71777195的博客
11-28 8275
SQL标准中定义了4种隔离级别，分别是读未提交、读已提交、可重复读以及序列化。不同的隔离级别下，可以解决不同的并发问题，如下图所示。当然MySQL也基本遵循了这个标准，但是在实现上稍有不同。本文重点探讨下MySQL是如何解决幻读问题的，首先串行化隔离级别铁定是可以解决所有的并发问题，相当于每个事务按顺序执行，但是性能很差，不是本文重点讨论对象。实际上MySQL默认的事务隔离级别是可重复读，难道这种隔离级别下MySQL就不管幻读问题了吗？其实不是的，本文就带大家一起看看。
从零起步学习MySQL || 第十四章：MySQL 事务中如何解决幻读问题（MVCC中通过ReadView来解决幻读问题详解）
最新发布
ze15829163918的博客
11-18 797
MySQL通过MVCC机制解决幻读问题。在可重复读隔离级别下，InnoDB使用事务级的readview（读视图）记录事务开始时的数据快照，并结合undo日志实现多版本读取。快照读不会看到其他事务后续插入的新行，从而在读端避免幻读。但对于读写操作，还需通过next-key锁防止其他事务在查询范围内插入数据。MVCC通过保存历史版本实现了读不阻塞写的高并发特性，但需注意长事务可能导致undo日志膨胀。
MySQL是如何解决幻读的
12-14
先看这篇博文讲的是事务特征、事务隔离级别，事务的sql语句https://blog.youkuaiyun.com/apologizetm/article/details/88389785 一、什么是幻读 在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行 二、为什么要解决幻读 在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。 三、MySQL 是如何解决幻读的 1. 多版本并发控制（MVCC）（快照读） 多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。 以 InnoDB 为例，每一行中都冗余了两个字段。一个是行的创
MySQL如何解决幻读
qq_44644452的博客
12-02 3254
MVCC，数据库，隔离引擎
MySQL 是如何解决幻读的
weixin_30682127的博客
04-04 1444
MySQL 是如何解决幻读的 一、什么是幻读 在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。 而多出来或者少的哪一行被叫做 幻行 二、为什么要解决幻读 在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。 三、MySQL 是如何解决幻读的 如果你看到了这篇文章，那么我会默认你了解了 脏读 、不可重复读与可重复读。 1. 多版本并发控制（MVCC）（快照读/...
MySQL可重复读级别能够解决幻读吗
09-09
MySQL的可重复读（Repeatable Read）隔离级别是其事务管理机制的一部分，旨在解决数据库并发操作中的一些问题，如脏读、不可重复读和幻读。然而，标题中提出的问题在于，可重复读隔离级别是否能完全防止幻读。在这个...
详解MySQL是如何解决幻读的
12-16
三、MySQL 是如何解决幻读的 如果你看到了这篇文章，那么我会默认你了解了 脏读 、不可重复读与可重复读。 1. 多版本并发控制（MVCC）（快照读） 多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的...
【MySQL】幻读是什么？如何避免幻读？
柠檬树
08-20 2954
一、前言 建表语句和初始化语句如下： CREATE TABLE `t`( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY(`id`), KEY `c`(`c`) )ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,...
Mysql如何解决幻读：
热门推荐
科蒙D的博客
09-10 1万+
1、事务隔离级别： 在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做幻行，也就是说当一个事务在进行读取数据的时候，其他事务对该数据进行了改变。在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。 脏读：比如A事务读取到了B事务还没有提交的数据，因为什么原因B事务回滚了，那么A事务读取的数据和数据库中的数据不同，也就是读到了其他事务没有提交的数据。 读取已提交会产生不可重复读：...
MySQL是如何解决幻读
一切如你的博客
12-02 2533
MySQL是如何解决幻读
MySQL是如何解决幻读问题的
qq_38803590的博客
02-22 1071
mysql隔离级别
Mysql如何解决幻读问题
weixin_43521001的博客
06-16 618
幻读(Phantom Read)是数据库并发控制中的一个重要问题，指在同一事务内，连续执行两次相同的查询，第二次查询看到了第一次查询未看到的新插入行(这些行是其他事务提交的)。
Mysql 解决幻读的问题
z3551906947的博客
08-12 1748
事务是数据库管理系统中执行的一系列操作，这些操作作为一个整体被执行，以确保数据的完整性和一致性。原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个点。一致性（Consistency）：事务必须保证数据库从一个一致的状态转移到另一个一致的状态。隔离性（Isolation）：并发执行的事务之间不会互相影响。持久性（Durability）：一旦事务提交，它对数据库的改变就是永久性的，即使系统发生故障也不会丢失。
MySQL的幻读是怎么被解决的？
小林coding
09-18 4823
大家好，我是小林。 我之前写过一篇数据库事务的文章「 事务、事务隔离级别和MVCC」，这篇我说过什么是幻读。 然后前几天有位读者跟我说，我这个幻读例子不是已经被「可重复读」隔离级别解决了吗？为什么还要有 next-key 呢？ 他有这个质疑，是因为他做了这个实验。 实验的数据库表 t_stu 如下，其中 id 为主键。 然后在可重复读隔离级别下，有两个事务的执行顺序如下： 从这个实验结果可以看到，即使事务 B 中途插入了一条记录，事务 A 前后两次查询的结果集都是一样的，并没有出现所谓的幻读现象。 读
mysql 幻读
05-06
### MySQL 中幻读的概念及解决方案 #### 幻读的定义 幻读是一种在并发环境下可能出现的数据一致性问题。当某个事务多次执行同一查询时，由于其他事务插入或删除了数据，导致每次查询的结果集不同，这种现象被称为幻读[^1]。 #### 幻读的影响 幻读可能会影响数据库的可靠性和数据的一致性，尤其是在需要严格控制数据状态的应用场景中。因此，在设计数据库应用时，需特别关注如何避免幻读的发生。 #### 事务隔离级别的作用 MySQL 提供了四种主要的事务隔离级别，分别是 `READ UNCOMMITTED`、`READ COMMITTED`、`REPEATABLE READ` 和 `SERIALIZABLE`。这些隔离级别通过不同的方式控制事务间的可见性和交互行为，从而减少诸如幻读等问题的发生[^2]。 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | |--------------------|------------|----------------|--------------| | **READ UNCOMMITTED** | 可能发生 | 可能发生 | 可能发生 | | **READ COMMITTED** | 解决 | 可能发生 | 可能发生 | | **REPEATABLE READ** | 解决 | 解决 | 可能发生 | | **SERIALIZABLE** | 解决 | 解决 | 解决 | #### 各隔离级别下的幻读处理能力 - 在 `READ UNCOMMITTED` 和 `READ COMMITTED` 下，幻读无法被完全解决。 - 在 `REPEATABLE READ` 下，默认情况下 MVCC（多版本并发控制）机制能够防止大多数类型的不一致读取，但对于新插入的记录仍可能存在幻读风险。 - 在最高隔离级别 `SERIALIZABLE` 下，所有事务都以串行的方式运行，彻底杜绝了幻读的可能性，但会显著降低系统的性能和吞吐量[^4]。 #### 解决幻读的具体方法 针对幻读问题，有多种可行的解决方案： 1. **提升隔离级别至 SERIALIZABLE** 将事务隔离级别设置为 `SERIALIZABLE` 是一种简单而有效的方法，因为在这种模式下所有的操作都会按照严格的顺序依次执行，从根本上消除了幻读的风险。然而，这种方法会对系统性能带来较大的负面影响，适用于对一致性要求极高且性能压力较小的场景[^2]。 2. **使用 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE** 如果应用程序允许显式的锁管理，则可以在查询语句后面附加 `FOR UPDATE` 或者 `LOCK IN SHARE MODE` 子句。这将强制锁定所涉及的行或者范围，阻止其他事务在此期间修改或新增相关数据，进而达到预防幻读的效果[^3]。 ```sql -- 使用 FOR UPDATE 锁定目标行 SELECT * FROM t_stu WHERE id = 5 FOR UPDATE; -- 使用 LOCK IN SHARE MODE 实现共享锁 SELECT * FROM t_stu WHERE id = 5 LOCK IN SHARE MODE; ``` 3. **调整业务逻辑** 对于某些特定场景，也可以通过对业务流程的设计优化来间接规避幻读问题。例如，先获取当前最新的快照作为基础再做进一步的操作，而不是依赖实时动态变化的数据集合。 --- ### 总结 综上所述，幻读问题是数据库领域常见的挑战之一，但在 MySQL 数据库中有成熟的应对策略可供选择。依据实际需求权衡性能与一致性之间的关系，合理选用适合的事务隔离级别以及辅助技术手段即可妥善解决问题。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值