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