Q

How Oracle unlocks deadlocks

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?
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

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close