快照读与加锁读 - MySQL 8.0官方文档笔记（三）

文档版本：8.0
来源：Consistent Nonlocking Reads 、Locking Reads
上一篇：事务隔离级别

本篇主要介绍InnoDB的快照读与加锁读。
快照读严格来说应该翻译为一致的无锁读（Consistent Nonlocking Reads），但国内一般都称作快照读。在之前的篇章中提到的普通读、无锁读和一致读等等，实际上讲的都是快照读的概念。

快照读

快照读，指的是InnoDB使用多版本机制，为一次查询提供数据库在特定时间点的快照。查询可以看到时间点之前已提交事务产生的变更，看不到未提交或时间点之后提交的。但有个特例，同一事物内快照生成之前产生的变更，也是可以看到的。这个特性会致使这样一种奇怪的情形：如果你更新了表的一些行，SELECT语句可以看到你做出的变更，但也可能看到某些行的老版本。如果其它事务也更新了这个表的一些行，你所看到的表的状态可能从未在数据库中存在过。

如果事务的隔离级别是可重复读（默认级别），同一事务内的所有快照读都会读取事务内第一次快照读时建立的快照。通过提交当前事物和发起新的查询，可以刷新快照。

而在读已提交级别下，每次快照读都会建立最新快照。

在读已提交和可重复读级别下，InnoDB会将SELECT语句全部转换为快照读。快照读不会给它读取的表上任何锁，因此在快照读执行的同时，其它事务可以对表进行任意修改。
假设你在默认的可重复读级别下，当你发起一次快照读（也就是普通的SELECT语句），InnoDB会给你的事务分配一个时间点：即记录查询看到数据库数据的时间点。如果其它事务在这个时间点之后删除了一行并提交，你的事务将不会感知到行被删除，插入和更新同理。

注意

数据库快照机制会应用在事务中的SELECT语句，但不会应用于DML语句。如果你插入或修改了某些行然后提交，同时另一个可重复读事务发起的DELETE或UPDATE语句，可以影响到这些刚刚提交的行，即使不能查询到它们。如果一个事务对另一个事务提交的行做更新或删除操作，这些本来不可见的变更将对前者可见。例如，你可能会遇到如下场景：

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- 返回 0：没有匹配记录。
DELETE FROM t1 WHERE c1 = 'xyz';
-- 将删除其它事务最近提交的若干行。

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- 返回 0：没有匹配记录。
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- 影响 10 行：另一个事务刚刚提交了10条值为‘abc’的记录。
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- 返回 10: 这个事务现在可以看到它刚刚修改的行。

你可以通过提交事务并执行SELECT或START TRANSACTION WITH CONSISTENT SNAPSHOT语句来刷新快照时间点。

上述的这些机制被称作多版本并发控制。

在下面的例子中，要想会话A看到会话B插入的行，必须让会话A和会话B都提交事务，从而使快照时间点刷新到B提交以后。

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
时间线
|          SELECT * FROM t;
|          空集
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           空集
                                  COMMIT;

           SELECT * FROM t;
           空集

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

如果你想保证看到数据库的最新状态，使用读已提交级别，亦或发起加锁读：

SELECT * FROM t FOR SHARE;

在读已提交级别下，同一事物内的每一次快照读都将设置自己的快照。而使用FOR SHARE语句，取而代之是产生一次加锁读：SELECT语句将阻塞直至事务获得到最新的行记录。

注意：FOR SHARE语句并不会刷新可重复读事务的快照时间点。

快照读对特定的DDL语句无效：

• 快照读对DROP TABLE无效，因为MySQL不能使用一张被释放的表，InnoDB已经销毁了表。
• 快照读对进行了拷贝-删除操作的ALTER TABLE无效。当重新发起一次快照读时，事务将看不到新表中的行因为在快照生成时这些行还不存在。此时，事务会返回一个错误：ER_TABLE_DEF_CHANGED，“表定义已改变，请重试事务”。

对于ALTER TABLE操作，MySQL提供三种算法：

• COPY：将在原表的拷贝中进行，随后表数据将从原表中逐行拷贝。期间不允许DML语句并行。
• INPLACE：不进行拷贝操作，但可能会原地重建表。在准备与执行阶段，ALTER操作可能会获取表的元数据独占锁。期间一般允许DML语句并行。
• INSTANT：只会修改数据字典中的元数据。在准备与执行阶段不会获取表的元数据独占锁，表数据也不会被影响，操作是瞬时的，允许DML语句并行。

当ALTER语句不支持INSTANT和INPLACE时，MySQL才会采取COPY算法进行拷贝-删除，例如更改字段顺序、改变字段类型或添加一个主键等等。关于触发COPY的各类条件详见：Online DDL Operations

对于SELECT语句的变种，如未明确FOR UPDATE或FOR SHARE的INSERT INTO ... SELECT，UPDATE ... (SELECT) 和 CREATE TABLE ... SELECT语句：

• 默认情况下，InnoDB对这些语句使用更强的锁，而SELECT部分则表现得像读已提交级别，即每一次快照读都会设置最新的快照，即使在同一事务内。
• 若要在这种情况下进行无锁读，须将事务设置为读未提交或读已提交，以避免读行时上锁。

加锁读

如果你在一个事务内先查询数据，再插入或更新与之相关联的数据，普通的SELECT语句不会给予足够的安全性保证。其他事务可以更新或删除你刚刚查询的行。InnoDB支持两种类型的加锁读来提供额外的安全性保证：

