Hi Guys,

I have a suggestion for fixing a long-term and painful
problem in PostgreSQL that is holding up many very
important commercial projects, including ours!

This problem has been reported numerous times:

When one process has a "row lock" on one or more rows
in a table, using "SELECT...FOR UPDATE" in default lock
mode, another process has NO WAY of aborting from the
same request, and reporting to the user that this record
is already locked, reserved, or whatever you want to call it.

In other words, by the time the second process has run the
"SELECT...FOR UPDATE" statement, it's too late!! This
second process is now locked "forever", waiting for the
first process to commit, rollback, or some other function
that will release the lock. Yes, the first process will eventually
release the lock by commiting or rollback, bu this is for a
commercial environment with users, not "processes", and
the user needs to be informed about the error immediately,
or within a second or so, and be given the chance to "retry"
the update with lock, or just abort and go find another record
to change. This problem is *fundamental*, and *very typical*
in a commercial, accounting, or mission-critical environment.

The only solution to this problem in PostgreSQL seems to be to:

(1) Re-write the "SELECT...FOR UPDATE" SQL code, to
return with an exception or error if it cannot immediately
secure the lock, OR:

(2) Add a "TIMEOUT N" clause to this statement, so that the
timeout can be controlled on a per-statement basis - this is
probably better.

For example:

[1] if I want the second process to give up within 1 millisecond,
and return if the lock is not possible, then write:

SELECT ... FOR UPDATE TIMEOUT 1

[0] If I want the default behaviour, (for a process that is prepared
to wait "forever" for the record(s)), then:

SELECT... FOR UPDATE TIMEOUT 0

OR, simply:

SELECT... FOR UPDATE

(as it is now)

I hope that this suggestion will be taken seriously, since it is
clear that a large number of developpers have made comments
on this problem, dated all the way back to 2001 or earlier.

Many thanks,

Tony Rich,
Richcorp Technology,
Sydney, Australia.



Tue, 10 Feb 2004 16:41:51 +1100
Anthony Rich <richae@optusnet.com.au>
No Timeout in SELECT..FOR UPDATE
Anthony,

What you need is a "NO WAIT" option.
This is already on the TODO list.
This feature should be implemented as GUC (see TODO list).
I don't think that a timeout would be accepted by the core team (doesn't 
make too much sense to me either). Telling PostgreSQL not to wait for 
certain locks is definitely better (at least from my point of view).

We might work on a patch like that in the near future but don't hesitate 
to send a patch yourself.

	Best regards,

		Hans


Anthony Rich wrote:
> Hi Guys,
> 
> I have a suggestion for fixing a long-term and painful
> problem in PostgreSQL that is holding up many very
> important commercial projects, including ours!
> 
> This problem has been reported numerous times:
> 
> When one process has a "row lock" on one or more rows
> in a table, using "SELECT...FOR UPDATE" in default lock
> mode, another process has NO WAY of aborting from the
> same request, and reporting to the user that this record
> is already locked, reserved, or whatever you want to call it.
> 
> In other words, by the time the second process has run the
> "SELECT...FOR UPDATE" statement, it's too late!! This
> second process is now locked "forever", waiting for the
> first process to commit, rollback, or some other function
> that will release the lock. Yes, the first process will eventually
> release the lock by commiting or rollback, bu this is for a
> commercial environment with users, not "processes", and
> the user needs to be informed about the error immediately,
> or within a second or so, and be given the chance to "retry"
> the update with lock, or just abort and go find another record
> to change. This problem is *fundamental*, and *very typical*
> in a commercial, accounting, or mission-critical environment.
> 
> The only solution to this problem in PostgreSQL seems to be to:
> 
> (1) Re-write the "SELECT...FOR UPDATE" SQL code, to
> return with an exception or error if it cannot immediately
> secure the lock, OR:
> 
> (2) Add a "TIMEOUT N" clause to this statement, so that the
> timeout can be controlled on a per-statement basis - this is
> probably better.
> 
> For example:
> 
> [1] if I want the second process to give up within 1 millisecond,
> and return if the lock is not possible, then write:
> 
> SELECT ... FOR UPDATE TIMEOUT 1
> 
> [0] If I want the default behaviour, (for a process that is prepared
> to wait "forever" for the record(s)), then:
> 
> SELECT... FOR UPDATE TIMEOUT 0
> 
> OR, simply:
> 
> SELECT... FOR UPDATE
> 
> (as it is now)
> 
> I hope that this suggestion will be taken seriously, since it is
> clear that a large number of developpers have made comments
> on this problem, dated all the way back to 2001 or earlier.
> 
> Many thanks,
> 
> Tony Rich,
> Richcorp Technology,
> Sydney, Australia.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at


