ORA-00060: Deadlock detected while waiting for resource Exception in Oracle

ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

1| java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource
1| at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
1| at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
1| at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
1| at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
1| at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
1| at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java
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.

At this point I think we would like to explore more about dead locks, so here are the in depth details on dead locks:
How to Fix it?
Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions/statements involved in the deadlock, thus releasing one set of resources/data locked by that transaction. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also produce detailed information in a trace file under database's UDUMP directory.

Most commonly these deadlocks are caused by the applications that involve multi table updates in the same transaction and multiple applications/transactions are acting on the same table at the same time. These multi-table deadlocks can be avoided by locking tables in same order in all applications/transactions, thus preventing a deadlock condition.

1 comment:

  1. A deadlock occurs when 2 sessions block each other by attempting to update a row, which is already updated by another session but has not been yet committed or rolled back. There can be more than 2 sessions involved, but the main idea is the same.




Please Provide your feedback here