MySQL如何解决幻读？

server/2024/11/18 9:34:03/

目录

一、什么是幻读？

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


http://www.ppmy.cn/server/142871.html

相关文章

微知-Linux存在多个内核如何设置系统默认启动内核？

微知-Linux存在多个内核如何设置系统默认启动内核？

查看 grubby --default-kernel 可以查看当前默认的启动内核路径。 grubby --infoALL 列出所有已安装的内核及其信息。 设置 grubby --set-default-kernel/boot/vmlinuz-5.9.1-1.el8.elrepo.x86_64 设置默认启动内核 grubby --set-default-index3 通过索引号来设置默认内核&a…
阅读更多...
JavaScript 中的多重继承与 ES6 中的继承

JavaScript 中的多重继承与 ES6 中的继承

JavaScript 中的多重继承与 ES6 中的继承 引言&#xff1a; 在 JavaScript 的奇妙世界中&#xff0c;继承如同一条坚韧的线索&#xff0c;将代码的各个部分紧密相连&#xff0c;构建出复杂而有序的程序结构。今天&#xff0c;我们将深入探索 JavaScript 中的多重继承以及 ES6…
阅读更多...
GOOGLE EARTH ENGINE——利用GEE计算和下载雪的覆盖频率（SCF）和雪的消失日期（SDD）含全球除格陵兰岛外的矢量

GOOGLE EARTH ENGINE——利用GEE计算和下载雪的覆盖频率（SCF）和雪的消失日期（SDD）含全球除格陵兰岛外的矢量

目录 简介 数据介绍 MODIS/006/MOD10A1 代码解释 代码 结果 简介 利用GEE计算和下载雪的覆盖频率（SCF）和雪的消失日期（SDD） 数据介绍 MODIS/006/MOD10A1 MODIS/006/MOD10A1数据是由美国国家航空航天局（NASA）的Moderate Resolution Imaging Spectroradiometer（…
阅读更多...
大数据-226 离线数仓 - Flume 优化配置 自定义拦截器 拦截原理 拦截器实现 Java

大数据-226 离线数仓 - Flume 优化配置 自定义拦截器 拦截原理 拦截器实现 Java

点一下关注吧&#xff01;&#xff01;&#xff01;非常感谢&#xff01;&#xff01;持续更新&#xff01;&#xff01;&#xff01; Java篇开始了&#xff01; 目前开始更新 MyBatis&#xff0c;一起深入浅出&#xff01; 目前已经更新到了&#xff1a; Hadoop&#xff0…
阅读更多...
【大数据学习 | flume】flume之常见的sink组件

【大数据学习 | flume】flume之常见的sink组件

Flume Sink取出Channel中的数据&#xff0c;进行相应的存储文件系统&#xff0c;数据库&#xff0c;或者提交到远程服务器。Flume也提供了各种sink的实现&#xff0c;包括HDFS sink、Logger sink、Avro sink、File Roll sink、HBase sink&#xff0c;。 ​ Flume Sink在设置存…
阅读更多...
【windows】05-windows系统级深度隐藏文件方法

【windows】05-windows系统级深度隐藏文件方法

文章目录 高级文件隐藏技巧基本用法隐藏文件或文件夹显示隐藏的文件或文件夹 完整语法 高级文件隐藏技巧 使用 attrib 命令可以在 Windows 系统中快速修改文件和文件夹的属性&#xff0c;实现更高级的隐藏。这种方法不仅隐藏文件&#xff0c;而且设置为系统文件&#xff0c;使…
阅读更多...
使用vscode+expo+Android夜神模拟器运行react-native项目

使用vscode+expo+Android夜神模拟器运行react-native项目

1.进入夜神模拟器安装路径下的bin目录 2.输入命令&#xff0c;连接Android Studio 启动夜神模拟器后&#xff0c; 打开安装目录的bin文件夹执行下面的命令&#xff0c;只需执行一次&#xff09; nox_adb.exe connect 127.0.0.1:62001adb connect 127.0.0.1:62001 3.运行项目…
阅读更多...
Springboot整合xxl-job

Springboot整合xxl-job

拉取xxl-job xxl-job: 一个分布式任务调度平台&#xff0c;其核心设计目标是开发迅速、学习简单、轻量级、易扩展。现已开放源代码并接入多家公司线上产品线&#xff0c;开箱即用。 配置项目 执行sql语句 更改配置 启动 访问 任务调度中心http://127.0.0.1:8081/xxl-job-a…
阅读更多...
最新文章

Copyright @ 2022~2023