On Mon, 2007-12-02 at 00:10 -0500, Tom Lane wrote: > Marc Munro <[EMAIL PROTECTED]> 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
Description: This is a digitally signed message part