• SELECT ... FOR SHARE

  对查询到的行施加共享锁。其它事务可以读取这些行，但在你的事务提交前不能修改它们。如果其它事务事先修改了这些行而没有提交，你的查询将阻塞直至其它事务提交，随后获取到最新的值。

  注意

  SELECT ... FOR SHARE是SELECT ... LOCK IN SHARE MODE的替代语法，但LOCK IN SHARE MODE仍保留下来作向后兼容。语句功能是一致的，但FOR SHARE支持OF table_name，NOWAIT和and SKIP LOCKED选项。详见Locking Read Concurrency with NOWAIT and SKIP LOCKED。

• SELECT ... FOR UPDATE

  对于扫描到的索引值，锁定其所在行和任何关联的索引项，这一点与执行UPDATE语句效果相同。其它事务如果对这些行进行更新、执行SELECT ... FOR SHARE或特定隔离级别下读取，将阻塞。如果对快照视图中存在的行加锁，快照读会忽略这些锁。（老版本的行记录不能被锁定，因为它们是通过将行拷贝到内存中并执行回滚日志来重建的。）

  SELECT ... FOR UPDATE需要SELECT权限，以及DELETE，LOCK TABLES或UPDATE的至少一种权限。

  无论在单表还是多表中，上述两种语句在处理树形结构与图形结构的数据时非常有用。它们能在遍历图边界或穷举树分支的同时，保留了回退与改变“结点”值的权利。

  FOR SHARE和FOR UPDATE查询所产生的锁将在事务提交或回滚后释放。

  注意

  加锁读只有在自动提交关闭的情况下才可用。（要么使用START TRANSACTION开启事务，要么设置autocommit为0。）

声明在外部语句的加锁读不会锁定嵌套子查询中的行，除非加锁读也声明在子查询中。例如，下列语句不会锁定t2表中的行：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

若要锁定t2表中的行，在子查询中声明加锁读：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

加锁读案例

假设你想在child表插入一行，并且确保child行在parent表有一个父行。你的应用代码可以在这个操作序列中的确保参照完整性。

首先，使用快照读查询PARENT表来验证父行是否存在，然而你能安全地将子行插入CHILD表吗？不行，因为其它事务可以在你执行SELECT和INSERT操作之间删除该父行，而你无法感知到。

为了避免这一可能发生的问题，发起带FOR SHARE的SELECT语句：

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

在FOR SHARE查询返回了父行‘Jones’后，你就可以安全地将子行插入到CHILD表并提交了。任何尝试获取PARENT表中可用行的独占锁的事务都会等待，直到你的事务结束，同时也代表每个表中的数据都处于一致状态。

举另一个例子，在CHILD_NODES表中设有一整形计数字段，用于给每个插入到CHILD表的子行赋一个唯一标识。在这种情况下不要使用快照读或共享锁来读取当前计数器的值，因为两个数据库用户可能看到计数器的同一个值，并将在CHILD表插入相同标识值的行，于是会发生重复键错误。

在这种情况下，FOR SHARE不是一个好的解决方案，如果两个事务同时读取到相同的计数值，至少会有一个事务在试图更新计数器时因死锁而终止。

如何实现对计数器的读取和迭代？先使用FOR UPDATE对计数器发起加锁读，然后迭代计数器。例如：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE语句读取最新的可用数据，为读取到的行设置独占锁。因此就可以在随后的UPDATE中匹配到的行上设置相同的锁。

上面的案例只是为了说明SELECT ... FOR UPDATE如何工作。在MySQL中，生成唯一标识的工作可以通过一条语句完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

其中SELECT语句只是为了获得标识信息（与当前的数据库连接相关联），不会查询任何表。

使用NOWAIT和SKIP LOCKED调整加锁读并发性

如果事务锁住一行数据，其它事务对同一行发起SELECT ... FOR UPDATE或SELECT ... FOR SHARE查询将必须等待锁被释放。这一特性防止了那些查询出来并将被更新的行被其它事务更新或者删除。然而有时你想要查询行被上锁时语句立即返回，或者可以接受结果集中不包含被上锁的行时，就没有必要等待行锁被释放。

通过在SELECT ... FOR UPDATE或SELECT ... FOR SHARE中设置NOWAIT与SKIP LOCKED选项，可以避免不必要的锁等待。

• NOWAIT

  使用了`NOWAIT`的加锁读将不会等待获取行锁。查询立即执行，在行被上锁时返回一个错误。

• SKIP LOCKED

  使用了SKIP LOCKED的加锁读也不会等待获取行锁。查询立即执行，从结果集中剔除被上锁的行。

  注意

  跳过加锁行的查询将返回一个非一致性的数据视图。因此SKIP LOCKED不适合常规事务场景。但可以用于在多个事务访问队列类型的表时避免锁竞争。

NOWAIT和SKIP LOCKED只能用于行级锁。

在复制语句中使用NOWAIT和SKIP LOCKED是不安全的。

下面演示NOWAIT和SKIP LOCKED如何使用。会话1开启了一个事务并获取了一行行锁。会话2在同一行发起附带NOWAIT选项的加锁读，因为请求行被会话1锁住，加锁读立刻返回了错误。会话3发起附带SKIP LOCKED的加锁读，则返回了不包含会话1锁住行的结果集。

# 会话 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# 会话 2:

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# 会话 3:

mysql> START TRANSACTION;

mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+