Sun, 15 Feb 2004 22:15:50 +0100
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at>
Re: No Timeout in SELECT..FOR UPDATE
Anthony Rich <richae@optusnet.com.au> writes:
> When one process has a "row lock" on one or more rows
> in a table, using "SELECT...FOR UPDATE" in default lock
> mode, another process has NO WAY of aborting from the
> same request, and reporting to the user that this record
> is already locked, reserved, or whatever you want to call it.

Not so.  See the statement_timeout parameter.

			regards, tom lane

Sun, 15 Feb 2004 16:36:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: No Timeout in SELECT..FOR UPDATE 
On Tue, 10 Feb 2004, Anthony Rich wrote:

> In other words, by the time the second process has run the
> "SELECT...FOR UPDATE" statement, it's too late!! This
> second process is now locked "forever", waiting for the

Or until statement_timeout is reached if it's set to a non-zero value.


Sun, 15 Feb 2004 13:48:12 -0800 (PST)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: No Timeout in SELECT..FOR UPDATE
> (1) Re-write the "SELECT...FOR UPDATE" SQL code, to
> return with an exception or error if it cannot immediately
> secure the lock, OR:

You could use SET STATEMENT_TIMEOUT...

Chris


Mon, 16 Feb 2004 09:23:50 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: No Timeout in SELECT..FOR UPDATE
On Sunday 15 February 2004 16:36, Tom Lane wrote:
> Anthony Rich <richae@optusnet.com.au> writes:
> > When one process has a "row lock" on one or more rows
> > in a table, using "SELECT...FOR UPDATE" in default lock
> > mode, another process has NO WAY of aborting from the
> > same request, and reporting to the user that this record
> > is already locked, reserved, or whatever you want to call it.
>
> Not so.  See the statement_timeout parameter.
>

ISTM this is the same problem with the stacked up vacuums... and 
statement_timeout doesnt solve it.  If someone sets statement_timeout = 
<small number> then true, there lock waiting will timeout if it hits the 
statement_timeout limit, but if the statement itself just takes longer than 
statement_timeout in the processing itself, then it also bombs out... and you 
have no way to really differentiate the two different cases.   what is needed 
i think is a lock_timeout, which times out soley for cases where the lock can 
not be aquired in a speedy manner.

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Mon, 16 Feb 2004 10:53:11 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: No Timeout in SELECT..FOR UPDATE
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Sunday 15 February 2004 16:36, Tom Lane wrote:
>> Not so.  See the statement_timeout parameter.

> what is needed i think is a lock_timeout, which times out soley for
> cases where the lock can not be aquired in a speedy manner.

I didn't say that there wasn't room for improvement, I was just
responding to the OP's assertion that there is "NO WAY" to do it now.

In practice though I think that a statement_timeout solves the problem
well enough.  The sort of command you are going to be worried about is
something like a SELECT ... FOR UPDATE with a simple WHERE condition,
and if the normal (unblocked) execution time isn't very much less than
your expected maximum transaction time then you've got problems anyway.
Whatever sort of timeout you use, you'd have to set at some multiple of
the expected max transaction time unless you want to get unnecessary
failures.  So it seems to me that there's plenty of daylight there,
and relatively little need for a per-lock timeout.

			regards, tom lane

Mon, 16 Feb 2004 11:16:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: No Timeout in SELECT..FOR UPDATE 
>Robert Treat
> On Sunday 15 February 2004 16:36, Tom Lane wrote:
> > Anthony Rich <richae@optusnet.com.au> writes:
> > > When one process has a "row lock" on one or more rows
> > > in a table, using "SELECT...FOR UPDATE" in default lock
> > > mode, another process has NO WAY of aborting from the
> > > same request, and reporting to the user that this record
> > > is already locked, reserved, or whatever you want to call it.
> >
> > Not so.  See the statement_timeout parameter.
> >
> 
>what is
> needed
> i think is a lock_timeout, which times out soley for cases where the
lock
> can
> not be aquired in a speedy manner.

Perhaps another way is to specify that you do not wish to wait at all.

Oracle and DB2, possibly others, allow the use of the NOWAIT operator,
applied to a preceding LOCK statement, which acts just as it says. If it
encounters a lock, it returns immediately. This then returns control
immediately to the application, so that it can report to the user to get
further instructions. My understanding is that implementing that might
require some fairly basic changes to the internal locking API - maybe
not too complex, but it might cause many changes; I'd vote for it, but
don't hold your breath...

Alternatively, don't use the SELECT..FOR UPDATE metaphor, try another
design that doesn't require this style of locking. Application level
locking can get you round many problems - the database can't do
everything.

Best regards, Simon Riggs




Tue, 17 Feb 2004 06:51:38 -0000
"Simon Riggs" <simon@2ndquadrant.com>
Re: No Timeout in SELECT..FOR UPDATE