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.

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.

Dig Deeper on Oracle database design and architecture