🧩 ORA-00060: Deadlock Detected While Waiting for Resource

 🧩 Introduction

In Oracle databases, a deadlock happens when two or more sessions are waiting for each other to release locks on resources they need. This error — ORA-00060: Deadlock detected while waiting for resource — indicates a circular locking condition that Oracle detects and resolves automatically by rolling back one of the statements to break the cycle.

Common Causes

1. Two sessions updating the same rows in different order — for example, Session A updates row 1 then row 2, while Session B updates row 2 then row 1.

2. Unindexed foreign keys — when child tables are updated or deleted and Oracle must scan the parent table without proper indexes.

3. Application logic issues — poorly designed transactions that lock data inconsistently.

4. Concurrent access to shared tables — especially in OLTP systems with heavy DML operations.

How to Diagnose

1. Check the alert log for the ORA-00060 entry. It usually contains the path to a trace file in the USER_DUMP_DEST or DIAG directory.

2. Review the trace file — it shows which SQL statements and tables are involved in the deadlock.

3. Use v$locked_object and v$session to find which sessions hold or wait for locks:

SELECT s.sid, s.serial#, l.object_id, o.object_name, l.locked_mode

FROM v$locked_object l, dba_objects o, v$session s

WHERE l.object_id = o.object_id

AND l.session_id = s.sid;

4. Check indexes on foreign keys to ensure they exist for every relationship.

Possible Solutions

Reorder DML statements so all transactions access tables and rows in the same sequence.

Add missing indexes on foreign key columns.

Keep transactions short — commit frequently to release locks sooner.

Avoid committing inside loops when processing bulk updates.

Review application code for transaction design and consistency.

Example Scenario

Two users attempt to update the same table rows in reverse order:

-- Session 1

UPDATE employees SET salary = salary + 100 WHERE emp_id = 10;

UPDATE employees SET salary = salary + 100 WHERE emp_id = 20;

-- Session 2

UPDATE employees SET salary = salary + 100 WHERE emp_id = 20;

UPDATE employees SET salary = salary + 100 WHERE emp_id = 10;

Oracle detects the deadlock and throws ORA-00060 for one of the sessions.

Best Practices to Avoid Deadlocks

1. Always update rows in a consistent order across all sessions.

2. Use proper indexes to avoid full table scans under concurrent DML.

3. Keep transactions as short as possible.

4. Implement retry logic in applications for deadlock-handled transactions.

Conclusion

Deadlocks are a natural part of concurrent database systems, but they can be minimized by applying good transaction design and indexing strategies. Regularly monitor your applications for locking patterns and ensure consistent DML ordering to maintain smooth performance.