不可重复读幻读的危害复现和MySQL的解决方案

置顶 bbq烤鸡

已于 2022-02-24 18:10:40 修改

阅读量2.7k

点赞数 10

分类专栏： Java mysql 文章标签： mysql java 数据库

于 2022-02-24 17:37:40 首次发布

By bbq烤鸡

本文链接：https://blog.csdn.net/qq_24054301/article/details/123086098

版权

Java 同时被 2 个专栏收录

17 篇文章 1 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

前言

事务的隔离级别分为读未提交RU、读已提交RC、可重复读取RR、序列化。概念不必多说，随处可搜，而危害很难搜到文章描述。主要原因还是主流数据库已经帮我们处理好了这些头痛的问题，并且业务上也很难有这种需求复现，所以会的以为大家都会，不会的搜遍全网也是一头雾水。正好有空讲这个问题总结一下，让有缘人有所参考。

问题引发的纠纷

当年正好是我在调优MySQL和大表分页的一段时间，延伸开来理解些东西，也是想从实际业务出发去理解事务问题，例如不可重复读和幻读的危害。本以为一搜就能找到，但是搜来搜去，花了大半天的功夫也没得到答案，很多答案也是废话连篇，搞得相当恼火。相当早的一个网红词汇描述叫做“砖家”来描述他们。
如下
在这里插入图片描述

请添加图片描述

点进头像一看，企业认证阿里。这。。。再扇自己两巴掌。
然后突然间我就顿悟了，给出了答案，后面的解决方案也是有误，隔了一个多月重新回复了。

解析

■ 事务一致性

上面这大佬跟和尚念经一样一直嘀咕事务一致性。他说的有错吗？没有错。那为什么这么让人讨厌呢？
因为事务通过原子性、隔离性、持久性来保证一致性。
什么意思呢？意思就是保证事务一致性是最终目标，它由aid来保证c，也就是说他在讲废话了。
他讲解错是因为违反了原则，危害就是违反原则。本末倒置了，应该是会发生错误而制定了原则，而错在哪却讲不出来，揪着原则不放。

■ 一般人的理解误区

为什么很多人抓破脑袋也想不出不可重复读的危害呢？
原因就是被误导了
搜索不可重复读，基本都是类似下面这个描述

不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。

这个描述有错吗？没错。错在后半句这个描述只是一个子集。正确的描述应该为
不可重复读，是指在数据库访问中，一个事务范围内两个查询的相同记录却返回了不同数据。
这样是不是就豁然开朗了，大家理解成了用一条SQL语句执行两次，而真正的业务想必不太可能会这么搞，所以一时间想不通危害。而不同的语句查到不同版本的同一条记录这是非常有可能发生的。

危害复现

沿用当年的例子，详细分析。

■ 不可重复读

业务例子

银行做活动。事务a查询某地区余额1000以下送一包餐巾纸，生成名单。事务b小明余额500，存了1000，变成1500。事务a查询1000到2000送一桶油，生成名单，这样小明收到了2个礼品。
在这里插入图片描述

后果

两份名单都有小明，小明很开心，偷偷地告诉了亲朋好友一起薅羊毛，行长总觉得哪里不对劲哭晕在厕所。

■ 幻读的业务例子

业务例子

银行做活动。事务a查询某地区余额1000以下送一包餐巾纸，生成名单。事务b新增了一个新用户小明，并存款500。事务a查询1000到2000送一桶油，生成名单，这样小明没有收到礼物，而同时注册的小李存了1500却收到了一桶油。
在这里插入图片描述

后果

小李发了个盆友圈：今天办了张卡，送了桶油，真开心。
小明：？？？退卡，我要换银行。

例子业务可能很难触发，但肯定是有概率的，以上就是不可重复读和幻读的危害。

mysql解决方案

不可重复度加入行锁;查出1000以下的名单锁住，小明存1000的操作就得等送油的名单生成，这就保证了小明不会收到两个礼品；幻读行锁无法解决，就得表锁，让新增用户等事务a结束才执行。

这是我当时最早的一个回复，非MySQL方案。有毛病吗？没毛病，错误不会发生了。但是性能大打折扣，动不动就悲观锁，用户要砸银行了。数据库要是这么搞还有人敢用吗。所以现在主流的MySQL，InnoDB引擎引入了MVCC版本号机制和Next key lock来解决这些问题。

解决不可重复读

MVCC，概念不赘述，到处都能找到。用案例来详细讲解一下它是怎么解决不可重复读的。

■ 测试MySQL默认情况下的业务结果

① 建表
建立account表，只有两个字段，一个是id，一个是余额。

CREATE TABLE `account` (
`id`  varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
`balance`  decimal(16,0) NULL DEFAULT NULL 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
ROW_FORMAT=DYNAMIC
;

两条初始数据

INSERT INTO `account` (`id`, `balance`) VALUES ('1', '500');
INSERT INTO `account` (`id`, `balance`) VALUES ('2', '1600');

② Navicat配置环境

SHOW VARIABLES LIKE 'autocommit';

默认肯定是ON，开启事务自动提交的，我们把它关闭。
打开两个session，分别执行

set autocommit = 0

然后会变成OFF。

③ 开始模拟
A_session：

SELECT * FROM `account` a WHERE a.balance < 1000

result

> 1	500

B_session：

UPDATE account SET balance=balance+1000 WHERE id = 1

COMMIT

A_session：

SELECT * FROM `account` a WHERE a.balance < 2000 and a.balance >=1000

result

> 2	1600

是不是很神奇，或者A_session查询第一条SQL，也是不变的。再或者你在B_session结束后再开一个C_session，是可以看到最新数据的。说明不存在不可重复读问题，并且也没有任何锁阻塞。

■ 图解原理

在这里插入图片描述
如图所示，我演示了在RR级别下的情况，重点讲几个。
RR级别下readview只在事务第一条SQL开始前生成，而RC每次执行SQL都生成，所以会出现不可重复读的现象。
网上讲解的MCVV机制基本都是查询不满足索引的情况，而查询满足索引的时候是不一样的。
事务update同时，会修改索引，索引的那条记录会存在多个版本，什么时候删除，我不确定（推测是同undolog一同删除的，purge线程的操作）。
聚簇索引借助删除标志、生成事务id、历史版本指针+MVCC来判断可见性。
二级索引借助删除标志、最新事务id、根据id回归聚簇索引那一套来判断可见性。

解决幻读（快照读）

■ 测试MySQL默认情况下的业务结果

①② 同上

③ 开始模拟
A_session：

SELECT * FROM `account` a WHERE a.balance < 1000

result

> 1	500

B_session：

insert into account value('10', '1500');
insert into account value('11', '500');

COMMIT

A_session：

SELECT * FROM `account` a WHERE a.balance < 2000 and a.balance >=1000

result

> 2	1600

???更神奇了，结果同update的案例一样，不变。说明RR模式下也不存在幻读问题，并且也没有任何锁阻塞。

■ 原理

同样是MVCC版本号机制，你发现上面那张图解在这同样可用，一样的。

■ 结论

所以我们可以下结论，在MySQL的innoDb引擎下，使用MVCC版本号机制解决了不可重复读、幻读（快照读）问题。

解决幻读（当前读）

正常的业务查询读没问题是不会产生幻读的，接下来讲解如何解决当前读。如果测试不正常，建议检查autocommit是否变回去了。

■ 测试MySQL默认情况下的业务结果（单列索引）

① 建表
建立account表，三个字段，一个是id，一个是余额，一个地址。

CREATE TABLE `account`  (
  `id` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `balance` decimal(16, 0) NULL DEFAULT NULL,
  `area` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `balance`(`balance`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

四条初始数据

INSERT INTO `bbq`.`account` (`id`, `balance`, `area`) VALUES ('1', 500, '2');
INSERT INTO `bbq`.`account` (`id`, `balance`, `area`) VALUES ('2', 1000, '3');
INSERT INTO `bbq`.`account` (`id`, `balance`, `area`) VALUES ('3', 1500, '1');
INSERT INTO `bbq`.`account` (`id`, `balance`, `area`) VALUES ('4', 2000, '3');

② 同上
③ 开始模拟单列索引
索引 balance
A_session：

SELECT * FROM `account` a WHERE a.balance > 1200 and a.balance < 1300 for UPDATE

result

> null

B_session：

insert into account value('11', 1000,'1') success
insert into account value('12', 1001,'1') wait
insert into account value('12', 1200,'1') wait
insert into account value('12', 1300,'1') wait
insert into account value('12', 1500,'1') wait
insert into account value('12', 1501,'1') success

多次测试得出结论：
等值查询，有记录，锁行，没记录等同范围查询；
范围查询，锁比范围更大的两条记录的间隙+右边记录的行锁
锁的范围为
（(<1200的纪录,1200]，(1300,>1300的记录]】

■ 测试MySQL默认情况下的业务结果（无索引）

删除 balance 索引
以上插入全部wait，说明锁是借助索引实现的，无索引就会锁聚簇，相当于锁表。

■ 测试MySQL默认情况下的业务结果（组合索引）

修改索引为(“area”,“balance”)

SELECT * FROM `account` a WHERE a.balance > 1200 and a.balance < 1300 and a.area="1" for UPDATE

索引走area+第二列balance的范围，锁area=“1”情况下的next key lock(同第三点)，就是多了一级而已。

组合索引树结构图

在这里插入图片描述

■ Insert改为update

例如

UPDATE account SET balance=balance+1000 WHERE id = 1 wait
UPDATE account SET balance=balance+1000 WHERE id = 2 success
UPDATE account SET balance=balance+1000 WHERE id = 3 wait
UPDATE account SET balance=balance+1000 WHERE id = 4 success

会发现1 、3阻塞，原理同上一致，锁了索引，不论插入还是更新都进不去。

MySQL RR异常事务例子

到了这会发现MySQL 的这套机制太牛逼了，用非常低的开销解决了这么多的问题。问题来了MVCC+Next key lock 真的能解决一切吗？答案肯定是否定的，同样以业务例子来展示。

业务例子

前景回顾，由于程序员的失误，认为不可重复读、幻读不影响业务，为了提高性能，把事务隔离级别设置成了RC，造成了行长的损失，被行长祭天了。双倍工资又招了一个有丰富经验的程序员，他懂得MVCC和Next key lock，一来马上就看出问题所在，修正了事务隔离级别，行长很满意，并提了个需求：
“由于上个程序员的失误，银行的油不多了，我们得再设置一个条件，送油得扣除用户的积分500，积分不够的即使余额够也是送餐巾纸”。
大佬程序员拍拍胸脯打包票，小意思，稍微修改了下SQL，测都不测试就提交了。

■ 测试RR业务结果

① 数据准备
数据和之前一样，增加了一个积分字段，并且初始积分为1000。

INSERT INTO `bbq`.`account` (`id`, `balance`, `integral`) VALUES ('1', '500', '1000');
INSERT INTO `bbq`.`account` (`id`, `balance`, `integral`) VALUES ('2', '1600', '1000');

② 开始模拟
A_session：
查询送纸名单

SELECT * FROM `account` a WHERE a.balance < 1000 or (a.balance >=1000 and a.integral < 500)

result

> 1	500 1000

B_session：
小明又来薅羊毛

UPDATE account SET balance=balance+1000 WHERE id = 1

A_session：
查询送油名单

SELECT * FROM `account` a WHERE a.balance < 2000 and a.balance >=1000 and a.integral >= 500

result

> 2	1600 1000

扣除积分

UPDATE account SET integral=integral-500 WHERE balance < 2000 and balance >=1000 and integral >= 500

结果，名单生成正常了，但是送纸的小明也被扣了500积分。
投诉电话纷纷打来，行长被气疯了，连夜叫大佬起来看是什么情况。

③ 修改
大佬懵逼了，由于发现的早，赶紧回滚了这些操作。不过行长又在催促——半个小时内必须生成名单不然滚蛋。大佬吓得手足无措，把送油名单和扣除积分的顺序做了个调换提交看看。完蛋了。。。名单乱七八糟，小明的好多盆友成功薅到羊毛，大佬赶紧提桶跑路。

④ 结论
当快照读和当前读并存的时候，要格外小心事务异常。事务的第一条sql应该执行当前读才能避免异常。

最后

不可重复读幻读的危害极大，好在MySQL默认RR级别帮我们解决了这些问题。我们需要注意的就是在业务中避免同一个事务执行完快照读又执行当前读的操作。

bbq烤鸡

关注

10
点赞
踩
25

收藏

觉得还不错? 一键收藏
7
评论
不可重复读幻读的危害复现和MySQL的解决方案

不可重复读幻读的危害复现和MySQL的解决方案)## 前言事务的隔离级别分为**读未提交RU**、**读已提交RC**、**可重复读取RR**、**序列化**。概念不必多说，随处可搜，而危害很难搜到文章描述。主要原因还是主流数据库已经帮我们处理好了这些头痛的问题，并且业务上也很难有这种需求复现，所以会的以为大家都会，不会的搜遍全网也是一头雾水。正好有空讲这个问题总结一下，让有缘人有所参考。
复制链接

扫一扫

专栏目录