WL#3597: Implement NOWAIT and SKIP LOCKED
In SQL Server, Sybase and Oracle there is a neat way of dealing with locked rows. When a row is locked due to an UPDATE or a SELECT ... FOR UPDATE, any other transaction will have to wait to access that locked row. In many cases there is a need to either return immediately if a row is locked or ignore locked rows. I.e.:
Session 1: mysql> BEGIN WORK; mysql> SELECT * FROM tab1 WHERE col1 = 1 FOR UPDATE;
Session 2: mysql> SELECT * FROM tab1 WHERE col1 = 1 FOR UPDATE NOWAIT; ERROR xxxx (xxxxx): Selected row is locked.
Session 3 (Oracle): mysql> SELECT * FROM tab1 WHERE col1 = 1 FOR UPDATE SKIP LOCKED; Empty set
Session 3 (Sybase / SQL Server): mysql> SELECT * FROM tab1 WHERE col1 = 1 FOR UPDATE NOWAIT SKIP LOCKED; Empty set
I could think of other syntaxic means of doing this, like using a pseudocolumn or a function that contains the session id of the session that holds a lock and NULL if there are no locks. This is cool as it can have other uses too:
SELECT * FROM tab1 WHERE lock_session() IS NULL; and SELECT DISTINCT lock_session() FROM tab1 WHERE lock_session() IS NOT NULL;
NOWAIT is also a possible option for transactional LOCK TABLE.
This worklog confines itself to the changes in the server. The changes to InnoDB will be tracked by WL#8919.
There is a contribution for the SKIP LOCKED feature:
- F-1: A locking clause using SKIP LOCKED will never wait for acquiring a row lock on the listed tables. Instead, the locked rows are removed from the result set.
- F-2: A locking clause using NOWAIT will never wait for acquiring a row lock. Instead, the query will fail with an error.
- F-3: A locking clause that does not list tables shall lock all tables in the query block.
- NF-1: Statements involving SKIP LOCKED shall be flagged unsafe for statement-based replication with the flag BINLOG_STMT_UNSAFE_SKIP_LOCKED.
- NF-2: Statements involving NOWAIT shall be flagged unsafe for statement-based replication with the flag BINLOG_STMT_UNSAFE_NOWAIT.
Vastly simplified, the
SELECT ... [FROM ...] [WHERE ...] ... [
FOR UPDATE | LOCK IN SHARE MODE
This work will augment the
SELECT ... [FROM ...] [WHERE ...] ... [
[ OF table_name [, ...] ] [ ] | LOCK IN SHARE MODE
::= UPDATE | SHARE
::= NOWAIT | SKIP LOCKED
Note that prior to this work, the locking clause did not have a name. The name "locking clause" comes from Postgres. Oracle calls it "for update clause": https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm
- The lock strength UPDATE corresponds to the current FOR UPDATE semantics.
- The new lock strength SHARE corresponds to the current LOCK IN SHARE MODE syntax. In other words, LOCK IN SHARE MODE is equivalent to FOR SHARE. The new syntax is more fine-grained, however, as it allows to name the tables that the lock applies. The old syntax will be kept for backward compatibility.
If the OF syntax is used, the
If OF is omitted, all tables in the query block are locked. Hence, using a locking clause without named tables together with any other locking clause is an error.
In order to execute a query with a locking clause, a user needs the SELECT and at least one of the LOCK TABLES, UPDATE or DELETE privileges.
There will be new error messages for the following cases:
- A table reference in a locking clause cannot be resolved in the current query block.
- A table appears in multiple locking clauses.
- A statement is unsafe for statement-based replication due to SKIP LOCKED.
- A statement is unsafe for statement-based replication due to NOWAIT.
The tokens INVISIBLE, NOWAIT, OF and VISIBLE are added. Since OF is an SQL reserved word, it cannot be used for any identifiers.
New rules and corresponding parse tree nodes are added for
On Name Resolution
Previously the contextualize() phase set the TABLE_LIST::lock_type member. This is rather surprising as locking was not previously available on a per-table basis. But it fits perfectly for this feature. Still, the tables to be locked also have to be resolved. We do not need full-fledged name resolution per se, however. In a query such as
SELECT ... FROM