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:

http://bugs.mysql.com/bug.php?id=49763

• 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.

Changed Syntax

Vastly simplified, the <select> syntax in MySQL is

   SELECT ...
   [FROM ...]
   [WHERE ...]
   ...
   [<locking clause>]

The current <locking clause> syntax is

   FOR UPDATE | LOCK IN SHARE MODE

This work will augment the <locking clause> syntax to the following:

   SELECT ...
   [FROM ...]
   [WHERE ...]
   ...
   [<locking clause> [, ...]]

   <locking_clause> ::=

       FOR <lock_strength> [ OF table_name [, ...] ] [ <locked_row_action> ]
     | LOCK IN SHARE MODE

   <lock_strength> ::= UPDATE | SHARE

   <locked_row_action> ::= 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

Semantics

<lock_strength> Semantics

• 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.

OF semantics

If the OF syntax is used, the <locking clause> applies only to the named tables. Other tables in the query block are processed as usual.

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.

Access Restriction

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.

New Errors

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.

Lexer/Parser Changes

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 <locking_clause_list>.

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 <table expression>
 FOR <lock strength> OF <table reference list>

all table references in <table expression> are either bare table names, qualified table names or aliases. Hence it is trivial to do look-ups in the parsed query during the contextualize() phase for the <table reference list>. Failed resolutions of the locking clause will then abort the query before proper name resolution. E.g. the query

 SELECT * FROM t1 FOR UPDATE OF t2

will fail for t2 regardless of whether t1 exists.

Stubs for Table Locks

This work adds new members to the thr_lock_type enum. As far as this worklog is concerned, the sole purpose of them is to make the code unit-testable. The unit tests will parse queries, build AST structures and check the TABLE_LIST::lock_type member. Exactly how these flags are then used by the storage engine is handled in WL#8919.

Derived tables, subqueries within the query block with a locking clause and CTE's will not be locked, as the lock is pointless anyway. This simplifies the test for duplicated table locks.

New Errors

The new error messages are

• ER_UNRESOLVED_TABLE_LOCK
• ER_DUPLICATE_TABLE_LOCK.
• ER_BINLOG_UNSAFE_SKIP_LOCKED
• ER_BINLOG_UNSAFE_NOWAIT