Re: Patch to allow multiple table locks in "Unison"

Neil Padgett <npadgett(at)redhat(dot)com> writes:
> For multiple tables, the tables are locked in series. The
> request blocks until a lock on all tables is obtained, each in turn. The
> tables are locked in OID order always -- this should prevent
> deadlocking.

This approach is fatally flawed. I believe the only reasonable way
is to lock the tables in the order given in the statement.

The idea of locking in OID order would be fine if single-statement-
locking-all-tables-used-in-the-transaction were the only way things were
ever locked, but in any real application you'll have to contend with
other actions that may lock tables sequentially, eg, read A, think for
a bit, write B. If you write "LOCK A,B" and B's OID happens to precede
A's, you're busted. What's worse, the code may behave correctly when
you write it, only to fail after a drop/recreate table or database dump
and reload, when the relative order of A's and B's OIDs changes.

In short, the order has to be user-specified, which pretty much
means it had better be as given in the statement. Which raises
the significant question of why bother with such a patch at all, as
opposed to using a series of LOCK statements as one can do already.
(With more flexibility, since separate LOCK statements can specify
different lock modes.) What's the rationale for adding such a
feature in the first place? I don't see that it's buying anything.

regards, tom lane