🧩 ORA-00060: Deadlock Detected While Waiting for Resource

🧩 In Oracle Database, a deadlock occurs when two or more sessions wait for each other to release resources, forming a circular dependency that halts progress. The database automatically detects the situation and terminates one of the involved transactions to break the deadlock, generating the ORA-00060 error.

🔹 What Causes ORA-00060?

This issue usually arises from poor transaction or application design. It happens when multiple sessions try to access the same resources but in different order.

For example:

Session 1 locks a row in Table A, then tries to lock a row in Table B.

Session 2 locks a row in Table B, then tries to lock a row in Table A.

Since both sessions are waiting for each other’s resource, neither can proceed — leading Oracle to detect a deadlock and cancel one transaction.

🔹 How to Diagnose the Problem

1. Check the Trace File

When a deadlock occurs, Oracle automatically writes details to a trace file in the diagnostic directory (DIAGNOSTIC_DEST or USER_DUMP_DEST).

You can locate the path using:

SHOW PARAMETER diag

Then open the latest trace file and review the SQL statements and locked resources mentioned.

2. Find Locking Sessions

To see which sessions are holding or waiting for locks, run:

SELECT s.sid, s.serial#, l.id1, l.id2, l.lmode, l.request, 

       s.username, s.machine

FROM v$lock l, v$session s

WHERE s.sid = l.sid;

3. Analyze Application Logic

Review the code to ensure transactions access database objects in a consistent order and avoid long-running locks.

🔹 Example Scenario

Session 1

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

UPDATE departments SET budget = budget - 1000 WHERE dept_id = 20;

Session 2

UPDATE departments SET budget = budget - 2000 WHERE dept_id = 20;

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

In this example, each session locks one table and then tries to lock another that’s already held by the other session. This triggers:

ORA-00060: deadlock detected while waiting for resource

🔹 Recommended Solutions

Access tables and rows in the same order across all sessions.

Use short transactions and commit frequently.

Avoid user interactions (like prompts) during transactions that hold locks.

Use SELECT FOR UPDATE NOWAIT or SELECT FOR UPDATE SKIP LOCKED to prevent waiting indefinitely.

🔹 Proactive Tips

Review transaction design in all application modules.

Regularly monitor locks using v$lock, v$session, or Oracle Enterprise Manager.

Implement proper indexing and ensure no unnecessary locks are placed during batch operations.

By following consistent transaction practices and reducing locking conflicts, you can prevent ORA-00060 and maintain stable database performance.