Ask the Expert

ORA-00060 error doing table inserts

I am getting random ORA-00060 for INSERTS on a table when the transaction volume is high. This is a multi-user retail environment, wherein multiple sessions are inserting data into the same table. This table has a composite primary key, and all session are inserting unique records. The main table has a non default initrans of 3.

    Requires Free Membership to View

The ORA-00060 error is "deadlock detected while waiting for resource." You are running into deadlock issues, which will most often occur when your transaction volume is high. A deadlock occurs in the following situation:

You have Transaction A (T_A). T_A attempts to modify two objects, TableX and TableY.

Simulataneously, there is Transaction B (T_B). T_B is attempting to modify the same two objects, TableX and TableY.

T_A is waiting for a lock to be released on TableY, but T_B has that lock and will not release it until T_B ends.

T_B is waiting for a lock to be released on TableX, but T_A has that lock and will not release it until T_A ends.

Both transactions are waiting for a lock that the other transaction holds. And neither transaction will release that lock. So a deadlock condition occurs.

When Oracle detects a deadlock, it kills the session that detected the condition. One time it could be T_A that gets killed and another, it could be T_B. You will also find a trace file in your database's USER_DUMP_DEST directory. This trace file is essential in helping to fix your problem. This trace file contains the information about what the transaction has locked, and what the transaction is waiting to be released.

There is really only one way to stop deadlocks from occuring. That way is to change your application so that it either does not simultaneously lock the objects involved, or to commit more often, thus releasing locks quicker. If this is not your application, then contact your application's vendor and send them the appropriate information so that they can fix the problem.


This was first published in October 2004

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: