No Timeout in SELECT..FOR UPDATE - Mailing list pgsql-hackers

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.