【Mysql-InnoDB 系列】锁定读

系列文章：

【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

 

关注infoq、微信公众号：程序员架构进阶，实时获取最新文章内容，欢迎您的光临。