MySQL官方文档14.5.2.4 锁定读取

原文地址：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

14.5.2.4 锁定读取

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

• SELECT … LOCK IN SHARE MODE
  在读取的任何行上设置共享模式锁定。其他会话可以读取行，但在您的事务提交之前无法修改它们。如果这些行中的任何一行已被另一个尚未提交的事务更改，那么您的查询将一直等到该事务结束，然后使用最新值。
• SELECT … FOR UPDATE
  对于搜索遇到的索引记录，锁定行和任何关联的索引条目，就像为这些行发起UPDATE语句一样。其他事务被阻止更新这些行，从执行SELECT … LOCK IN SHARE MODE或者通过读取特定事务隔离级别的数据。一致性读取忽略读取视图中存在的记录上设置的任何锁定。（记录的旧版本不能被锁定；它们通过在记录的内存副本上应用撤消日志来重建。）

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

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

注意
使用SELECT FOR UPDATE锁定行以进行更新仅适用于禁用自动提交时（通过使用START TRANSACTION开始事务或将自动提交设置为0）。如果启用自动提交，则不会锁定与规范匹配的行。

锁定读取示例

假设你想插入一个新的行到一个子表中，并确保子行在父表中有一个父行。您的应用程序代码可以确保贯穿这一系列操作的参照完整性。

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

为了避免这种潜在的问题，请使用LOCK IN SHARE MODE执行SELECT：

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

在LOCK IN SHARE MODE查询返回父’Jones’后，可以安全地将子记录添加到CHILD表并提交事务。任何尝试获取PARENT表中适用行的排他锁的事务都会等待，直到完成为止，也就是说，直到所有表中的数据都处于一致状态。

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

在这里，LOCK IN SHARE MODE不是一个好的解决方案，因为如果两个用户同时读取计数器，最终至少有一个用户在尝试更新计数器时死锁。

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

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

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语句仅检索标识符信息（特定于当前连接）。它不访问任何表。