SELECT..FOR UPDATE has been extended in MySQL version 8.0 to implement new clauses NOWAIT and SKIP LOCKED ( WL#3597 and WL #8919 ).This is a simple try and see how it works.Finally, similar functionality in AliSQL is described below

test

Like the literal meaning of the new syntax, NOWAIT means that an error is returned directly when a lock cannot be acquired, rather than waiting; SKIP LOCKED means that records that have been occupied by other session s are ignored.

--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)

--Insert a record but the transaction does not commit
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)

-- Waiting for log lock timeout
mysql> select * from t1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- Record(2,3) Line lock behavior session 1 Owned, so don't wait to return error code 3572 directly
mysql> select * from t1 for update nowait;

-- Ignore locked records(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)

The new grammar is self-explanatory.In addition to these two new grammars, the OF grammar has been added to specify tables that need to be locked

-- 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)

How to achieve

Commit ID

At a glance, this commit contains a lot of refactoring, so it looks longer, mainly extending at the InnoDB layer, where the lock option is stored in row_prebuilt_t:: select_model

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 */
};

When a lock is held (function lock_rec_lock_slow), it is judged that if the record is locked, NOWAIT returns the error code DB_LOCK_NOWAIT directly, and SKIP LOCKED returns DB_SKIP_LOCKED, but the former returns the error directly, while the latter continues to query the next record (row_search_mvcc)

The overall implementation idea is still simple.

AliSQL-related features

In our Open Source Branch Version ) also extends SELECT..FOR UPDATE.The main control is the time the lock waits.

AliSQL syntax mainly includes

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

This functionality complements the official NOWAIT/SKIP LOCKED.For example, we can define how many seconds to wait before waiting no longer, or skip directly.