MySQL的MVCC是否解决幻读

已于 2023-07-19 09:35:47 修改
阅读量893 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
于 2023-07-18 20:51:54 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/huchao_lingo/article/details/131796155
版权

MySQL的MVCC是否解决幻读

MySQL事务隔离级别

✓ 读未提交（Read Uncommitted）：最低的隔离级别，会读取到其他事务还未提交的内容，存在脏读。

✓ 读已提交（Read Committed）：读取到的内容都是已经提交的，可以解决脏读，但是存在不可重复读。

✓ 可重复读（Repeatable Read）：在一个事务中多次读取时看到相同的内容，可以解决不可重复读，但是存在幻读。但是在 InnoDB 中不存在幻读问题，对于快照读，InnoDB 使用 MVCC 解决幻读，对于当前读，InnoDB 通过 gap locks 或 next-key locks 解决幻读。

✓ 串行化（Serializable）：最高的隔离级别，串行的执行事务，没有并发事务问题。

MySQL 默认的事务隔离级别是可重复读（REPEATABLE READ）

简单总结一下，MySQL 的 4 种事务隔离级别对应脏读、不可重复读和幻读的关系如下：

事务隔离级别

脏读

不可重复读

幻读

读未提交（READ UNCOMMITTED）

√

√

√

读已提交（READ COMMITTED）

×

√

√

可重复读（REPEATABLE READ）

×

×

√

串行化（SERIALIZABLE）

×

×

×

不可重复度和幻读的区别

不可重复读 主要是说多次读取一条记录, 发现该记录中某些列值被修改过。

幻读 主要是说多次读取一个范围内的记录(包括直接查询所有记录结果或者做聚合统计), 发现结果不一致(标准档案一般指记录增多, 记录的减少应该也算是幻读)。(可以参考)

模拟幻读问题

环境准备

事务相关的命令

# 查看 MySQL 版本
select version();

# 开启事务
start transaction;

# 提交事务
commit;

# 回滚事务
rollback;

MySQL8查询事务隔离级别的命令

select @@global.transaction_isolation,@@transaction_isolation;

通过以下 SQL 可以设置当前客户端的事务隔离级别：

set session transaction isolation level 事务隔离级别;

事务隔离级别的值有 4 个：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

测试数据准备

创建测试数据库和表信息，执行 SQL 如下：

-- 创建数据库
drop database if exists testdb;
create database testdb;
use testdb;
-- 创建表
create table userinfo(
  id int primary key auto_increment,
  name varchar(250) not null,
  balance decimal(10,2) not null default 0
);
-- 插入测试数据
insert into userinfo(id,name,balance) values(1,'Java',100),(2,'MySQL',200);

创建的表结构和数据如下：

mysql> select * from userinfo;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | Java  | 100.00  |
|  2 | MySQL | 200.00  |
+----+-------+---------+
2 rows in set (0.00 sec)

幻读模拟

接下来会使用两个窗口（两个客户端）来演示事务在可重复读隔离级别下的幻读的问题。

幻读场景1

在一个事务中明明没有查到主键为 X 的数据，但主键为 X 的数据就是插入不进去，就像某种幻觉一样。 幻读演示的执行流程如下：

执行步骤

客户端1（窗口1）

客户端2（窗口2）

说明

第 1 步

set session transaction isolation

 level repeatable read;

start transaction;

select * from userinfo where id=3;

设置事务隔离级别为可重复读；开启事务；查询用户编号为 3 的数据，查询结果为空。

第 2 步

start transaction;

insert into userinfo(id,name,balance) 

values(3,'Spring',100);

commit;

开启事务；添加用户，用户编号为 3；提交事务。

第 3 步

insert into userinfo(id,name,balance)

 values(3,'Spring',100);

窗口 2 添加用户编号为 3 的数据，执行失败。

第 4 步

select * from userinfo where id=3;

查询用户编号为 3 的数据，查询结果为空。

在窗口2中查询id为3的记录为空

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userinfo where id=3;
Empty set

在窗口1中插入一条id为3的记录

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into userinfo(id,name,balance) values(3,'Spring',100);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

在窗口2中插入id为3的记录

insert into userinfo(id,name,balance) values(3,'Spring',100);

发现插入失败

mysql> insert into userinfo(id,name,balance) values(3,'Spring',100);
1062 - Duplicate entry '3' for key 'userinfo.PRIMARY'

继续在窗口2查询id为3的记录，发现查询不到

mysql> select * from userinfo where id=3;
Empty set

幻读场景2

在一个事务A中一开始查询不到id为X的记录，但在另一个事务B中插入id为X的记录并提交事务后，在事务A中再更新id为X的记录可以更新到，再次查询id为X的记录也可以查询到。

执行步骤

