[ACDU 翻译] MySQL 15.7.2.4 锁定读取

如果您查询数据，然后在同一事务中插入或更新相关数据，则常规SELECT 语句无法提供足够的保护。其他事务可以更新或删除您刚刚查询的相同行。 InnoDB支持两种类型的 锁定读取，可提供额外的安全性：

• SELECT ... FOR SHARE

  在读取的任何行上设置共享模式锁。其他会话可以读取这些行，但在您的事务提交之前不能修改它们。如果其中任何行被另一个尚未提交的事务更改，您的查询将等待该事务结束，然后使用最新值。

  笔记

  SELECT ... FOR SHARE是 的替代品SELECT ... LOCK IN SHARE MODE，但 LOCK IN SHARE MODE仍可用于向后兼容。语句是等价的。但是，FOR SHARE支持、 和选项。请参阅 使用 NOWAIT 和 SKIP LOCKED 锁定读取并发。 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 授权表上获取读锁。有关详细信息，请参阅 授予表并发性。

• SELECT ... FOR UPDATE

  对于搜索遇到的索引记录，锁定行和任何关联的索引条目，就像您为这些行发出 UPDATE语句一样。其他事务被阻止更新这些行、执行SELECT ... FOR SHARE或读取某些事务隔离级别的数据。一致读取忽略读取视图中存在的记录上设置的任何锁定。（旧版本的记录不能被锁定；它们是通过 在记录的内存副本上 应用撤消日志来重建的。）

  SELECT ... FOR UPDATE需要 特权以及、 或 特权SELECT中的至少一项。 DELETELOCK TABLESUPDATE

这些子句主要在处理树结构或图形结构数据时非常有用，无论是在单个表中还是在多个表中拆分。您从一个地方遍历边缘或树枝到另一个地方，同时保留返回并更改任何这些 “指针”值的权利。

当事务提交或回滚时， 所有由FOR SHARE和查询 设置的锁都会被释放。FOR UPDATE

笔记

只有在禁用自动提交时才能锁定读取（通过使用 START TRANSACTION或设置 autocommit为 0 开始事务）。

外部语句中的锁定读取子句不会锁定嵌套子查询中表的行，除非子查询中还指定了锁定读取子句。例如，以下语句不会锁定 table 中的行 t2。

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

要锁定 table 中的行t2，请在子查询中添加锁定读取子句：

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

锁定读取示例

假设您要向表中插入新行 child，并确保子行在表中有父行parent。您的应用程序代码可以确保整个操作序列的引用完整性。

首先，使用一致性读查询表 PARENT，验证父行是否存在。您可以安全地将子行插入表格 CHILD吗？SELECT不，因为其他一些会话可能会在您和您之间的那一刻删除父行 INSERT，而您不会意识到这一点。

为避免此潜在问题，请执行 SELECTusing FOR SHARE：

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

FOR SHARE查询返回 parent 后'Jones'，您可以安全地将子记录添加到CHILD表中并提交事务。任何尝试在 PARENT表中的适用行中获取排他锁的事务都会等待，直到您完成，即直到所有表中的数据处于一致状态。

再举一个例子，考虑 table 中的整数计数器字段CHILD_CODES，用于为添加到 table 的每个子项分配唯一标识符 CHILD。不要使用一致读取或共享模式读取来读取计数器的当前值，因为数据库的两个用户可能会看到相同的计数器值，并且如果两个事务尝试添加行表的相同标识符CHILD。

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

要实现计数器的读取和递增，首先使用 执行计数器的锁定读取FOR UPDATE，然后递增计数器。例如：

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

ASELECT ... FOR UPDATE读取最新的可用数据，在它读取的每一行上设置排他锁。因此，它设置的锁与搜索的 SQLUPDATE 将在行上设置的锁相同。

前面的描述仅仅是如何 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 请求同一锁定行的事务必须等待，直到阻塞事务释放行锁。此行为可防止事务更新或删除其他事务查询更新的行。但是，如果您希望查询在请求的行被锁定时立即返回，或者如果从结果集中排除锁定的行是可以接受的，则无需等待释放行锁。

为避免等待其他事务释放行锁， NOWAIT选项SKIP LOCKED 可与SELECT ... FOR UPDATE或SELECT ... FOR SHARE 锁定读取语句一起使用。

• NOWAIT

  使用NOWAIT从不等待获取行锁的锁定读取。查询立即执行，如果请求的行被锁定，则失败并出现错误。

• SKIP LOCKED

  使用SKIP LOCKED 从不等待获取行锁的锁定读取。查询立即执行，从结果集中删除锁定的行。

  笔记

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

NOWAIT并且SKIP LOCKED 仅适用于行级锁。

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

下面的例子演示了NOWAIT 和SKIP LOCKED。会话 1 启动一个对单个记录进行行锁定的事务。会话 2 尝试使用该 NOWAIT选项对同一记录进行锁定读取。因为请求的行被会话 1 锁定，锁定读取立即返回错误。在 Session 3 中，locking read withSKIP LOCKED返回请求的行，但被 Session 1 锁定的行除外。

# 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 |
+---+
复制