Re: Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)

On Sun, 2007-11-02 at 12:21 -0600, Jim C. Nasby wrote:
> On Thu, Feb 08, 2007 at 08:47:42AM -0800, Marc Munro wrote:
> > One of the causes of deadlocks in Postgres is that its referential
> > integrity triggers can take locks in inconsistent orders. Generally a
> > child record will be locked before its parent, but not in all cases.
>
> Where would PostgreSQL lock the parent before the child? AFAIK the
> behavior should be consistent...

Consider a table C containing 2 child records C1 and C2, of parent P.
If transaction T1 updates C1 and C2, the locking order of the the
records will be C1, P, C2. Another transaction, T2, that attempts to
update only C2, will lock the records in order C2, P.

The locks on C2 and P are taken in different orders by the two
transactions, leading to the possibility of deadlock.

__
Marc