MySQL RR 与 幻读问题(实验 + 案例)
一、前言
本文围绕这三个话题展开学习 RR
如何解决幻读?
MVCC
原理- 实验:
RR
与 幻读 - 案例:死锁
先来回顾下 MySQL
中 InnoDB
支持的四种事务隔离 和 并发事务所带来的一些问题:
- 读未提交:能读到一个事务的中间过程,违背了
ACID
特性,存在脏读的问题,基本不会用到。 - 读提交:表示如果其他事务已经提交,那么就可以看到。在生产环境中用的并不多。
- 可重复读:默认级别,使用最多的一种。其特点是有
Gap
锁(间隙锁)。 - 可串行化:所有的实现都是通过锁来实现的。
并发事务处理也会带来一些问题:脏读、不可重复读、幻读
-
脏读:一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态。
-
不可重复读:一个事务按相同查询条件前后两次读取,读出的数据不一致(修改、删除)。
-
幻读:一个事务内按相同的查询条件重新查询数据,却发现其他事务插入了满足其查询条件的新数据。
本文脉络梳理: RR
为了更快并发,引入 MVCC
,但有幻读的可能,为解决幻读,引入 Gap
锁,Gap
可能造成死锁。
二、MVCC
原理
MVCC
(多版本控制): 指数据库中为了实现高并发的数据访问,对数据进行多版本处理,并通过事务的可见性来保证事务能看到自己应该看到的数据版本。
MVCC
最大的好处是读不加锁,读写不冲突。- 在
OLTP (On-Line Transaction Processing)
应用中,读写不冲突很重要,几乎所有RDBMS
都支持MVCC
。
注意:MVCC
只在 读提交RC
和 可重复读RR
两种隔离级别下工作。
注意:MVCC
只在 读提交RC
和 可重复读RR
两种隔离级别下工作。
注意:MVCC
只在 读提交RC
和 可重复读RR
两种隔离级别下工作。
(1)MVCC
多版本实现
MySQL
实现 MVCC
机制的时候,是基于 undo log
多版本链条 + ReadView
机制。
undo log
多版本链: 每一次对数据库的修改,都会在undo log
日志中记录当前修改记录的事务号及修改前数据状态的存储地址(即ROLL_PTR
),以便在必要的时候可以回滚到老的数据版本。ReadView
机制: 在多版链的基础上,控制事务读取的可见性。(主要区别是:RC
和RR
)
这里不着重探究原理,但要有大概的概念:undo log
多版本链 和 ReadView
机制。
针对 undo log
多版本链,举个栗子:
- 一个读事务查询到当前记录,而最新的事务还未提交。
- 根据原子性,读事务看不到最新数据,但可以去回滚段中找到老版本的数据,这样就生成了多个版本。
针对 ReadView
机制: 基于 undo log
多版本链实现,不同事务隔离有不同处理 :
RC
级别的事务: 可见性比较高,它可以看到已提交的事务的所有修改。RR
级别的事务: 一个读事务中,不管其他事务对这些数据做了什么修改,以及是否提交,只要自己不提交,查询的数据结果就不会变。
这是如何做到的呢?
-
RC
读提交: 每一条读操作语句都会获取一次ReadView
,每次更新之后,都会获取数据库中最新的事务提交状态,也就可以看到最新提交的事务了,即每条语句执行都会更新其可见性视图。 -
RR
可重复读: 开启事务时不会获取ReadView
,只有发起第一个快照读时才会获取ReadView
。- 如果使用当前读,都会获取新的
ReadView
,也能看到更新的数据。
- 如果使用当前读,都会获取新的
(2)快照读与当前读
在 MVCC
并发控制中,读操作 可以分为两类:
-
快照读:读取的是记录的可见版本(有可能是历史版本), 不用加锁 。
- 操作:简单的
SELECT
操作。
- 操作:简单的
-
当前读:读取的是记录的最新版本,并且当前读返回的记录,都会加锁,保证其他事务不会再并发修改这条记录。
- 操作:特殊读操作、新增/更新/删除操作。
-- 对应 SQL 如下: -- 1. 特殊读操作 SELECT ... FOR UPDATE SELECT ... LOCK IN SHARE MODE -- 共享锁 -- 2. 新增:INSERT -- 3. 更新:UPDATE -- 4. 删除:DELETE
结合 ReadView
机制来区分:快照读 和 当前读:
-
快照读: 在一个事务里,只有发起第一个快照读时才会获取
ReadView
,之后的读操作不会再获取。 -
当前读: 每次读操作都会获取
ReadView
。
三、实验:RR 与幻读
面试题:在 RR
事务隔离级别下,事务A查询一条数据,事务B新增一条数据,事务A能看到事务B的数据嘛?
这个问题比较模糊,但大致考察点我们知晓是 RR
与 幻读,可以将问题分为两类:
- 什么情况下,
RR
产生幻读?(能看到数据)- 答案:当前读(
SELECT..FOR UDPDATE
、SELECT ... LOCK IN SHARE MODE
)
- 答案:当前读(
- 什么情况下,
RR
解决幻读?(不能看到数据)- 答案:加锁、快照读
注意: 不可重复读 重点在于 UPDATA
和 DELETE
,而幻读的重点在于 INSERT
。
它们之间最大的区别:是如何通过锁机制来解决它们产生的问题。
这里说的锁只是使用悲观锁机制。
再来回顾下:幻读
-- 举个栗子:有这样一个查询 SQL
SELECT * FROM user WHERE id < 10;
- 在同一个事务下,T1时刻查询出来 4 条数据,T2时刻查询出来 8 条数据。这就产生了幻读。
- 在同一个事务下,T1时刻查询出来 8 条数据,T2时刻查询出来 4 条数据。这就产生了幻读。
实验准备如下: 动手实践起来
show variables like 'transaction_isolation'; -- 事务隔离级别 RR
select version(); -- 版本 8.0.16
show variables like '%storage_engine%'; -- 引擎 InnoDB
-- 1. 手动开启事务提交
begin; -- 开始事务
commit; -- 提交事务
-- 2. 创建表
CREATE TABLE IF NOT EXISTS `student` (
`id` INT NOT NULL COMMENT '主键 id',
`name` VARCHAR(50) NOT NULL COMMENT '名字',
`age` TINYINT NOT NULL COMMENT '年龄',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '学生表';
-- 3. 新增数据用于实验
INSERT INTO student (id, name, age) VALUES (5, 'kunkun', 14);
INSERT INTO student (id, name, age) VALUES (30, 'ikun', 18);
(1)RR
产生幻读
实验如下: 测试当前读
- 实验一:先
SELECT
,再SELECT ... FOR UPDATE
- 实验二:先
SELECT
,再UPDATE
(不会产生幻读)
实验一:先 SELECT
,再 SELECT ... FOR UPDATE
-- 事务A:
BEGIN;
SELECT * FROM student WHERE id < 30;
SELECT * FROM student WHERE id < 30 FOR UPDATE; -- 等待事务B commit 后再执行
-- SELECT * FROM student WHERE id < 30 LOCK IN SHARE MODE;
COMMIT;
-- 事务B:
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, 'wulikun', 16);
COMMIT;
发生情况如下图所示:
实验记录如下图所示:
现象结论: 当使用当前读(SELECT ... FOR UPDATE
)会产生幻读。
- 同样使用
SELECT ... LOCK IN SHARE MODE;
会产生幻读。
实验二:先 SELECT
,再 UPDATE
-- 事务A:
BEGIN;
SELECT * FROM student WHERE id < 30;
UPDATE student SET name = 'zhiyin' WHERE id = 5; -- 等待事务B commit 后再执行
SELECT * FROM student WHERE id < 30;
COMMIT;
-- 事务B:
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, 'wulikun', 16);
COMMIT;
发生情况如下图所示:
实验记录如下图所示:
现象结论: 当前读(UPDATE
)不会产生幻读。同样 INSERT
/ DELETE
均不会。
(2)RR
解决幻读
实验如下:
- 实验一:快照读
- 实验二:加锁(更新不存在的记录)
- 实验三:加锁(
SELECT ... FOR UPDATE
)
实验一:快照读,普通 SELECT
-- 事务A:
BEGIN;
SELECT * FROM student;
SELECT * FROM student; -- 等待事务B commit 后再执行
COMMIT;
-- 事务B:
BEGIN;
INSERT INTO student (id, name, age) VALUES (20, 'wulikun', 16);
COMMIT;
发生情况如下图所示:
实验记录如下图所示:
现象结论: 在 RR
事务隔离级别下,只有快照读(SELECT
)不会出现幻读。没有当前读。
实验二:加锁 ,(更新不存在的记录)
在
RR
隔离级别下,事务 A 使用UPDATE
加锁,事务 B 无法在这之间插入新数据,这样事务 A在UPDATE
前后读的数据保持一致,避免了幻读。
-- 事务A:
BEGIN;
SELECT * FROM student;
UPDATE student SET name = 'wulikunkun' WHERE id = 18; -- 记录不存在,产生间隙锁 (5, 30)。
COMMIT;
-- 事务B:
BEGIN;
INSERT INTO student (id, name, age) VALUES (10, 'zhiyin', 16); -- 需要等待事务A结束。
COMMIT;
-- 事务C:
BEGIN;
INSERT INTO student (id, name, age) VALUES (40, 'zhiyin你太美', 32);
COMMIT;
-- 查询数据库中当前有哪些锁
SELECT INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
发生情况如下图所示:
实验记录如下图所示:
现象结论:
- 一开始先加 临键锁
Next-key lock
,锁范围为(5,30]
。 - 因为是唯一索引,且更新的记录不存在,临键锁退化成 间隙锁
Gap
,最终锁范围为(5,30)
。其余的记录不受影响。
实验三:加锁(SELECT ... FOR UPDATE
)
-- 事务A:
BEGIN;
SELECT * FROM student;
SELECT * FROM student WHERE id < 5 FOR UPDATE;
COMMIT;
-- 事务B:
BEGIN;
INSERT INTO student (id, name, age) VALUES (4, 'zhiyin', 4); -- 需要等待事务A结束。
COMMIT;
-- 事务C:
BEGIN;
INSERT INTO student (id, name, age) VALUES (5, 'zhiyin你太美', 32); -- 插入成功
COMMIT;
-- 查询数据库中当前有哪些锁
SELECT INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks;
发生情况如下图所示:
实验记录如下图所示:
现象结论:
- 先加 临键锁
Next-key lock
,锁范围为(-∞,5]
。 - 所以,
id < 5
和id = 5
的数据都插入不进去。
拓展:Gap
锁(间隙锁)
根据 官方文档 可知:
- 锁是加在索引上的。
- 记录锁: 行锁,只会锁定一条记录。
- 间隙锁 :是在索引记录之间的间隙上的锁,区间为前开后开
(,)
。 - 临键锁(
Next-Key Lock
): 由 记录锁 和 间隙锁Gap
组合起来。 - 加锁的基本单位是 临键锁,其加锁区间为前开后闭
(,]
。 - 索引上的等值查询,给唯一索引加锁的时候,如果满足条件,临键锁 退化为 行锁。
- 索引上的等值查询,给唯一索引加锁的时候,如果不满足条件,临键锁 退化为 间隙锁。 注意,非等值查询是不会优化的。
转载自:https://juejin.cn/post/7151795695073099812