客户端1（窗口1）

客户端2（窗口2）

说明

第 1 步

set session transaction isolation

 level repeatable read;

start transaction;

select * from userinfo where id=4;

设置事务隔离级别为可重复读；开启事务；查询用户编号为 4 的数据，查询结果为空。

第 2 步

set session transaction isolation

 level repeatable read;

start transaction;

select * from userinfo where id=4;

insert into userinfo(id,name,balance)

 values(4,'Redis',100);

commit;

开启事务；添加用户，用户编号为 4；提交事务。

第 3 步

select * from userinfo where id=4;

查询用户编号为 3 的数据，查询结果为空。此时还未出现幻读问题

第 4 步

update userinfo set name='kafka' 

where id = 4;

更新用户编号为4的记录，更新成功

第 5 步

select * from userinfo where id=4;

查询到用户编号为4的记录，查询到数据，出现幻读问题

第一步，在窗口1中查询id为4的记录，查询结果为空

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from userinfo where id=4;

Empty set

第二步，在窗口2中查询id为4的记录，查询结果为空。

插入一条id为4 的记录并提交事务

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from userinfo where id=4;

Empty set
 
mysql> insert into userinfo(id,name,balance) values(4,'Redis',100);

Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql>

第三步，在窗口1中查询id为4 的记录，查询结果为空，此时还没有幻读问题

mysql> select * from userinfo where id=4;

Empty set

第四步，在窗口1中更新id为4 的记录，更新成功

mysql> update userinfo set name='kafka' where id = 4;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

第五步，在窗口1中查询id为4 的记录，查询到更新后的数据，出现幻读问题

mysql> select * from userinfo where id=4;

+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  4 | kafka | 100.00  |
+----+-------+---------+
1 row in set (0.00 sec)

幻读场景3

在事务A中查询id为X的记录查询不到，在事务B中插入一条id为X的记录后，再到事务A中对表执行当前读 for update可以查询到id为X的记录。

执行步骤

客户端1（窗口1）

客户端2（窗口2）

说明

第 1 步

set session transaction isolation
 level repeatable read;

start transaction;

select * from userinfo;

设置事务隔离级别为可重复读；开启事务；查询用户表所有数据，查询结果为4条。

第 2 步

set session transaction isolation

 level repeatable read;

start transaction;

select * from userinfo where id=5;

insert into userinfo(id,name,balance) 

values(5,'Pulsar',100);

commit;

开启事务；添加用户，用户编号为 5；提交事务。

第 3 步

select * from userinfo for update;

执行当前读for update，查询出了id为5的记录，出现幻读问题。

第一步，在窗口1中查询用户表的所有记录，查询结果为4条

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from userinfo;

+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | Java   | 100.00  |
|  2 | MySQL  | 200.00  |
|  3 | Spring | 100.00  |
|  4 | kafka  | 100.00  |
+----+--------+---------+
4 rows in set (0.00 sec)

第二步，在窗口2中查询id为5的记录，查询结果为空。

插入一条id为5 的记录并提交事务

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from userinfo where id=5;

Empty set
 
mysql> insert into userinfo(id,name,balance) values(5,'Pulsar',100);

Query OK, 1 row affected (0.00 sec)
mysql> commit;

Query OK, 0 rows affected (0.00 sec)

第三步，窗口1执行当前读for update，查询出了id为5的记录，出现了幻读问题。

mysql> select * from userinfo for update;

+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | Java   | 100.00  |
|  2 | MySQL  | 200.00  |
|  3 | Spring | 100.00  |
|  4 | kafka  | 100.00  |
|  5 | Pulsar | 100.00  |
+----+--------+---------+
5 rows in set (0.00 sec)

结论

不可重复读的重点在于 update 和 delete, 而幻读着重强调 insert 操作, 当用户第一次读取时，存在结果集 a, b, c，第二次读取时结果集为 a, b, c, d 即多出来的一条数据 d 表示发送幻读现象。

不可重复读的现象是用户第一次读取，返回结果集 a, 而第二次读取返回结果 a’, 即数据发生了变更. 而 a’ 数据确实由一个已经 committed 的事务修改. 不可重复读现象需要隔离级别为 Repeatable Read 来规避. 在 InnoDB 中使用 MVCC 机制实现。

通常来说, MVCC 的多版本控制并不能保证在 RR 隔离级别下完全避免幻读, 但 InnoDB 通过 MVCC + Next key lock 的方式来保证在 RR 隔离级别下避免部分场景下幻读。

