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

On Mon, 2007-12-02 at 00:10 -0500, Tom Lane wrote:
> Marc Munro <marc@bloodnok.com> writes:
> > 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.
>
> But the lock on P is shared, hence no deadlock.

Doh!  Yes, you are right.  It is not that simple.

For deadlock to occur, we need a transaction that takes an exclusive
lock on P as well as on one of the children.  Let us replace T2 with a
new transaction, T3, which is going to update P and only one of its
children.

If T3 is going to update P and C1 without the possibility of deadlock
against T1, then it must take out the locks in the order C1, P.  If, on
the other hand, it is going to update P and C2, then the locks must be
taken in the order P, C2.

This means that there is no single strategy we can apply to T3 that will
guarantee to avoid deadlocks with transactions that update only C (ie
transactions, which to a developers point of view do nothing to P, and
so should be unable to deadlock with T3).

From an application developer's standpoint there are few options, none
of them ideal:

1) Insist on a locking policy that requires updates to first lock their
parent records.

This is horrible for so many reasons.  It should be unnecessary; it
causes exclusive locking on parent records, thereby eliminating the
gains made by introducing row share locks in 8.1; it is onerous on the
developers; it is error-prone; etc

2) Remove FK constraints to eliminate the possibility of RI-triggered
deadlocks.

Ugh.

3) Encapsulate all transactions in some form of retry mechanism that
traps deadlocks and retries those transactions.

This may not be practicable, and incurs all of the overhead of
encountering and trapping deadlocks in the first place.  Also, as each
deadlock occurs, a number of locks will be left active before deadlock
detection kicks in, increasing the window for further deadlocks.  On a
busy system, the first deadlock may well trigger a cascade of further
deadlocks.

__
Marc