锁定读取

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

• SELECT ... FOR SHARE

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

  注意

  SELECT ... FOR SHARE是的替代SELECT ... LOCK IN SHARE MODE，但LOCK IN SHARE MODE仍可用于向后兼容。这些语句是等效的。然而，FOR SHARE支持，和选项。请参阅使用NOWAIT和SKIP LOCKED锁定读取并发。OF table_nameNOWAITSKIP LOCKED

• SELECT ... FOR UPDATE

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

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

提交或回滚事务时，将释放由FOR SHARE和设置的所有锁定FOR UPDATE。

注意

仅当禁用自动提交时（START TRANSACTION通过autocommit以0 开始事务或设置为0 才可以进行锁定读取）。

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

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

要锁定table中的行，t2请向子查询添加锁定的read子句：

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

锁定阅读示例

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

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

为避免此潜在问题，请执行以下SELECT使用FOR SHARE：

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

在之后FOR SHARE的查询返回父'Jones'，你可以放心地将孩子记录添加到CHILD表并提交事务。任何试图获取PARENT表中适用行中的排他锁的事务都将等到您完成操作（即所有表中的数据处于一致状态）后再进行。

再举一个例子，考虑一个表中的整数计数器字段，该字段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;

A SELECT ... FOR UPDATE读取最新的可用数据，并在读取的每一行上设置排他锁。因此，它设置与搜索的SQL 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事务必须等待，直到阻塞事务释放该行锁为止。此行为可以防止事务更新或删除其他事务为更新而查询的行。但是，如果希望在锁定请求的行时立即返回查询，或者可以接受从结果集中排除锁定的行，则不必等待释放行锁。

为了避免等待其他事务释放行锁，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锁定，因此锁定读取立即返回错误。在会话3中，使用read进行的锁定读取SKIP LOCKED返回请求的行，但会话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 	|
+---	+