ORA-00060: deadlock detected while waiting for resource

Resolving Deadlocks

Oracle automatically detects deadlocks and rolls back one of the transactions to resolve the deadlock. The session whose transaction is rolled back will receive an error message.

ORA-00060: deadlock detected while waiting for resource

Avoiding Deadlocks

To avoid deadlocks, consider the following best practices:

1. Access Objects in a Consistent Order: Ensure that transactions access tables and rows in the same order.
2. Use Short Transactions: Keep transactions short and commit or rollback as soon as possible.
3. Minimize User Interaction in Transactions: Avoid waiting for user input during a transaction.
4. Use Lower Isolation Levels: Where appropriate, use lower isolation levels to reduce locking.
5. Proper Indexing: Ensure proper indexing to avoid long-running queries that might hold locks for extended periods.

Monitoring Deadlocks

Oracle provides a trace file and logs the details of the deadlock in the alert log file. You can find these details to diagnose and understand the deadlock.

To check for deadlocks, you can also query the V$LOCK and V$SESSION views.

SELECT l1.sid AS waiting_session, l1.type AS waiting_lock_type, l2.sid AS blocking_session, l2.type AS blocking_lock_type FROM v$lock l1 JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2 JOIN v$session s1 ON l1.sid = s1.sid JOIN v$session s2 ON l2.sid = s2.sid WHERE l1.block = 0 AND l2.block = 1;

What is a Deadlock?

A deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved but the idea is the same.

Example of Deadlock

The following example demonstrates a deadlock scenario.

Setup

create table eg_60 ( num number, txt varchar2(10) );
insert into eg_60 values ( 1, 'First' );
insert into eg_60 values ( 2, 'Second' );

commit;
select rowid, num, txt from eg_60;

ROWID                     NUM TXT
------------------ ---------- ----------
AAASuCAAEAAAAinAAA          1 First
AAASuCAAEAAAAinAAB          2 Second

Session #1:

update eg_60 set txt='ses1' where num=1;

Session #2:

update eg_60 set txt='ses2' where num=2;
update eg_60 set txt='ses2' where num=1;

Session #2 is now waiting for the TX lock held by Session #1

Session #1:

update eg_60 set txt='ses1' where num=2;

Session #1 is now waiting  on the TX lock for this row.
The lock is held by Session #2.
However Session #2  is already waiting on Session #1
This causes a deadlock scenario so deadlock detection kicks in and one of the sessions signals an ORA-60.

Session #2:

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Session #1 is still blocked until Session #2 commits or rolls back as ORA-60  only rolls back the current statement and not the entire transaction.

Reference : Troubleshooting "ORA-00060 Deadlock Detected" Errors (Doc ID 62365.1)