Question

ORA-00060: Deadlock detected

Answer 1

Look at the trace file to see the transactions and resources involved. Retry if necessary.

ORA-00060 error indicates that a dead lock occurred due to a resource contention with another session and Oracle rolled back your current statement to resolve the dead lock. The other session can proceed further as usual . Your current sessions rolled backed statements needs to be resubmitted for the execution after the required resources are available.

These dead locks can occur in different scenarios: They can occur while doing parallel DML operations, while updating/deleting data from same tables from different sessions , while performing transactions on bitmap index tables and so on but the mentioned scenarios are the most common ones.
 

 

Resolution/Fix for deadly Embrace Dead Lock:

The session on which ORA-00060 occurred encountered the dead lock and Oracle automatically rolled back the statement that caused the dead lock. No other session got affected, so other sessions functions as usual.

In the affected session, the rolled back statement needs to be re-executed once the resources are available ie. When no other session is competing for the same resource.

Probably it will be a good idea to analyze oracle trace file and then modify the application to avoid this situation.

To avoid deadly embrace dead locks, review the application transaction logic thoroughly in the design phase it-self and ensure tables are ordered with in the transactions/applications in such a way that resource contention not occurs among transactions/applications.