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".
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.