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

2009/5/11 Boszormenyi Zoltan <zb(at)cybertec(dot)at>:
> Does statement_timeout counts against subtransactions as well? No.
> If a statement finishes before statement_timeout, does it also decrease
> the possible runtime for the next statement? No. I was talking about
> locks acquired during one statement.

With respect I can't figure out what you're trying to say here.

> He argued about he wants a timeout *independent* from statement_timeout
> for locks only inside the same statement IIRC.

I think what you're saying is you think he only wanted to distinguish
total time spent waiting for locks from total time spent executing
including such things as i/o wait time. That's possible, Hans-Juergen
wasn't very clear on what "locking issues" he was concerned about. I
can think of a few categories of "locking issues" that might be
problems though:

1) A web application wants to ensure that a slow batch job which locks
records doesn't impact responsiveness. I think statement_timeout
handles this better though.

2) A batch job might want to ensure it's still "making progress" even
if slowly, but some other jobs might block indefinitely while holding
locks (for example an email generating script might be stuck waiting
for remote sites to respond). statement_timeout is better for ensuring
overall execution speed but it won't fire until the entire time
allotment is used up whereas something which detects being stuck on an
individual lock would detect the problem much earlier (and perhaps the
rest of the job could still be completed).

3) Applications which have hidden deadlocks because they block each
other outside the database while holding locks in the database. This
can be dealt with by using userlocks to represent the external
resources but that depends on all of those external resources being
identified correctly. A lock timeout would be an imprecise way to
detect possible deadlocks even though it's always possible it just
didn't wait long enough.

Hans-Juergen, are any of these use cases good descriptions of your
intended use? Or do you have a different case?
--
greg