Thread

1. AW: AW: timeout on lock feature

   Zeugswetter Andreas SB <zeugswettera@wien.spardat.at> — 2001-04-17T15:20:25Z

   > > Bruce Momjian <pgman@candle.pha.pa.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
   
   

2. Re: AW: AW: timeout on lock feature

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-17T15:31:14Z

   Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.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
   
   

3. Re: AW: AW: timeout on lock feature

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-17T16:56:11Z

   Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.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
   
   

4. Re: timeout on lock feature

   Nathan Myers <ncm@zembu.com> — 2001-04-17T21:01:19Z

   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@zembu.com
   
   

5. Re: timeout on lock feature

   Theo Kramer <theo@flame.co.za> — 2001-04-18T04:34:54Z

   > 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