Thread: AW: timeout on lock feature
> > 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. It is not something that makes anything unrelyable or less robust. It is also simple: "I (the client) request that you (the backend) dont wait for any lock longer than x seconds" > 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. I think you are talking about different kinds of timeouts here. > 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. > but the application can better implement them itself. It can, but it makes the program more complicated (needs timers or threads, which violates your last statement "simplest interface". > > 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. Mentioned functionality has nothing to do with above statement which I can fully support. > 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. Agreed. Andreas
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
> 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
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.)
>> 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, 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.
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.
regards, tom lane
> 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. As I pointed before - it's half useless. And I totally do not understand why to object feature 1. that affects users *only when explicitly requested*; 2. whose implementation costs nothing - ie has no drawbacks for overall system. It was general practice in project so far: if user want some feature and it doesn't affect others - let's do it. What's changed? Vadim
> One idea Tom had was to make it only active in a transaction, > so you do: > > BEGIN WORK; > SET TIMEOUT TO 10; > UPDATE tab SET col = 3; > COMMIT > > Tom is concerned people will do the SET and forget to RESET > it, causing all queries to be affected by the timeout. And what? Queries would be just aborted. It's not critical event to take responsibility from user. Vadim
[ Charset ISO-8859-1 unsupported, converting... ] > > 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. > > As I pointed before - it's half useless. > > And I totally do not understand why to object feature > > 1. that affects users *only when explicitly requested*; > 2. whose implementation costs nothing - ie has no drawbacks > for overall system. > > It was general practice in project so far: if user want some > feature and it doesn't affect others - let's do it. > What's changed? This is another reason to make it be SET TIMEOUT ... because then we don't have to have this NOWAIT tacked on to every command. It keeps the parser and manual pages cleaner, and it is a non-standard extension. One idea Tom had was to make it only active in a transaction, so you do: BEGIN WORK;SET TIMEOUT TO 10;UPDATE tab SET col = 3;COMMIT Tom is concerned people will do the SET and forget to RESET it, causing all queries to be affected by the timeout. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
[ Charset ISO-8859-1 unsupported, converting... ] > > One idea Tom had was to make it only active in a transaction, > > so you do: > > > > BEGIN WORK; > > SET TIMEOUT TO 10; > > UPDATE tab SET col = 3; > > COMMIT > > > > Tom is concerned people will do the SET and forget to RESET > > it, causing all queries to be affected by the timeout. > > And what? Queries would be just aborted. It's not critical event > to take responsibility from user. Hey, I agree. If the users wants the TIMEOUT, give it to them. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, Apr 18, 2001 at 09:54:11AM +0200, Zeugswetter Andreas SB 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. > > It is not something that makes anything unrelyable or less robust. > It is also simple: "I (the client) request that you (the backend) > dont wait for any lock longer than x seconds" Many things that are easy to say have complicated consequences. > > 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. > > I think you are talking about different kinds of timeouts here. Exactly. I'm talking about useful, meaningful timeouts, not random timeouts attached to invisible events within the database. > > 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. No. You wrote elsewhere that the application sets "30 seconds" and leaves it. But that 30 seconds doesn't have any application-level meaning -- an operation could take twelve hours without tripping your 30-second timeout. For the application to dictate the timeouts reasonably, PG would have to expose all its lock events to the client and expect it to deduce how they affect overall behavior. > > but the application can better implement them itself. > > It can, but it makes the program more complicated (needs timers > or threads, which violates your last statement "simplest interface". It is good for the program to be more complicated if it is doing a more complicated thing -- if it means the database may remain simple. People building complex systems have an even greater need for simple components than people building little ones. What might be a reasonable alternative would be a BEGIN timeout: report failure as soon as possible after N seconds unless the timer is reset, such as by a commit. Such a timeout would be meaningful at the database-interface level. It could serve as a useful building block for application-level timeouts when the client environment has trouble applying timeouts on its own. Nathan Myers ncm@zembu.com
> What might be a reasonable alternative would be a BEGIN timeout: report > failure as soon as possible after N seconds unless the timer is reset, > such as by a commit. Such a timeout would be meaningful at the > database-interface level. It could serve as a useful building block > for application-level timeouts when the client environment has trouble > applying timeouts on its own. Now that is a nifty idea. Just put it on one command, BEGIN, and have it apply for the whole transaction. We could just set an alarm and do a longjump out on timeout. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote: > > What might be a reasonable alternative would be a BEGIN timeout: report > > failure as soon as possible after N seconds unless the timer is reset, > > such as by a commit. Such a timeout would be meaningful at the > > database-interface level. It could serve as a useful building block > > for application-level timeouts when the client environment has trouble > > applying timeouts on its own. > > Now that is a nifty idea. Just put it on one command, BEGIN, and have > it apply for the whole transaction. We could just set an alarm and do a > longjump out on timeout. Of course, it begs the question why the client couldn't do that itself, and leave PG out of the picture. But that's what we've been talking about all along. Nathan Myers ncm@zembu.com
> On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote: > > > What might be a reasonable alternative would be a BEGIN timeout: report > > > failure as soon as possible after N seconds unless the timer is reset, > > > such as by a commit. Such a timeout would be meaningful at the > > > database-interface level. It could serve as a useful building block > > > for application-level timeouts when the client environment has trouble > > > applying timeouts on its own. > > > > Now that is a nifty idea. Just put it on one command, BEGIN, and have > > it apply for the whole transaction. We could just set an alarm and do a > > longjump out on timeout. > > Of course, it begs the question why the client couldn't do that > itself, and leave PG out of the picture. But that's what we've > been talking about all along. Yes, they can, but of course, they could code the database in the application too. It is much easier to put the timeout in a psql script than to try and code it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, Apr 18, 2001 at 09:39:39PM -0400, Bruce Momjian wrote: > > On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote: > > > > What might be a reasonable alternative would be a BEGIN timeout: > > > > report failure as soon as possible after N seconds unless the > > > > timer is reset, such as by a commit. Such a timeout would be > > > > meaningful at the database-interface level. It could serve as a > > > > useful building block for application-level timeouts when the > > > > client environment has trouble applying timeouts on its own. > > > > > > Now that is a nifty idea. Just put it on one command, BEGIN, and > > > have it apply for the whole transaction. We could just set an > > > alarm and do a longjump out on timeout. > > > > Of course, it begs the question why the client couldn't do that > > itself, and leave PG out of the picture. But that's what we've > > been talking about all along. > > Yes, they can, but of course, they could code the database in the > application too. It is much easier to put the timeout in a psql script > than to try and code it. Good: add a timeout feature to psql. There's no limit to what features you might add to the database core once you decide that new features need have nothing to do with databases. Why not (drum roll...) deliver e-mail? Nathan Myers ncm@zembu.com