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操作，所以是针对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）使用串行化(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;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。