Search Postgresql Archives

Re: Curosity question regarding "LOCK" NOWAIT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 22 September 2012 05:08, John R Pierce <pierce@xxxxxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux