Re: SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5

Hi,

Tom Lane írta:
> Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> writes:
>
>> i would like to propose an extension to our SELECT FOR UPDATE mechanism.
>> especially in web applications it can be extremely useful to have the
>> chance to terminate a lock after a given timeframe.
>>
>
> I guess my immediate reactions to this are:
>
> 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks?
>
> 2. That "clear and easy to use" oracle syntax sucks. You do not want
> to be embedding lock timeout constants in your application queries.
> When you move to a new server and the appropriate timeout changes,
> do you want to be trying to update your clients for that?
>
> What I think has been proposed previously is a GUC variable named
> something like "lock_timeout", which would cause a wait for *any*
> heavyweight lock to abort after such-and-such an interval. This
> would address your point about not wanting to use an overall
> statement_timeout, and it would be more general than a feature
> that only works for SELECT FOR UPDATE row locks, and it would allow
> decoupling the exact length of the timeout from application query
> logic.
>

Would the "lock_timeout" work for all to be acquired locks individually,
or all of them combined for the statement? The individual application
of the timeout for every locks individually wouldn't be too nice.
E.g. SELECT ... FOR ... WAIT N (N in seconds) behaviour in this
scenario below is not what the application writed would expect:

xact 1: SELECT ... FOR UPDATE (record 1)
xact 2: SELECT ... FOR UPDATE (record 2)
xact 3: SELECT ... FOR UPDATE WAIT 10 (record 1 and 2, waits for both
records sequentially)
xact 1: COMMIT/ROLLBACK almost 10 seconds later
xact 3 acquires lock for record 1, wait for lock on record2
xact 2: COMMIT/ROLLBACK almost 10 seconds later
xact 3 acquires lock for record 2

3rd transaction has to wait for almost 2 times the specified time.
E.g. in Informix the SET LOCK MODE TO WAIT N works
for all to-be acquired locks combined. If lock_timeout and/or
... "FOR <lockmode> WAIT N" ever gets implemented, it should
behave that way.

Best regards,
Zoltán Böszörményi

> regards, tom lane
>
>

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/