Re: Exclusive lock for database rename

On Tue, Nov 08, 2005 at 03:14:34PM -0600, Jim C. Nasby wrote:
> On Sat, Nov 05, 2005 at 11:48:56AM +0100, Martijn van Oosterhout wrote:
> > On Sat, Nov 05, 2005 at 10:47:30AM +0100, Jochem van Dieten wrote:
> > > On 11/4/05, Jim C. Nasby wrote:
> > > >
> > > > I would argue that in cases like this (and 'this' means just about any
> > > > DDL, for starters) that it would be better not to block everyone until
> > > > work can actually be done. Or at least make that an option.
> > >
> > > Would it be possible to simulate this by manually trying to grab a
> > > lock on a relation using NOWAIT in a loop or are the locks DDL
> > > requires different from the ones acquired by the LOCK statement?
> >
> > What you want is probably some kind of "attempt to grab lock with
> > timeout". Ie, it tries to grab the lock but gets stuck waiting for
> > someone else. After some timeout it fails, waits a few seconds and
> > tries again. That few seconds allows other clients waiting for you to
> > unstuck.
> >
> > Set the timeout to maybe 30 seconds. Then no query will wait for your
> > lock for more than 30 seconds. Or maybe exponentially rising delay,
> > otherwise you'll never guarentee completion. With notice to client what
> > is happening, hopefully...

I think this wait with an exponentially rising delay hurts not helps. If the
stricter lock can be granted in a short time, ie the dalay could be small,
then there is no problem. If the lock cannot be granted and the delay expires
the stricter lock has incurred extra wait time already and allowed newer
conflicting requests ahead of it possibly increasing the total wait time.
As the timeout increases newer requests end up waiting for the new longer
time anyway so the overall effect is to increase all lockers total wait time.

-dg

--
David Gould daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.