Re: AW: AW: timeout on lock feature

> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Added to TODO:
> > > * Add SET parameter to timeout if waiting for lock too long
> >
> > I repeat my strong objection to any global (ie, affecting all locks)
> > timeout. Such a "feature" will have unpleasant consequences.
>
> I envisioned:
>
> SET TIMEOUT TO 10;
> UPDATE tab SET col = 3;
> RESET TIMEOUT
>
> Can't we get that work work properly? Let the timeout only apply to the
> 'tab' table and none of the others. Can't we exclude system tables from
> being affected by the timeout?

Why exactly would you be willing to wait longer for an implicit system table lock?
If this was the case you should also be willing to wait for the row lock, no ?
The timeout will be useful to let the client or user decide on an alternate course
of action other that killing his application (without the need for timers or threads in
the client program).

> Requiring a LOCK statement that matches
> the UPDATE/DELETE and wrapping the whole thing in a transaction seems
> needlessly complex to me.

Agreed.

Andreas

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at> writes:
> The timeout will be useful to let the client or user decide on an
> alternate course of action other that killing his application (without
> the need for timers or threads in the client program).

This assumes (without evidence) that the client has a good idea of what
the timeout limit ought to be. I think this "feature" has no real use
other than encouraging application programmers to shoot themselves in
the foot. I see no reason that we should make it easy to misdesign
applications.

regards, tom lane

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at> writes:
> The timeout will be useful to let the client or user decide on an
> alternate course of action other that killing his application (without
> the need for timers or threads in the client program).

Okay, let's take a close look at this assumption.

1. Why is 10 seconds (or 1, or 30) a magic number? If you've waited
that long, why wouldn't you be willing to wait a little longer? How
will you know what value to pick?

2. If you do want a timeout to support an interactive application, seems
to me that you want to specify it as a total time for the whole query,
not the maximum delay to acquire any individual lock. Under normal
circumstances lock delays are likely to be just a small part of total
query time.

3. Since we already have deadlock detection, there is no need for
timeouts as a defense against deadlock. A timeout would only be useful
to defend against other client applications that are sitting idle or
executing long-running operations while holding locks that conflict
with your real-time query. This scenario strikes me as a flaw in the
overall application design, which should be fixed by fixing those other
clients and/or the lock usage. A lock timeout is just a bandaid
to cope (poorly) with broken application design.

4. The correct way to deal with overly-long queries in an interactive
application is to let the user interactively cancel queries (which we
already support). This is much better than any application-specified
fixed timeout, because the application is unlikely to be aware of
extenuating circumstances --- say, the system is heavily overloaded at
the moment because of lots of activity. I can think of few things more
annoying than an application-set timeout that kills my unfinished query
whenever the system is under load.

In short, I think lock timeout is a solution searching in vain for a
problem. If we implement it, we are just encouraging bad application
design.

regards, tom lane

On Tue, Apr 17, 2001 at 12:56:11PM -0400, Tom Lane wrote:
> In short, I think lock timeout is a solution searching in vain for a
> problem. If we implement it, we are just encouraging bad application
> design.

I agree with Tom completely here.

In any real-world application the database is the key component of a
larger system: the work it does is the most finicky, and any mistakes
(either internally or, more commonly, from misuse) have the most
far-reaching consequences. The responsibility of the database is to
provide a reliable and easily described and understood mechanism to
build on.

Timeouts are a system-level mechanism that to be useful must refer to
system-level events that are far above anything that PG knows about.
The only way PG could apply reasonable timeouts would be for the
application to dictate them, but the application can better implement
them itself.

You can think of this as another aspect of the "end-to-end" principle:
any system-level construct duplicated in a lower-level system component
can only improve efficiency, not provide the corresponding high-level
service. If we have timeouts in the database, they should be there to
enable the database to better implement its abstraction, and not pretend
to be a substitute for system-level timeouts.

There's no upper limit on how complicated a database interface can
become (cf. Oracle). The database serves its users best by having
the simplest interface that can possibly provide the needed service.

Nathan Myers
ncm(at)zembu(dot)com

> Timeouts are a system-level mechanism that to be useful must refer to
> system-level events that are far above anything that PG knows about.
> The only way PG could apply reasonable timeouts would be for the
> application to dictate them, but the application can better implement
> them itself.

OK we have the following scenario

Session A Session B

begin begin

insert -- on unique constraint

insert -- on same unique constraint

-- Session A becomes idle

: -- Session B becomes ...

or we have (Informix Online)

Session A Session B

set lock mode to wait [seconds] set lock mode to wait [seconds]

begin begin

insert -- on unique constraint

insert -- on same unique constraint

* resource not available error *

-- Session B carries on

Oracle 7 (OCI) has oopt() call to set wait options for requested
resources. Oracle 8 OCI has the same behaviour as PG ie. oopt()
is no longer available.

I believe that the ability to switch the database to either not wait
for resources, or wait a specified period or wait forever
(default) is essential especially for interactive applications.

Regards
Theo