MySQL:图解MVCC到底能不能解决幻读问题?

背景

大家对MVCC能不能解决幻读问题各持己见,都有一套自己的说辞;最骚的是有些面试官就认为它不能,你说能他就认为你说的不对。那么到底能不能呢?我们今天从各个方面去深度分析一把:什么情况下能,什么情况不能。

验证

我们的验证基于MySQL InnoDB引擎的默认事务隔离级别REPEATABLE-READ
查询事务隔离级别的方式:

mysql> show variables like '%isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)

一、前戏

开始之前,我们先把自动提交事务给关了。
(1)查询是否开启了自动提交事务:

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

从代码来看,MySQL是默认开始自动提交事务的。

(2)关闭自动提交事务:

  • 值为 0 和值为 OFF:关闭事务自动提交。如果关闭自动提交,用户将会一直处于某个事务中,只有提交或回滚后才会结束当前事务,重新开始一个新事务。
  • 值为 1 和值为 ON:开启事务自动提交。如果开启自动提交,则每执行一条 SQL 语句,事务都会提交一次。
mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

二、创建数据库和表结构

(1)创建test数据库:

create database test;

(2)创建person表:

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

(3)基础数据准备:

insert into person(id, name, age) values(1, "a", 18);
insert into person(id, name, age) values(2, "b", 19);
insert into person(id, name, age) values(5, "e", 19);

三、图解幻读问题流程

我们开启两个MySQL窗口:左边的是事务A,右边的是事务B。
在这里插入图片描述
(1)先分别开启事务,然后执行一次快照读操作
在这里插入图片描述
(2)事务B中插入一条记录,但不提交事务;事务A、事务B中再分别执行一次读操作
在这里插入图片描述
此时我们发现,事务B中插入一条数据,事务A中并看不到这条数据,因为在可重复读事务隔离级别下,数据快照只在事务开始的时候创建。也就是事务A执行的还是快照读事务B此时因为执行了inset操作,所以是针对id=3这行数据是当前读,本质上还是ReadView的可见性规则决定。

(3)我们在事务A中修改ID为1的数据,然后再执行一次读操作
上面我们发现当执行当前读之后,事务中可以获取到最新的数据。那我们在事务A中更新ID为1的记录之后,是不是也可以看到在事务B(未提交)中新插入的那条ID为3的记录?
在这里插入图片描述
咦,不对呀,还是看不到ID为3的那条数据。什么情况!!!
其实我们可以发现第(2)步中事务B的当前读是针对ID=3的那条记录;而在当前步骤中,事务A的当前读是针对id=1的记录(ReadView可见性规则决定),对其他记录依旧是快照读,即此处依旧是快照读。所以我们看不到ID=3的那条记录,即不会出现幻读问题

那么问题来了:怎么会出现幻读呢?
(4)我们在事务A修改age为19的数据,然后再执行读操作
在这里插入图片描述

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

咦,怎么更新不了啊。我看看一下报错,它的意思是我们获取不到锁。因为事务B还没有commit,锁被它占用了。我们把事务B commit即可。

(5)提交事务B,再在事务中修改age为19的数据,接着再执行读操作
在这里插入图片描述
因为在事务B(已提交)中插入记录的age为19,所以我们在事务A中更新完age为19的记录,会针对age为19的数据进行当前读。此时我们在事务A中可以看到ID=3这条数据,也就出现了幻读问题。

(6)事务A提交,再执行查询操作
在这里插入图片描述
两个事务结束。

四、命令时间轴

时间事务1事务2
begin;begin;
T1select * from person;3个结果
T2insert into person(id, name, age) values(3,‘saint’,19);commit;
T3select * from person;3个结果
T4update person set name=‘aaaa’ where age =19;此时看到影响的行数为3
T5commit;
T6select * from pereson;4个结果

结论

如果事务中都是用快照读,那么不会产生幻读的问题;
但是快照读和当前读一起使用的时候就会产生幻读。

幻读的解决方案

(1)使用串行化(SERIERLIZED)事务隔离级别

set session transaction isolation level serializable;

在这里插入图片描述

(2)采用间隙锁的机制
即select时加锁:select … for update;

时间事务1事务2
begin;begin;
T1select * from person where age =19 for update;2个结果
T2insert into person(id, name, age) values(3,‘saint’,19);
此时会阻塞等待锁
T3select * from person;2个结果

设置隔离级别

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注意:默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果你使用GLOBAL关键字,语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别(甚至在事务的中间),或者为下一个事务设置隔离级别。

查询全局和会话事务隔离级别:

  • select @@global.transaction_isolation;
  • select @@session.transaction_isolation;
  • 4
    点赞
  • 9
    收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:Age of Ai 设计师:meimeiellie 返回首页
评论 1

打赏作者

秃秃爱健身

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值