Thread: No Timeout in SELECT..FOR UPDATE
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.
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
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.
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
> (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
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
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
>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