NOWAIT及SKIP LOCKED的使用

NOWAIT及SKIP LOCKED的使用

Locking Read Concurrency with NOWAIT and SKIP LOCKED

If a row is locked by a transaction, a SELECT … FOR UPDATE or SELECT … FOR SHARE transaction that requests the same locked row must wait until the blocking transaction releases the row lock. This behavior prevents transactions from updating or deleting rows that are queried for updates by other transactions. However, waiting for a row lock to be released is not necessary if you want the query to return immediately when a requested row is locked, or if excluding locked rows from the result set is acceptable.

如果某一行被一个事务A锁住了，另外一个包含SELECT…FOR UPDATE 或SELECT…FOR SHARE的事务B请求被事务A锁住的行时，那么事务B必须等待事务A释放行锁，才能执行。这种行为能够阻止一个事务更新或删除已经被另外一个事务通过updates锁住的行。
然而，当请求的行有被锁住的，你又想立即返回查询结果时或者对于查询结果不包括被锁住的行是可接受的，这时仍然等待锁被自然释放，就显得没有必要了。

To avoid waiting for other transactions to release row locks, NOWAIT and SKIP LOCKED options may be used with SELECT … FOR UPDATE or SELECT … FOR SHARE locking read statements.

NOWAIT和SKIP LOCKED选项可以用于SELECT…FOR UPDATE或者SELECT…FOR SHARE语句，用以避免等待其他事务释放锁住的行。

• NOWAIT

A locking read that uses NOWAIT never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.

使用了NOWAIT的SELECT…FOR UPDATE或者SELECT…FOR SHARE语句，不需要等待以获取行锁（等待其他事务释放）。查询会立即执行，如果请求的行被锁住了那么查询会失败并返回一个错误。

• SKIP LOCKED

A locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.

使用了SKIP LOCKED的SELECT…FOR UPDATE或者SELECT…FOR SHARE语句，不需要等待以获取行锁（等待其他事务释放）。查询会立即执行，查询时会移除被锁住的行（会从结果集中剔除被锁住的行）。

Note
	Queries that skip locked rows return an inconsistent view of the data. SKIP LOCKED is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queue-like table.

注意：
使用SKIP LOCKED返回的数据视图不能保证一致性。因此，SKIP LOCKED不适合一般的事务性工作。然而，在多个会话存取相同的queue-like表时，它可用于避免锁连接。

NOWAIT and SKIP LOCKED only apply to row-level locks.

NOWAIT和SKIP LOCKED只能用于行级锁

Statements that use NOWAIT or SKIP LOCKED are unsafe for statement based replication.

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

The following example demonstrates NOWAIT and SKIP LOCKED. Session 1 starts a transaction that takes a row lock on a single record. Session 2 attempts a locking read on the same record using the NOWAIT option. Because the requested row is locked by Session 1, the locking read returns immediately with an error. In Session 3, the locking read with SKIP LOCKED returns the requested rows except for the row that is locked by Session 1.

下面的例子演示了在SELECT…FOR UPDATE语句中使用NOWAIT和SKIP LOCKED。Session1启动了一个事务锁住了一条记录。Session2试图使用NOWAIT来获取Session1
中相同的记录的锁。因为Session2请求的行已经被Session1锁住了，由于Session2使用了NOWAIT，所以会直接返回一个错误。在Session3中，由于使用了
SKIP LOCKED,会直接返回除了被Session1锁住的行以外的行。

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

以上翻译自：摘自MySQL 8 官方文档

使用示例

参考：MySQL 中NOWAIT及SKIP LOCKED的使用示例

MySQL 事务使用

参考：MySQL存储过程 事务 简介及两者之间的关系