Thread: Curosity question regarding "LOCK" NOWAIT

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?

is this in the sql spec?


what do you expect to happen if these timeouts expire? return an error,
and abort the transaction?


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On Sep 22, 2012, at 0:08, John R Pierce <pierce@hogranch.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),
whendealing the explicit LOCK TABLE or the SELECT…FOR(SHARE|UPDATE) commands? 
>
> is this a feature in any other major databases?

Doesn't matter to me...I'm only using PostgreSQL
>
> is this in the sql spec?
>

Guessing no if it is not implemented...

> what do you expect to happen if these timeouts expire? return an error, and abort the transaction?
>

The same thing that happens if "NOWAIT" had been specified instead.

The goal is to specify that I don't mind waiting but only for a short period of time.

David J.

On Fri, Sep 21, 2012 at 7:43 PM, David Johnston <polobo@yahoo.com> 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?

I think you could do this by issuing

   SET statement_timeout = 50;

and then attempting the LOCK TABLE or SELECT ... FOR UPDATE command,
without NOWAIT. You'll either get an "ERROR:  canceling statement due
to statement timeout" or the command should succeed.

Josh

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?

 

David J.

we have a proposed patch for 9.3 at
http://archives.postgresql.org/message-id/50339B3D.8040403@cybertec.at
that adds "SET lock_timeout = N;" to have a time-limited wait for locks.

Best regards,
Zoltán Böszörményi

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de    http://www.postgresql.at/

2012-09-22 06:08 keltezéssel, John R Pierce írta:
> 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?

Yes, at least Informix has it in the form of SET LOCK MODE TO { NOT WAIT | WAIT [N] }.

> is this in the sql spec?

No.

>
>
> what do you expect to happen if these timeouts expire? return an error, and abort the
> transaction?
>
>


--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
      http://www.postgresql.at/

2012-09-22 07:55 keltezéssel, Josh Kupershmidt írta:
> On Fri, Sep 21, 2012 at 7:43 PM, David Johnston <polobo@yahoo.com> 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?
> I think you could do this by issuing
>
>     SET statement_timeout = 50;
>
> and then attempting the LOCK TABLE or SELECT ... FOR UPDATE command,
> without NOWAIT. You'll either get an "ERROR:  canceling statement due
> to statement timeout" or the command should succeed.

This is not the same as the handling of a timeout waiting for a lock.
The statement_timeout may also trigger when returning large
result sets without locking, i.e. in a plain SELECT case.

Best regards,,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
      http://www.postgresql.at/

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