Thread: lock deadlocks

I have just encountered some applications that really need
transactions and so have been perusing the transaction statements and
the lock man page.  Thinking of the possibility of deadlocks if two
processes try to acquire locks in opposite order suggested a solution.

Couldn't the parser syntax be expanded to
 LOCK [TABLE] table1 [, table2 [, table3 [...]]]

in which case locks on the entire group of tables could be obtained
atomically.  If one fails, the process should release locks on all the
rest, wait a bit, and retry.  This should prevent infinite deadlocks
since all locks (not just the most recent one of several independent
locks) would be released at some point, allowing other processes to
assert theirs.

I say all this based only on the man page and my naive understanding
of how the locking code actually works.  Perhaps the man page doesn't
really reflect the actual deadlock problem, though.

Cheers,
Brook

> I have just encountered some applications that really need
> transactions and so have been perusing the transaction statements and
> the lock man page.  Thinking of the possibility of deadlocks if two
> processes try to acquire locks in opposite order suggested a solution.
> 
> Couldn't the parser syntax be expanded to
> 
>      LOCK [TABLE] table1 [, table2 [, table3 [...]]]
> 
> in which case locks on the entire group of tables could be obtained
> atomically.  If one fails, the process should release locks on all the
> rest, wait a bit, and retry.  This should prevent infinite deadlocks
> since all locks (not just the most recent one of several independent
> locks) would be released at some point, allowing other processes to
> assert theirs.

You give a nice extension of the LOCK statement, that is quite valid,
_and_ can not be simulated with multiple lock statements.

Complex kernel locking systems, mostly multi-cpu kernels, have to do
similar things.  You want an 'all or nothing' lock statement.  I will
add this to the TODO list.

--  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,
Pennsylvania19026