系列文章：

【Mysql-InnoDB 系列】InnoDB 架构

【Mysql-InnoDB 系列】锁

【Mysql-InnoDB 系列】事务模型

【Mysql-InnoDB 系列】关于一致读

一 概念

锁定读，是相对于一致（非锁定）读来说的。

当我们在同一个事务（T1）中先读数据，然后执行插入或更新相关数据时，普通的 SELECT 语句并不能给予足够的保护。其他事务也可能更新或删除我们在 T1 事务中查询的相同行。InnoDB 支持两种类型的锁定读，来提供额外的保护：

1、SELECT ... FOR SHARE

在所有读取的行上设置一个共享模式锁。其他会话可以读这些行，但在你的事务提交之前都不能进行修改。如果这些行中有被其他未提交的事务（T2）修改的记录，你的查询需要等到 T2 事务结束之后并使用最新的值。

注意：

SELECT ... FOR SHARE 是 SELECT ... LOCK IN SHARE MODE的替代，但LOCK IN SHARE MODE 保持向后兼容。这两个语句是等价的。不过，FOR SHARE 支持 OF table_name， NOWAIT和SKIP LOCKED选项。

MySQL 8.0.22 版本之前，SELECT ... FOR SHARE 需要 SELECT 权限，以及 DELETE、LOCK TABLES 或 UPDATE 这三个权限中的至少一个。从 MySQL 8.0.22 开始，只需要 SELECT 权限。

MySQL 8.0.22 开始，SELECT ... FOR SHARE语句不再需要获取 MySQL 授权表中的读锁权限。

2、SELECT ... FOR UPDATE

对于搜索到的索引记录，锁住行和任意与之关联的索引项，这与你在那些行上执行一个 UPDATE 语句操作时相同。在特定的事务隔离级别下，其他事务无法更新这些行，无法执行 SELECT ... FOR SHARE，或读取数据。一致读取忽略在“读取”视图中存在的记录上设置的所有锁。（记录的旧版本无法被锁；它们是通过在记录的内存副本上应用撤消日志来重建的）

这些子句主要用于处理树结构或图形结构的数据（在单个表中或拆分到多个表中）。您可以从一个地方到另一个地方遍历边或树枝，同时保留返回并更改这些“指针”值的权利。提交或回滚事务时，将释放由 FOR SHARE 和 FOR UPDATE 查询设置的所有锁。

注意：

锁定读只有在禁用自动提交时才可能（或者在事务开始时使用 START_TRANSACTION 或设置 autocommit=0）。

外层语句中的 locking read 子句不会锁定嵌套子查询中表的行，除非在子查询中也指定了 locking read 子句。例如，下面的语句不会锁定表 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 记录。你的应用程序代码可以确保整个操作序列中的引用完整性。

首先，使用一个一致读来查询表 PARENT 并 验证 parant 记录存在。你能否确保安全地把这条 child 记录插入到表 child 中？答案是否定的，因为某些其他的会话，可能会在你的 SELECT 语句和 INSERT 语句执行的期间删除掉这条 parent 记录，而不会通知你。

为了避免这个潜在的问题，在执行 SELECT 时使用 FOR SHARE：

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

在 FOR SHARE 查询返回 parent 'Jones'之后，你可以安全第一添加 child 记录到 child 表中，然后提交事务。任何试图在 parent 表的适用行中获取独占锁的事务都会等待，直到你的操作完成，也就是说，直到所有表中的数据处于一致状态。

另一个例子，考虑 child_codes 表中的一个 integer 类型的计数器字段 ，用于为添加到表 child 的每个子级分配唯一标识符。不要使用一致读取或共享模式读取来读取计数器的当前值，因为数据库的两个用户可以看到计数器的相同值，如果两个事务尝试向 child 表中添加具有相同标识符的行，则会发生重复键错误。

这里，FOR SHARE 并不是好的解决方法，因为如果两个用户同时读取计数器，则至少有一个用户在尝试更新计数器时会陷入死锁。

为了实现读和 counter 自增，首先使用 FOR UPDATE 来实现一个计数器字段的锁定读，然后自增计数器字段。例如：

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

SELECT ... FOR UPDATE 读取最新的可用数据，在它读的任一行上设置排它锁。因此，它设置的锁与搜索到的 SQL 更新对行设置的锁相同。

前面的描述仅仅是 SELECT ... FOR UPDATE 如何执行的一个示例。在 MySQL 中，生成唯一标识符的特定任务实际上可以只通过对表的一次访问来完成：

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

SELECT 语句仅检索标识符信息（特定于当前连接）。它不访问任何表。

三 锁定读并发-使用 NOWAIT 和 SKIP LOCKED

如果一行记录被某个事物 T1 锁住，另一个 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 的事务请求同一个被锁住的行时，必须等待直到阻塞中的事务 T1 释放这个行锁。这个行为组织了其他事务更新或删除被其他事务查询并即将修改的行。如果希望查询在请求的行被锁定时立即返回，或者从结果集中排除锁定的行是可以接受的，则无需等待释放行锁定。

为了避免等待其他事务释放行锁，NOWAIT 和 SKIP LOCKED 选项可以与 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 这些锁定读语句组合使用。

• NOWAIT
  

使用了 NOWAIT 的锁定读不会等待获取行锁。查询会立即执行，当请求的行被锁住时立即返回失败。

• SKIP LOCKED
  

使用 SKIP LOCKED 的锁定读也不会等待获取行锁。与 NOWAIT 不同的是，查询也会立即返回，但如果存在锁住的行时，会把这些行从结果集中移除（即只返回没有被锁住的行）。

注意：

使用 SKIP LOCKED 的查询返回的是不一致的数据视图。SKIP LOCKED 因此并不适合一般事务使用。不过，当多个会话访问同一个类似队列的表时，它可以用来避免锁争用。

NOWAIT 和 SKIP LOCKED 仅适用于行级锁。

使用 NOWAIT 或 SKIP LOCKED 的语句对基于语句的复制不安全。

下面的示例演示 NOWAIT 和 SKIP LOCKED。 会话 1 开启事务，在一条记录上获取行锁；会话 2 尝试在同一行记录上使用 NOWAIT 选项进行锁定读。由于请求的行被会话 1 锁住，会话 2 的请求立即返回失败。 在会话 3 中，使用 SKIP LOCKED 的锁定读返回了除去被会话 1 锁住的行之外的结果集。

【注】注意 mysql 版本，MySQL 8.0.22 之后才行。如果是在前面的版本，使用时会报语法错误，因为版本还没有支持 NOWAIT 和 SKIP LOCKED 语法。

# Session 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 |+---+
# Session 2:
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;ERROR 3572 (HY000): Do not wait for lock.
# Session 3:
mysql> START TRANSACTION;
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;+---+| i |+---+| 1 || 3 |+---+

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html