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

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