> [Charset ISO-8859-1 unsupported, filtering to ASCII...]
> > I was looking at this
> > 
> > * Allow LOCK TABLE tab1, tab2, tab3 so all tables locked in unison
> > 
> > but I'm not sure if my solution is really what was wanted, because it
> > doesn't actually guarantee an all-or-nothing lock, it just locks each
> > table in order. Thus it's more like a syntax simplification and reduces
> > overhead.
> > 
> 
> It took a few minutes, but I remember the use for this.  If you are
> going to hang waiting to lock tab3, you don't want to lock tab1 and tab2
> while you are waiting for tab3 lock.  The user wanted all tables to lock
> in one operation without holding locks while waiting to complete all
> locking.
> 
> Can you do the locks, and if one fails, not hang, but unlock the
> previous tables, go lock/hang on the failure, and go back and lock the
> others? Seems it would have to be some kind of lock/fail/unlock/wait
> loop.

[CC to hackers]

Let me add to this.  One problem is that my description would sometimes
lock the tables in different orders, and that is a recipe for deadlock.

If you have to release earlier locks to wait on a later lock, once you
get the later lock, you must release it and then start from the
beginning, locking them in order again.  If you don't, the system could
report a deadlock at random times, which would be very bad.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 18 Dec 1999 15:26:15 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Lock
   > > * Allow LOCK TABLE tab1, tab2, tab3 so all tables locked in unison

   Let me add to this.  One problem is that my description would sometimes
   lock the tables in different orders, and that is a recipe for deadlock.

   If you have to release earlier locks to wait on a later lock, once you
   get the later lock, you must release it and then start from the
   beginning, locking them in order again.  If you don't, the system could
   report a deadlock at random times, which would be very bad.

I'll add something, too. :) I think this derived from a suggestion I
made long ago.  My idea was that when multiple tables need locking, a
deadlock can occur in the process of doing them one at a time.  My
suggested solution was based on an analogy with the way ethernet
packets work.

- go through the list locking tables along the way.

- if a lock cannot be obtained within some time, release some (all?) locks,
  and try again after some random time.

- keep trying (and releasing as needed) until some other timeout
  passes, and then punt.

My thought was that if colliding locks are occuring, some sequence of
relinquishing locks (not necessarily all of them with each trial),
waiting, and reasserting them should work around the collisions.
Introducing random components to this might reduce the overall waiting
time, but I suppose a careful analysis of this needs to be done.
Perhaps just releasing all of the locks, waiting a random time, and
trying again is enough.

Somehow there has to be a mechanism for atomically asserting locks on
more than one table.

Cheers,
Brook

Sat, 18 Dec 1999 16:14:50 -0700 (MST)
Brook Milligan <brook@biology.nmsu.edu>
Re: [HACKERS] Re: [PATCHES] Lock
Brook Milligan <brook@biology.nmsu.edu> writes:
> Somehow there has to be a mechanism for atomically asserting locks on
> more than one table.

(scratches head reflectively...)  y'know, thirty years ago there were
a bunch of smart people writing PhD theses about this type of issue.
I've got to think it's been a solved problem for a long time.  Seems
like someone should go spend a long afternoon in a university library
and dig up the answer.

			regards, tom lane

Sun, 19 Dec 1999 02:27:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: [PATCHES] Lock 