WL#3597: Implement NOWAIT or SKIP LOCKED or both

Affects: Server-7.1   —   Status: Un-Assigned   —   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 LOCK, see WL#3561 "transactional LOCK TABLE".