MySQL8.0新特性随笔： NOWAIT以及SKIP LOCKED

MySQL8.0版本中对SELECT..FOR UPDATE进行了扩展，实现了新的子句NOWAIT 及 SKIP LOCKED ( WL#3597 及 WL #8919)。 本文简单的试玩一把，并看看是怎么实现的。最后介绍下AliSQL中存在的类似功能

测试

如新语法的字面含义，NOWAIT表示当无法获取到锁时直接返回错误，而不是等待；SKIP LOCKED表示忽略那些已经被其他session占有行锁的记录。

--session 1
mysql> use test
Database changed
mysql> create table t1 (a int primary key, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

--插入一条记录但事务不提交
mysql> insert into t1 values (2,3);
Query OK, 1 row affected (0.00 sec)

-- session 2
mysql> set session innodb_lock_wait_timeout = 2;
Query OK, 0 rows affected (0.00 sec)

-- 等待记录锁超时
mysql> select * from t1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 记录(2,3) 的行锁为session 1拥有，因此不等待直接返回错误码3572
mysql> select * from t1 for update nowait;

-- 忽略被锁住的记录(2,3)
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
mysql> select * from t1 for update skip locked;
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
+---+------+
1 row in set (0.00 sec)

新语法的功能一目了然，不再赘述。除了这两个新语法外，还增加了OF语法指定需要加锁的表

-- session 2

mysql> create table t2 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

mysql> select * from t1 for update of t2 nowait;
ERROR 3568 (HY000): Unresolved table name `t2` in locking clause.

mysql> select * from t1,t2 for update of t2 nowait;
+---+------+------+------+
| a | b    | a    | b    |
+---+------+------+------+
| 1 |    2 |    1 |    2 |
+---+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t1,t2 for update of t1 nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

mysql> select * from t1,t2 for update of t1 skip locked;
+---+------+------+------+
| a | b    | a    | b    |
+---+------+------+------+
| 1 |    2 |    1 |    2 |
+---+------+------+------+
1 row in set (0.00 sec)

如何实现的

Commit ID

简单看了下，这个commit包含了大量的重构，所以看起来比较长，主要在InnoDB层进行了扩展，加锁选项被存储到row_prebuilt_t::select_mode中

enum select_mode {
        SELECT_ORDINARY,        /* default behaviour */
        SELECT_SKIP_LOCKED,     /* skip the row if row is locked */
        SELECT_NOWAIT           /* return immediately if row is locked */
};

在加锁时(函数 lock_rec_lock_slow) 会据此进行判断，如果记录已经加锁， NOWAIT直接返回错误码DB_LOCK_NOWAIT， SKIP LOCKED的话就返回DB_SKIP_LOCKED，但前者会直接返回错误，后者则去继续查询下一条记录(row_search_mvcc)

整体的实现思路还是满简单的。

AliSQL相关特性

在我们的开源分支版本)中，也对SELECT..FOR UPDATE进行了扩展。主要是对锁等待的时间进行了控制。

AliSQL的语法主要包括

SELECT ... FOR UPDATE [WAIT [n]|NO_WAIT]
SELECT ... LOCK IN SHARE MODE [WAIT [N]|NO_WAIT]
LOCK TABLE ... [WAIT [n]|NO_WAIT]

这个功能可以和官方的NOWAIT/SKIP LOCKED形成很好的补充。例如我们可以定义成等待多少秒后不再等待，或者直接跳过。