WL#3597: Implement NOWAIT and SKIP LOCKED

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

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.

Contents


Changed Syntax

Vastly simplified, the