How can a deadlock problem can be solved by using Oracle? Could you give me instructions on how this DBMS works to recover from a deadlock?

    Requires Free Membership to View

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.

This was first published in May 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: