Sort order and deadlocks

Deadlocks occur when two or more sessions mutually block each other to the point where neither session can progress. As a result, these sessions continue to block until the database management system kills one of the deadlocked sessions in order for the others to continue.

Deadlocks occur when two or more sessions obtain resource locks in an arbitrary fashion. For example, the following is a classic example:

   Txn 1                                  Txn 2
   Locks Record A                      Locks Record B
   Tries to Lock Record B    (blocked)    Tries to Lock Record A (blocked)
   

In the example above, Txn 1 holds the lock for Record A and Txn 2 holds the lock for Record B. When Txn 1 tries to lock Record B, it becomes blocked. When Txn 2 tries to lock Record A, it also becomes blocked. Now, neither session can progress unless one of the transaction is killed.

If the resource locks were obtained in a consistent order, the deadlock does not occur. For example, all transactions agree to lock the records in ascending order (Record A then Record B).

Replaying the example above, we now have:

   Txn 1                                  Txn 2
   Locks Record A                      Tries to Lock Record A (blocked)
   Locks Record B    
   commits
                                       Locks Record A
                                       Locks Record B
                                       commits

In the example above, Txn 2 is delayed but not deadlocked. Both transactions eventually complete.