Oracle Error ORA-00060
ORA-00060:deadlock detected while waiting for resource
Cause: Transactions deadlock one another waiting for resources
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary. Explanation: A deadlock has been detected so your statement has been rolled back to allow the other party to continue.
Explanation: A deadlock has been detected so your statement has been rolled back to allow the other party to continue.
A deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embraced) and cannot continue processing.
Oracle automatically detects deadlocks and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. The session that is rolled back will observe Oracle Error ORA-00060: deadlock detected while waiting for resource. Oracle will also write out a trace file with detailed information to the database's UDUMP directory.
Multi-table deadlocks can be avoided by locking tables in same order (in all applications), thus preventing a deadlock condition. For example, session1 lock table pippo then pluto; session2pippo then pluto; If this is not possible, your application should check for ORA-00060 errors and restart the rolled back transactions.
|
|
|
Session 1 lock table pippo:
SQL> UPDATE pippo SET column= column+100; 14 rows updated. Session 2 lock table pluto:
SQL> UPDATE pluto SET name = 'merovingio'; 4 rows updated. Session 1 now update pluto. The session will hang waiting for a lock (not a deadlock yet!):
SQL> UPDATE pluto SET name = 'merovingio.it'; Session 2 now update pippo, causing the deadlock:
SQL> UPDATE pippo SET column= column+100;
Oracle will detect the deadlock and roll back one of these statements:
SQL> UPDATE pippo SET column= column+100; UPDATE pippo SET column= column+100;* ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
|
|
|
|
|
|
|
|