MySQL：图解MVCC到底能不能解决幻读问题？

本文主要是介绍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操作，所以是当前读。

（3）我们在事务A中修改ID为1的数据，然后再执行一次读操作
上面我们发现当执行当前读之后，事务中可以获取到最新的数据。那我们在事务A中更新ID为1的记录之后，是不是也可以看到在事务B（未提交）中新插入的那条ID为3的记录？

咦，不对呀，还是看不到ID为3的那条数据。什么情况！！！
其实我们可以发现第(2)步中事务B的当前读是针对ID=3的那条记录；而在当前步骤中，事务A的当前读是针对id=1的记录，对其他记录依旧是快照读，即此处依旧是快照读。所以我们看不到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）使用串行化(SERIERLIZED)事务隔离级别

set session transaction isolation level serializable;

（2）采用间隙锁的机制
即select时加锁：select … for update;

设置隔离级别

用户可以用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;

这篇关于MySQL：图解MVCC到底能不能解决幻读问题？的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！