14.7.2.4 锁定读
如果查询数据,然后在同一事务中插入或更新相关数据,则常规SELECT
语句不能提供足够的保护。其他事务可以更新或删除刚查询的相同行。 InnoDB
支持两种提供额外安全性的locking reads:
在读取的任何行上设置共享模式锁定。其他会话可以读取行,但是在事务提交之前不能修改它们。如果这些行中的任何一个被尚未提交的另一个事务更改,则查询将 await 直到该事务结束,然后使用最新值。
对于索引记录,搜索遇到的情况,锁定行和任何关联的索引条目,就像您为这些行发出UPDATE
语句一样。禁止其他事务更新这些行,执行SELECT ... LOCK IN SHARE MODE
或读取某些事务隔离级别的数据。一致的读取将忽略读取视图中存在的记录上设置的任何锁定。 (记录的旧版本无法锁定;可以通过在记录的内存副本上应用undo logs来重构它们。)
这些子句在处理单个 table 或跨多个 table 的树结构或图结构数据时最有用。您从一处到另一处遍历边缘或树枝,同时保留返回并更改任何这些“指针”值的权利。
提交或回滚事务时,将释放由LOCK IN SHARE MODE
和FOR UPDATE
查询设置的所有锁。
Note
只有在禁用自动提交(通过以START TRANSACTION开始事务或将autocommit设置为 0)时,才可以进行锁定读取。
除非在子查询中也指定了锁定读取子句,否则外部语句中的锁定读取子句不会锁定嵌套子查询中 table 的行。例如,以下语句不会锁定 tablet2
中的行。
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;
要锁定 tablet2
中的行,请向子查询添加一个锁定 read 子句:
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;
读锁示例
假设您要在 tablechild
中插入新行,并确保子行在 tableparent
中具有父行。您的应用程序代码可以确保整个操作序列的引用完整性。
首先,使用一致的读取来查询 tablePARENT
并验证父行是否存在。您可以安全地将子行插入 tableCHILD
吗?不可以,因为某些其他会话可能会在SELECT
和INSERT
之间的瞬间删除父行,而不会引起您注意。
为避免此潜在问题,请使用LOCK IN SHARE MODE
执行SELECT:
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
LOCK IN SHARE MODE
查询返回父级'Jones'
后,您可以安全地将子记录添加到CHILD
table 中并提交事务。任何尝试获取PARENT
table 中适用行中的排他锁的事务都将等到您完成操作(即所有 table 中的数据处于一致状态)后再进行。
对于另一个示例,请考虑 tableCHILD_CODES
中的整数计数器字段,该字段用于向添加到 tableCHILD
的每个子项分配唯一标识符。不要使用一致读取或共享模式读取来读取计数器的当前值,因为数据库的两个用户可能会看到该计数器的相同值,并且如果两个事务尝试使用以下方式添加行,则会发生重复键错误: CHILD
table 具有相同的标识符。
在这里,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 中,生成唯一标识符的特定任务实际上可以仅通过单次访问 table 来完成:
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
SELECT语句仅检索标识符信息(特定于当前连接)。它不访问任何 table。