mysql中RR与幻读的问题怎么解决

这篇“MySQL中RR与幻读的问题怎么解决”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“mysql中RR与幻读的问题怎么解决”文章吧。

成都创新互联公司主营嫩江网站建设的网络公司,主营网站建设方案,app开发定制,嫩江h5微信小程序开发搭建,嫩江网站营销推广欢迎嫩江等地区企业咨询

一、前言

MVCC 原理
实验：RR 与幻读
案例：死锁

先来回顾下 MySQL中 InnoDB 支持的四种事务隔离和并发事务所带来的一些问题：

mysql中RR与幻读的问题怎么解决

读未提交：能读到一个事务的中间过程，违背了 ACID 特性，存在脏读的问题，基本不会用到。
读提交：表示如果其他事务已经提交，那么就可以看到。在生产环境中用的并不多。
可重复读：默认级别，使用最多的一种。其特点是有 Gap 锁（间隙锁）。
可串行化：所有的实现都是通过锁来实现的。

并发事务处理也会带来一些问题：脏读、不可重复读、幻读

脏读：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态。
不可重复读：一个事务按相同查询条件前后两次读取，读出的数据不一致（修改、删除）。
幻读：一个事务内按相同的查询条件重新查询数据，却发现其他事务插入了满足其查询条件的新数据。

本文脉络梳理： RR 为了更快并发，引入 MVCC，但有幻读的可能，为解决幻读，引入 Gap 锁，Gap 可能造成死锁。

二、MVCC 原理

MVCC（多版本控制）：指数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。

MVCC 最大的好处是读不加锁，读写不冲突。

在 OLTP (On-Line Transaction Processing)应用中，读写不冲突很重要，几乎所有 RDBMS 都支持 MVCC。

注意：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的数据嘛？

mysql中RR与幻读的问题怎么解决

这个问题比较模糊，但大致考察点我们知晓是 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;

发生情况如下图所示：

mysql中RR与幻读的问题怎么解决

实验记录如下图所示：

mysql中RR与幻读的问题怎么解决

现象结论：当使用当前读（SELECT ... FOR UPDATE）会产生幻读。

同样使用 SELECT ... LOCK IN SHARE MODE; 会产生幻读。

mysql中RR与幻读的问题怎么解决

实验二：先 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;

发生情况如下图所示：

mysql中RR与幻读的问题怎么解决

实验记录如下图所示：

mysql中RR与幻读的问题怎么解决

现象结论：当前读（UPDATE）不会产生幻读。同样 INSERT / DELETE 均不会。

mysql中RR与幻读的问题怎么解决

（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;

发生情况如下图所示：

mysql中RR与幻读的问题怎么解决

实验记录如下图所示：

mysql中RR与幻读的问题怎么解决

现象结论：在 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;

发生情况如下图所示：

mysql中RR与幻读的问题怎么解决

实验记录如下图所示：

mysql中RR与幻读的问题怎么解决

现象结论：

一开始先加临键锁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;

发生情况如下图所示：

mysql中RR与幻读的问题怎么解决

实验记录如下图所示：

mysql中RR与幻读的问题怎么解决

现象结论：

先加临键锁Next-key lock，锁范围为 (-∞,5]。

所以，id < 5 和 id = 5 的数据都插入不进去。

拓展：Gap 锁（间隙锁）

根据官方文档可知：

锁是加在索引上的。
记录锁：行锁，只会锁定一条记录。
间隙锁：是在索引记录之间的间隙上的锁，区间为前开后开 (,)。
临键锁（Next-Key Lock）：由记录锁和间隙锁Gap 组合起来。
加锁的基本单位是临键锁，其加锁区间为前开后闭 (,]。
索引上的等值查询，给唯一索引加锁的时候，如果满足条件，临键锁退化为行锁。
索引上的等值查询，给唯一索引加锁的时候，如果不满足条件，临键锁退化为间隙锁。注意，非等值查询是不会优化的。

以上就是关于“mysql中RR与幻读的问题怎么解决”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注创新互联行业资讯频道。

分享名称：mysql中RR与幻读的问题怎么解决
网页链接：http://cqcxhl.cn/article/pcjhij.html

重庆分公司，新征程启航

mysql中RR与幻读的问题怎么解决

一、前言

二、MVCC 原理

三、实验：RR 与幻读

其他资讯