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
