MySQL如何解决幻读？

已于 2024-11-15 17:24:09 修改
阅读量2.3k 收藏 21
点赞数 27
分类专栏： mysql 文章标签： mysql 数据库
于 2024-11-15 17:19:23 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/achandy/article/details/143801977
版权

目录

一、什么是幻读？

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锁定扫描的索引范围

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

注：

我们知道，默认情况下，MySQL是在autocommit开启的状态下运行。开启事务（START TRANSACTION）后，自动提交模式(autocommit)会隐式禁用，所有的纯SELECT语句会被转化成SELECT.....LOCK IN SHARE MODE, 但是如果想要锁住间隙或是锁住索引记录的话，需要显示触发锁定行为。

结论

        在可重复读（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

1.MySQL面试题之innodb如何解决幻读
前端、移动端、后端源码级底层原理分享
08-02 2090
在数据库系统中，幻读（Phantom Read）是指在一个事务中，两次读取同一范围的数据集时，由于其他事务的插入操作，导致第二次读取结果集发生变化的问题。InnoDB 作为 MySQL 的一个存储引擎，通过多种机制来解决幻读问题，主要包括锁机制和隔离级别。
MySQL是如何解决幻读的
12-14
先看这篇博文讲的是事务特征、事务隔离级别，事务的sql语句https://blog.youkuaiyun.com/apologizetm/article/details/88389785 一、什么是幻读 在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行 二、为什么要解决幻读 在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。 三、MySQL 是如何解决幻读的 1. 多版本并发控制（MVCC）（快照读） 多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。 以 InnoDB 为例，每一行中都冗余了两个字段。一个是行的创
事务隔离级别及Mysql的支持
炎黄的专栏
11-26 1327
MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）。 未提交读（READUNCOMMITTED）。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。 提交读（READCOMMITTED）。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的
Mysql(Innodb)如何避免幻读
FaN()
12-19 2万+
幻读Phantom Rows The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but return
MySQL 幻读
一起加油！！！
11-01 4355
快照读（普通select语句），是通过MVCC 方式解决了幻读当前读（select … for update），是通过next-key lock（记录锁+间隙锁）来解决幻读但MySQL 可重复读隔离界别并没有彻底解决幻读问题，只是很大程度上解决了幻读现象非发生。
MySQL中的幻读
ByLir的博客
05-27 3128
MySQL的默认隔离级别可重复读很大程度上解决了幻读问题。在快照读情况下是通过MVCC解决的，在第一次执行查询语句时生成一张Read View，后续每次快照读都是读这张Read View。在当前读情况下是加锁来解决的，会阻塞其他事务的当前读，从而避免幻读。然而可重复读并不能完全解决幻读，当一个事务里面使用快照读之后又使用当前读的话就还是会出现幻读。
再探幻读！什么是幻读?为什么会产生幻读，MySQL中是怎么解决幻读的？
ZhaoSimonone的博客
12-10 5万+
本文阐述了自己对于幻读的看法，对于什么是幻读，本文给出了自己的解释。除此之外，本文解释了为什么在可重复读的隔离条件下还会产生幻读，并分析了MySQL中是怎么通过引入间隙锁来解决幻读的
七. 幻读是什么，幻读有什么问题
Tyella的博客
10-23 1万+
什么是幻读 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。 在可重复读隔离级别下，普通的查询是快照读，是不会看到 别的事务插入的数据的。因此，幻读在“当前读”下才会出现。 上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为“幻读”。幻读仅专指“新插入的行”。 我们新建一张表，插入6条数据： CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DE
Mysql如何解决幻读：
科蒙D的博客
09-10 1万+
1、事务隔离级别： 在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做幻行，也就是说当一个事务在进行读取数据的时候，其他事务对该数据进行了改变。在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。 脏读：比如A事务读取到了B事务还没有提交的数据，因为什么原因B事务回滚了，那么A事务读取的数据和数据库中的数据不同，也就是读到了其他事务没有提交的数据。 读取已提交会产生不可重复读：...
详解MySQL是如何解决幻读的
12-16
三、MySQL 是如何解决幻读的 如果你看到了这篇文章，那么我会默认你了解了 脏读 、不可重复读与可重复读。 1. 多版本并发控制（MVCC）（快照读） 多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的...
MySQL可重复读级别能够解决幻读吗
09-09
MySQL的可重复读（Repeatable Read）隔离级别是其事务管理机制的一部分，旨在解决数据库并发操作中的一些问题，如脏读、不可重复读和幻读。然而，标题中提出的问题在于，可重复读隔离级别是否能完全防止幻读。在这个...
脏读幻读和不可重复读 数据库锁 隔离机制
热门推荐
song854601134的博客
06-06 6万+
一、脏读、不可重复读、幻读1、脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。 例如： 张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。 与此同时， 事务B正在读取张三的工资，读取到张三的工资为8000。 随后， 事务A发生异常，而回滚了事务。张三的工资又回滚为5000。 最后， 事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。2、不可重复读：是指在一个事务内，多
你知道MySQL是如何解决幻读的吗？
m0_71777195的博客
11-28 8156
SQL标准中定义了4种隔离级别，分别是读未提交、读已提交、可重复读以及序列化。不同的隔离级别下，可以解决不同的并发问题，如下图所示。当然MySQL也基本遵循了这个标准，但是在实现上稍有不同。本文重点探讨下MySQL是如何解决幻读问题的，首先串行化隔离级别铁定是可以解决所有的并发问题，相当于每个事务按顺序执行，但是性能很差，不是本文重点讨论对象。实际上MySQL默认的事务隔离级别是可重复读，难道这种隔离级别下MySQL就不管幻读问题了吗？其实不是的，本文就带大家一起看看。
如何解决幻读？
m0_51167384的博客
04-04 1万+
一.什么是幻读？ 幻读：一个事务按相同的查询条件重新读取以前的检索过的数据，却发现其他事务插入了满足其条件查询的新数据，这种现象被成为幻读。 进行了读取，分别读取了不同的数据，重点在于新增（insert），针对多笔数据。 举个例子：事务A对数据进行查询，这时事物B开启，对其中一笔数据进行了新增，然后进行了提交（这里进行了提交），然后事务A又对数据进行了查询，发现查询所得的结果集是不一样的。幻读针对的是多笔记录。读提交（Read Committed）是不足以解决的，需进行Serializable 序列化就能解
MySQL如何解决幻读
qq_44644452的博客
12-02 3123
MVCC，数据库，隔离引擎
MySQL是如何解决幻读
一切如你的博客
12-02 2342
MySQL是如何解决幻读
Mysql幻读
weixin_46488959的博客
05-10 1238
5、范围查询会访问到不满足条件的第一个值为止(唯一索引也一样)。
幻读及其解决方法
qq_46130027的博客
06-24 1880
幻读是指在同一个事务中，前后两次查询相同范围的时候，得到的结果不一致。第一个事务里面，我们执行了一个范围查询。这个时候满足条件的数据只有一条。而在第二个事务里面，它插入了一行数据并且进行了提交。接着第一个事务再去查询的时候，得到的结果比第一次查询的结果多出来了一条数据。注意第一个事务的第一次查询和第二次查询都在同一个事务里面。所以幻读会带来数据一致性的问题。虽然InnoDB里面通过间隙锁的方式解决了幻读的问题，但是加锁之后一定会影响到并发的性能。
什么是幻读？怎么解决幻读问题？
最新发布
01-25
### 定义幻读 幻读指的是在一个事务内两次查询返回的结果集不同，即使在这两个操作期间没有其他事务对该数据进行修改。具体来说，在同一个事务中先后执行相同的查询语句时，第二次查询得到了第一次查询之后新增加或删除的数据行[^3]。 ### 数据库事务隔离级别中的幻读现象 在不同的数据库事务隔离级别下，幻读的发生与否有所不同： - **READ UNCOMMITTED (读取未提交)** 和 **READ COMMITTED (读取已提交)** 都无法防止幻读，因为在这些较低级别的隔离条件下，不仅不能保障同一记录的一致性读取，甚至也无法阻止新记录的插入影响到当前事务内的后续读取结果。 - **REPEATABLE READ (可重复读)** 虽然能够保证在同一事务内部对于相同条件下的多条SELECT语句得到一致性的结果，但由于其主要针对的是现有记录的变化控制，并不涉及对范围锁定的支持，所以仍然可能出现新的符合条件的新纪录被插入而导致的幻读问题[^1]。 - **SERIALIZABLE (序列化)** 是唯一能彻底杜绝幻读发生的隔离级别，它通过对整个表施加共享锁的方式确保任何写入操作都需要等待所有正在运行的只读事务完成，从而实现了最严格的隔离效果。 ### 解决方案 为了有效应对幻读问题，通常有如下几种方法： #### 使用更高的隔离级别 将事务的隔离级别提升至 `SERIALIZABLE` 可以从根本上消除幻读的可能性，不过这也会带来性能上的开销，因为它强制要求所有的并发访问按照顺序排队处理，降低了系统的并行度和响应速度。 #### 应用特定类型的锁机制 例如 MySQL InnoDB 存储引擎采用了一种称为**间隙锁**(Gap Lock)的技术来缓解幻读的影响。当一个事务获取了某个索引范围内部分记录的选择权后，除了会对实际选中的那些行加上排他锁外，还会对其前后相邻位置之间的“空白区域”也实施保护措施，以此阻止其他事务在此区间内插入新的记录[^2]。 ```sql -- 示例：显示如何启用InnoDB存储引擎默认使用的Next-Key Locks特性（包括Row-Level Locking and Gap Locking） SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; SELECT * FROM table_name WHERE id BETWEEN 5 AND 10 FOR UPDATE; -- 这里会应用Next-Key Lock INSERT INTO table_name VALUES (7, 'value'); -- 如果此时另一事务尝试插入这条记录，则会被阻塞直到上述事务结束 COMMIT; ```
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值