MySQL之MVCC与幻读
年轻气盛 温顺尚早
11-30 3533
文章目录MVCC1. 什么是MVCC？2. MVCC解决什么问题？3. MVCC如何工作？隐藏字段事务号undo log幻读当前读和快照读如何解决幻读参考文章 MVCC 1. 什么是MVCC？ 多版本并发控制技术的英文全称是 Multiversion Concurrency Control，简称 MVCC 。 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
MySQL核心知识：MVCC是如何解决快照读下的幻读问题的
wdj_yyds的博客
08-05 213
LBCC是基于锁的并发控制，因为锁的粒度过大，会导致性能的下降，因此提出了比 LBCC 性能更优越的方法 MVCC。MVCC 是基于多版本的并发控制协议，通过版本号，避免同一数据在不同事务间的竞争，只存在于InnoDB引擎下。MVCC 主要是为了提高数据库的并发读写性能，不用加锁就能让多个事务并发读写。MVCC的实现依赖于：三个隐藏字段、Undo log和Read View。MVCC 的核心思想就是：只能查找事务id小于等于当前事务ID的行；
mysql使用MVCC来解决幻读_MVCC 能解决幻读吗？
weixin_35604021的博客
01-20 890
昨天面试当中一个小伙认为MVCC可以解决RR中的幻读问题， 先说结论， MVCC不能解决幻读，很遗憾还是得用锁。 下面详细描述一下。InnoDB的MVCC, 实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)， 用于支持RC...
【MySQL】面试题之：MVCC能否解决幻读？
热门推荐
CAFE-BABE的博客
08-01 1万+
“幻读”指，同一个事务里面连续执行两次同样的sql语句，可能导致不同结果的问题，第二次sql语句可能会返回之前不存在的行。 先给出结论：不能笼统的说能不能解决，因为有的情况下可以解决，但是有的情况下解决不了。 情况1 mysql里面实际上有两种读，一种是“快照读”，比如我们使用select进行查询，就是快照读，在“快照读"的情况下是可以解决“幻读”的问题的。使用的就是MVCC，具体来说如下图，几个事务并发执行： 可以看到，尽管别的事务已经提交插入和更新，但是事务A的select读取...
【MySQL】幻读被彻底解决了吗？
yuxiangdeming的博客
07-07 1390
幻读是数据库中的一种并发问题，指在同一事务中执行两次相同的查询，但第二次查询返回了第一次查询所没有的新数据行。前后读取的记录数不一致举个例子：这是一个电商的大致逻辑，一般用户购买商品后付的钱会先冻结在平台上，然后由平台在固定的时间内结算用户款，例如七天一结算、半月一结算等方式，在结算业务中通常都会涉及到核销处理，也就是将所有为【已签收状态】的订单改为【已核销状态】。
MySQL系列-仅靠MVCC就能解决幻读？错！Gap锁了解一下？
qq_39696697的博客
07-25 374
前言 上篇MySQL系列有提到间隙锁，但是我感觉没有讲清楚，仅仅只是介绍了一下间隙锁，所以再来记录一下间隙锁。 上篇传送门：https://juejin.cn/post/6986223980269191182#heading-3 先说一下结论： 读，分为当前读和快照读，MVCC解决的是快照读的幻读问题，而当前读的幻读问题需要Gap锁+行锁来解决。 一、Gap锁（间隙锁）的概念 Innodb支持三种行锁定方式： 行锁：锁的是索引，如果SQL没有走索引，那么会全表扫描，从而升级为表锁。 PS: 想必肯
MySQL(五)—MVCC解决可重复读的幻读了吗？
shang_0122的博客
09-14 2371
MVCC解决可重复读的幻读了吗？
详解MySQL是如何解决幻读的
12-16
三、MySQL 是如何解决幻读的 如果你看到了这篇文章，那么我会默认你了解了 脏读 、不可重复读与可重复读。 1. 多版本并发控制（MVCC）（快照读） 多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的...
MySQL是如何解决幻读的
12-14
为了解决幻读，MySQL的InnoDB存储引擎采取了两种主要策略：多版本并发控制（MVCC）和Next-Key锁。 1. **多版本并发控制（MVCC，Multiversion Concurrency Control）**： MVCC是一种并发控制机制，它允许事务看到...
MySQL(九)：MVCC能否解决幻读问题
Edwin_Hu的博客
05-28 1万+
面试必问。聊聊MVCC到底能否解决幻读问题
mysql MVCC产生幻读原因及解决方式
weixin_44145478的博客
08-25 4472
事务隔离级别有四种，mysql默认使用的是可重复读，mysql是怎么实现可重复读的？为什么会出现幻读？是否解决了幻读的问题？ 一、事务的隔离级别 Read Uncommitted（未提交读） 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读（Dirty Read）。该级别用的很少。 Read Committed（提交读） 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变，换句话说就是事务提交
既然MySQL中InnoDB使用MVCC，为什么REPEATABLE-READ不能消除幻读？(仅仅记录)
xts5701046的博客
08-04 3134
共有11个答案 最后回答: 9个月前 按票数排序 显示最新答案 1 你看的高性能mysql那本书么?我也测试过,要看下你的代码例子,如果你A事务insert提交了, B事务还是可以读取到的,如果你B在A提交之前先select一下,然后就不会读到了,一致性读  consistent read --- 共有 1 条评论 --- 炁元： 你试试在同两个session窗中更改不同隔离级别测试，R...
MySQL的MVCC机制可以完全解决幻读么？
我只是无聊
06-11 1525
定义 MVCC MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。 在MySQL，MVCC并不是Server级别的实现，而是InnoDB引擎的实现。 快照读 像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是
mysql使用MVCC来解决幻读_MVCC能否解决幻读
weixin_39545310的博客
02-02 973
Mysql(Innodb)如何避免幻读幻读Phantom RowsThe 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 ...
mysql 幻读 mvcc_MVCC 能解决幻读吗？
weixin_29628611的博客
01-19 777
MySQL通过MVCC(解决读写并发问题)和间隙锁(解决写写并发问题)来解决幻读MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”(REPEATABLE READ)。未提交读(READ UNCOMMITTED)。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据(脏读)。提交读(READ COMMITTED)。本事务读取到的是最新的数据(其他事务提交后...
数据库mvcc可重复读下无法彻底解决幻读
qq_36642340的博客
06-17 4573
 先看看初始表数据：使用2个cmd模拟两端，分别开启事务一端修改数据后提交成功另一端查看数据未发生更改再执行更新操作price和deposit同时更改了，产生了幻读这是由于mysql的快照读和当前读机制引起的。...
大厂基本功 | MySQL 三大日志 ( binlog、redo log 和 undo log ) 的作用？
langjiaohjiopji的博客
11-06 1202
MySQL日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志binlog（归档日志）和事务日志redo log（重做日志）和undo log（回滚日志）。今天就来聊聊redo log（重做日志）、binlog（归档日志）、两阶段提交、undo log（回滚日志）。MySQL InnoDB 引擎使用redo log(重做日志)保证事务的持久性，使用undo log(回滚日志)来保证事务的原子性。MySQL数据库的数据备份、主备、主主、主从都离不开。
mysql 配置文件 my.cnf 增加 lower_case_table_names = 1 服务启动不了
最新发布
BillKu的博客
11-10 160
只是简单的增加一行配置信息：lower_case_table_names = 1。
ReactPress 安装指南：从 MySQL 安装到项目启动
小牛itbull的博客
11-10 394
ReactPress 是一个基于 React 的开源发布平台，适用于搭建博客、网站或内容管理系统（CMS）。本文将详细介绍如何安装 ReactPress，包括 MySQL 数据库的安装、ReactPress 项目的配置与启动，并特别指出使用 pnpm 而不是 yarn 作为包管理工具。
mvcc怎么解决幻读
08-25
MVCC（Multi-Version Concurrency Control）是一种用于解决并发事务中幻读的机制。在MVCC中，每个事务都有一个唯一的事务ID和时间戳。当一个事务开始时，它会创建一个事务快照，该快照包含了在该事务开始之前的数据库状态。如果其他事务在这个事务快照创建后进行了修改，那么这些修改将在事务快照中不可见。 具体来说，MVCC通过保存数据的旧版本来实现。当一个事务开始时，它只能看到在该事务开始之前已经提交的其他事务的修改。如果在事务的执行过程中有新的事务提交了修改，那么这些新的修改对于当前事务是不可见的。 因此，MVCC可以解决幻读问题。幻读是指在同一个事务中，当多次执行相同的查询时，可能会得到不同的结果集，因为其他事务已经插入或删除了符合查询条件的数据。通过MVCC的机制，事务在开始时就创建了一个快照，这个快照包含了在事务开始之前的数据库状态。因此，在整个事务的执行过程中，事务只能看到在事务开始之前已经提交的其他事务的修改，而无法看到其他事务的插入或删除操作。 总之，MVCC通过保存旧版本的数据来防止幻读的发生。每个事务在开始时都会创建一个事务快照，该快照包含了在事务开始之前已经提交的其他事务的修改。这样，事务在执行过程中只能看到在事务开始之前已经提交的数据，从而避免了幻读的问题。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [mysql MVCC产生幻读原因及解决方式](https://blog.csdn.net/weixin_44145478/article/details/119910868)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] - *2* [详解MySQL是如何解决幻读的](https://download.csdn.net/download/weixin_38730767/13699659)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] - *3* [一文搞懂MySQL的MVCC原理及如何解决幻读](https://blog.csdn.net/weixin_43477531/article/details/121963884)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 33.333333333333336%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值