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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
