
> > It is not something that makes anything unrelyable or less robust.
> 
> How can you argue that?  The presence of a lock timeout *will* make
> operations fail that otherwise would have succeeded; moreover that
> failure will be pretty unpredictable (at least from the point of view
> of the application that issued the command).  That qualifies as
> "unreliable and not robust" in my book.
> A persistent SET variable
> also opens up the risk of completely unwanted failures in critical
> operations --- all you have to do is forget to reset the variable

?????? So what, when you e.g. forget to commit you are also in trouble,
I do not see anything special here.

> when the effect is no longer wanted.  (Murphy's Law guarantees that
> you won't find out such a mistake until the worst possible time.
> That's even less robust.)

My OLTP clients set it to 30 sec right after connect and leave it at that.  

> 
> >> The only way PG could apply reasonable timeouts would be for the 
> >> application to dictate them, 
> 
> > That is exactly what we are talking about here.
> 
> The *real* problem is that the application cannot determine reasonable
> timeouts either.  Perhaps the app can decide how long it is willing to
> wait overall,

Yes, that is it. As I tried to explain earlier, the amount of work that needs to be 
done for the own tx (in OLTP) is pretty well predictable, but the work of other 
clients is not.

> but how can it translate that into the low-level notion of
> an appropriate lock timeout?  It does not know how many locks might get
> locked in the course of a query, nor which locks they are exactly, nor
> what the likely distribution of wait intervals is for those locks.

The above would imho be a wrong approach at determining the timeout.

> Given that, using a lock timeout "feature" is just a crapshoot.  If you
> say "set lock timeout X", you have no real idea how that translates to
> application-visible performance nor how big a risk you are taking of
> inducing unwanted failures.  You don't even get to average out the
> uncertainty across a whole query, because if any one of the lock waits
> exceeds X, your query blows up.  Yet making X large destroys the
> usefulness of the feature entirely, so there will always be a strong
> temptation to set it too low.
> 
> This is the real reason why I've been holding out for restricting the
> feature to a specific LOCK TABLE statement: if it's designed that way,
> at least you know which lock you are applying the timeout to, and have
> some chance of being able to estimate an appropriate timeout.

I do not agree, but such is life :-)

BTW: for distributed txns you need a lock timeout feature anyway, because 
detecting remote deadlocks between two or more different servers would be 
very complicated. And I do think PostgreSQL will need remote db access a la long.

Andreas

Wed, 18 Apr 2001 18:09:37 +0200
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: AW: timeout on lock feature 
>>>>> "A Z" == Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

PS: where can I find more on the distributed txn plans for PostgreSQL? Thanks.

    A Z> BTW: for distributed txns you need a lock timeout feature
    A Z> anyway, because detecting remote deadlocks between two or
    A Z> more different servers would be very complicated. And I do
    A Z> think PostgreSQL will need remote db access a la long.

A typical distributed transaction management system would consist of

- a transaction manager for that particular transaction
- an associated log manager
- an optional associated lock manager (depending on transaction type:
  pessimistic, optimistic, etc)
- one or more resource managers (usually storage)

and thus the lock manager would have a single point of reference for
the existence of a transaction or not.  The "distributed" part in that
scenario would be that one {log|lock|resource} manager could be client
to several transaction managers simultaneously.  The problem of
deadlock detection is that of cyclic dependency-detection among
several transaction managers.  Pessimistic Transaction Managers do use
locks and understand their semantics, thus they can communicate with
their peers that are accessing the shared pool of locks.

I would agree that the simplest solution for deadlock detection is a
timeout, but it certainly is not the only one.

Most desirable would be a measure to choose which transaction to
abort, which simultaneously avoids starvation (no more cycles, ever
for txn X), upper limits (txns beyond X objects / locks / cycles /
... cannot happen), etc..  A timeout mechanism is not going to
approach this measure, but an analysis of the dependency matrix with
the associated information on resource usage of each transaction might
get close.

so long,

Oliver

18 Apr 2001 18:57:22 +0200
Oliver Seidel <seidel@in-medias-res.com>
theory of distributed transactions / timeouts