MySQL8.0新特性隨筆：NOWAIT以及SKIPLOCKED

zhaiwx_yinfeng發表於2017-08-05
MySqlAI

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形成很好的補充。例如我們可以定義成等待多少秒後不再等待，或者直接跳過。


相關文章