Re: Curosity question regarding "LOCK" NOWAIT

On 22 September 2012 05:08, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 09/21/12 7:43 PM, David Johnston wrote:
>>
>> Has there been any discussion regarding adding a time-limited version of
>> NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK
>> TABLE or the SELECT…FOR(SHARE|UPDATE) commands?
>
>
> is this a feature in any other major databases?

There may be other/better ways to do this, I'm only a casual user of
at least one of these, but here is what I could find for the big
three:

Oracle:
Error if locks can't be obtained immediately:
SELECT ... FOR UPDATE NOWAIT
Error if locks can't be obtained in <time>:
SELECT ... FOR UPDATE WAIT <seconds>
Skip rows that can't be locked immediately:
SELECT ... FOR UPDATE SKIP LOCKED

Microsoft SQL Server:
Error if locks can't be obtained immediately:
SELECT ... FOR UPDATE WITH (NOWAIT)
Error if locks can't be obtained in <time>:
SET LOCK_TIMEOUT <milliseconds>
SELECT ... FOR UPDATE
Skip rows that can't be locked immediately:
SELECT ... FOR UPDATE WITH (READPAST)

IBM DB2
Error if locks can't be obtained immediately:
SET CURRENT LOCK TIMEOUT NOWAIT
SELECT ... FOR UPDATE
Error if locks can't be obtained in <time>:
SET CURRENT LOCK TIMEOUT WAIT <seconds>
SELECT ... FOR UPDATE
Skip rows that can't be locked immediately:
-- currently z/OS version only
SELECT ... FOR UPDATE SKIP LOCKED ROWS

> is this in the sql spec?

My understanding is that the SQL 1992 spec doesn't talk about
locking directly, it talks about isolation levels and
updatability (and I don't have accesss to the 2003 spec to check
if that has changed). Although it does standardise FOR
UPDATE (<updatability clause> which is an optional part of a
<cursor specification>), it's not for explicit locking, it
specifies that a cursor is updatable. Locking is an
implementation matter (and the use of FOR UPDATE outside of a
<cursor specification>, ie in a <query specification>, may be
non-standard anyway). NOWAIT is not an ANSI SQL keyword, and
WAIT is a keyword reserved for future use.

Regards,
Thomas Munro