How Oracle unlocks deadlocks

A deadlock occurs when two transactions are holding locks the other transactions require. Oracle does not release locks until the transaction ends. And the transactions will sit there and wait until the locks are released. So a Catch-22 condition occurs. The transaction will not release the lock until it obtains the other lock and finishes. And the lock held by the second transaction will not release the lock the first one requires until it contain obtain the lock held by the first transaction. This condition is called a deadlock.

When two transactions become deadlocked, they will be able to determine that this condition exists. The first transaction to detect a deadlock condition is terminated. By terminating one of the transactions, the locks are released and the remaining transaction can now complete.

If you are receiving deadlocks, then your only course to resolve these are through better programming techniques. There are two common actions to perform.

1. Make sure that your application performs DML operations on tables in the same order. Having one section of your application code perform DML on three tables in one order while another section of your application code performing DML on these same tables in a different order can introduce a deadlock condition if two users simultaneously execute the different sections of code.

2. Make sure that you are committing as often as possible. When you commit (or roll back) your transaction, you release the locks that transaction held.

This Metalink document contains more information on deadlocks and how to resolve them. The Oracle Concepts Guide also contains more information.