pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: lock timeout patch
2+ messages / 2 participants
[nested] [flat]

* Re: lock timeout patch
@ 2004-06-29 17:36 Josh Berkus <josh@agliodbs.com>
  2004-06-29 20:26 ` Re: lock timeout patch Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Josh Berkus @ 2004-06-29 17:36 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org

Tom,

> I'd accept a mechanism to enforce a timeout at the lock level if you
> could show me a convincing use-case for lock timeouts instead of
> statement timeouts, but I don't believe there is one.  I think this
> proposal is a solution in search of a problem.

Hmmm ... didn't we argue this out with NOWAIT?   What did we conclude then?  
I'm reluctant to go over old ground repeatedly.

Let me say for myself that I would use this feature if it existed, but would 
not miss it a whole lot if the patch was rejected.    Here's the idea:

I have an OLAP database of regional office evaluations (in SQL Server, sadly) 
which requires that the evaluations, sometimes interlocking, of regions be 
"closed" simultaneously (in one transaction).   This means that during the 
closure process, certain kinds of data entry needs to be frozen out.   I am 
using SQL Server's lock timeout functionality for this; bascially, the data 
entry waits for 30 seconds, and then tells the user to try again in 10 
minutes.

I could do the same thing in PostgreSQL using NOWAIT and a loop on the client 
side.   But the lock timeout is somewhat easier.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: lock timeout patch
  2004-06-29 17:36 Re: lock timeout patch Josh Berkus <josh@agliodbs.com>
@ 2004-06-29 20:26 ` Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Simon Riggs @ 2004-06-29 20:26 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-hackers@postgresql.org

On Tue, 2004-06-29 at 18:36, Josh Berkus wrote:
> Tom,
> 
> > I'd accept a mechanism to enforce a timeout at the lock level if you
> > could show me a convincing use-case for lock timeouts instead of
> > statement timeouts, but I don't believe there is one.  I think this
> > proposal is a solution in search of a problem.
> 
> Hmmm ... didn't we argue this out with NOWAIT?   What did we conclude then?  
> I'm reluctant to go over old ground repeatedly.
> 
> Let me say for myself that I would use this feature if it existed, but would 
> not miss it a whole lot if the patch was rejected.    Here's the idea:
> 
Can't vouch for the patch, but I can say this would get used...

> I have an ... database ...
> which requires that the evaluations, sometimes interlocking, of regions be 
> "closed" simultaneously (in one transaction).   This means that during the 
> closure process, certain kinds of data entry needs to be frozen out.   I am 
> using ... lock timeout functionality for this; bascially, the data 
> entry waits for 30 seconds, and then tells the user to try again in 10 
> minutes.

Just implementing this same scenario, using DB2 (...). Of course, if I
had MVCC on that application, I could argue that this is not
required...is that the basis of the "not required" view?

> 
> I could do the same thing in PostgreSQL using NOWAIT and a loop on the client 
> side.   But the lock timeout is somewhat easier.

SQLServer and DB2 support a lock timeout system wide, simple but not
granular. Oracle supports the NOWAIT option, even though it supports
readers-dont-block locking. I prefer the NOWAIT option as it gives a
more detailed handle on the exact statements that you wish to wait, or
not.

Without NOWAIT, we would need to set lock_timeout = 30 (seconds)

Statement level timeout is a different thing entirely, since there are
very often statements that need to run for 2-3 hours (even more in some
cases), so statement level timeout is set to 10000 (seconds).

Best Regards, Simon Riggs




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-06-29 20:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-29 17:36 Re: lock timeout patch Josh Berkus <josh@agliodbs.com>
2004-06-29 20:26 ` Simon Riggs <simon@2ndquadrant.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox