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包含了大量的重构，所以看起来比较长，主要在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形成很好的补充。例如我们可以定义成等待多少秒后不再等待，或者直接跳过。
