认识 MySQL 的快照读、当前读

基本概念

普通读

普通读，也称快照读，英文名：Consistent Read。

普通读就是单纯的 select 语句，不包括下面这两类语句：

1 2	select ... for update select ... lock in share mode

当前读

当前读，也称锁定读，Locking Read。

当前读，读取的是最新版本，并且需要先获取对应记录的锁，如以下这些 SQL 类型：

select ... for update
select ... lock in share mode
update ...
delete ...
insert ...

实验操作

环境准备：

一个 MySQL Server，俩个 MySQL 客户端（笔者使用的是 windows mysql 客户端），一个数据库，一张数据库表，表中存储了些许测试数据。

数据库环境约定

MySQL 版本及默认事务隔离级别约定

MySQL Server 版本

查看 MySQL Server 版本：

1	select version();

MySQL Server 版本为 5.7.27

windows 环境安装MySQL 教程请移步至笔者这篇博文：MySQL 5.7 绿色安装(for windows)

事务隔离级别

查看事务隔离级别：

1	select @@tx_isolation;

InnoDB 存储引擎的事务隔离级别默认为：可重复读。

关闭自动提交

MySQL 客户端默认是开启自动提交的，需要手动关闭。

查看自动提交状态

方式1：

1	select @@autocommit;

1 表示开启自动提交，0 表示关闭自动提交

方式2：

1	show variables like 'autocommit'\G

ON 表示开启自动提交，OFF 表示关闭自动提交

关闭自动提交

方式1：

1	set autocommit=0;

方式2：

1	set autocommit=OFF;

创建数据库表及测试数据

执行如下 sql 创建数据库表及测试数据：

-- 清空已存在的 woodwhales_test_db 数据库
drop database if exists woodwhales_test_db;

-- 创建 woodwhales_test_db 数据库
create database woodwhales_test_db;

-- 使用 woodwhales_test_db 数据库
use woodwhales_db;

-- 创建 test 数据库表，存储引擎使用 innodb
CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `age` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 插入测试数据
begin; -- 开启事务
use woodwhales_test_db; -- 使用 woodwhales_test_db 数据库
insert into test_table(age) values (1),(2); -- 插入测试数据
commit; -- 提交事务

快照读-实验

步骤1

客户端1、客户端2分别开启事务；

步骤2

客户端1、客户端2分别查询测试数据库表；

此时，客户端1、客户端2在各自的事务中看到的库表数据是一样的。

步骤3

客户端2执行更新语句，但不执行 commit 或者 rollback 命令；

客户端2更新 id = 1 的数据：

1	update test_table set age = 10 where id = 1;

步骤4

客户端1、客户端2分别查询测试数据库表；

1	select * from test_table;

俩个客户端执行上述命令之后的结果如下图：

从上图结果可知：

客户端1在自己的事务中查询到的结果与客户端 2 在自己的事务中查询到结果不一致：

客户端1查询到的还是开启事务之前的版本
客户端2查询到的是自己操作数据之后的版本

出现读取数据不一致的现象，是因为客户端1的 sql 语句是一个快照读，读取的是事务开启时的库表快照。

读者可能会怀疑，为什么没有读取到客户端2操作后的数据，是不是因为客户端2还没有提交事务呢？

别忘了笔者在数据库环境约定中指明了数据库的隔离级别为可重复读，因此客户端2就算执行了提交事务操作，也不会影响客户端1在当前未提交事务中所读的数据，因为只要事务还没有提交，读取的就是事务开启时的库表快照。

实验证明：客户端2提交事务，并重新查询数据库表：

当前读-实验

防止俩个客户端有未提交的事务，影响实验结果。使用其中一个客户端更新数据为初始状态。并将俩个客户端都执行一下 commit 命令，保证俩个客户端开启事务是在“同一起跑线”上。

下述的前三个步骤与上小节的三个步骤一致。

步骤1

客户端1、客户端2分别开启事务；

步骤2

客户端1、客户端2分别查询测试数据库表；

步骤3

客户端2执行更新语句，但不执行 commit 或者 rollback 命令；

客户端2更新 id = 1 的数据：

1	update test_table set age = 10 where id = 1;

步骤4

客户端1使用当前读查询：

1	select * from test_table for update

客户端1执行上述命令之后，命令行窗口会处于阻塞状态，而客户端2执行上述命令无任何阻塞：

注意此时的客户端1不会无限阻塞，会阻塞一段时间之后，报出ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction错误。

InnoDB 存储引擎中innodb_lock_wait_timeout参数控制着锁等待时长，默认获取锁等待 50s，一旦数据库锁超过这个时间就会报错。
1
2
3
4
-- 查看锁等待超时时间，单位：秒
show global variables like 'innodb_lock_wait_timeout';
-- 设置锁等待超时时间，单位：秒
set global innodb_lock_wait_timeout=500;

由于上述步骤 4 中客户端1查询的是全表的当前最新数据记录，我们知道当前全表中的 id = 1 的数据是客户端2修改了并且没有提交事务，由于事务隔离级别的约束，所以客户端1只能阻塞等待。

步骤5

如果步骤 4 中的客户端1查询的不是客户端1已修改但未提交的记录，那么客户端1执行当前读语句是不会阻塞的：

步骤6

先明确当前读的概念，当前读表示强制读取数据的最新状态，并且这个数据状态一定是已事务提交后的数据状态。由于事务隔离级别决定了，不允许产生脏读，所以无论是快照读还是当前读都不可能读取到其他事务未提交的数据状态。

如果步骤 4 中的客户端1执行当前读之前，客户端2进行了事务提交，那么客户端1进行当前读的时候就会读取到数据的最新状态：

从上图可以看出，客户端1没有被阻塞，而是查询到了数据，但是数据和开启事务时的数据状态不一致，出现了幻读的情况。总结得出，在可重复读的事务隔离级别下，快照读和当前读混用可能会造成幻读。