Oracle Database Deadlock – (ORA-00060: deadlock detected) Complete Troubleshooting Guide

Introduction :

A deadlock is a situation in which two or more sessions are waiting for each other to release locks, creating a circular dependency. Unlike simple blocking, where one session waits for another, a deadlock involves a cycle of waits that can never be resolved naturally.

Oracle Database automatically detects deadlocks and resolves them by rolling back one of the sessions involved, returning the error:

ORA-00060: deadlock detected while waiting for resource

This guide provides a comprehensive, step-by-step approach to understanding, creating, detecting, diagnosing, and resolving deadlocks in Oracle Database.

Background :

Difference Between Blocking and Deadlock

CREATE TABLE deadlock_test_a (id NUMBER PRIMARY KEY, description VARCHAR2(100));

CREATE TABLE deadlock_test_b (id NUMBER PRIMARY KEY, description VARCHAR2(100));

INSERT INTO deadlock_test_a VALUES (1, 'Row A');
INSERT INTO deadlock_test_b VALUES (1, 'Row B');

COMMIT;

SELECT * FROM deadlock_test_a;
SELECT * FROM deadlock_test_b;

Step 2 – Session 1 Locks Row A :

Purpose

To have Session 1 acquire a lock on deadlock_test_a.

SQL Command (Run in Session 1)

UPDATE deadlock_test_a SET description = 'Locked by Session 1' WHERE id = 1;

Important

Do NOT execute COMMIT or ROLLBACK.

Theoretical Explanation

• Session 1 now holds a TX lock on the row in deadlock_test_a.

• The lock is recorded in the block’s ITL (Interested Transaction List).

• The transaction remains uncommitted, so the lock persists.

Step 3 – Session 2 Locks Row B :

Purpose

To have Session 2 acquire a lock on deadlock_test_b.

SQL Command (Run in a New Session — Session 2)

UPDATE deadlock_test_b SET description = 'Locked by Session 2' WHERE id = 1;

Important

Do NOT execute COMMIT or ROLLBACK.

Theoretical Explanation

• Session 2 now holds a TX lock on the row in deadlock_test_b.

• At this point:

  • Session 1 holds a lock on Row A.

  • Session 2 holds a lock on Row B.

  • No deadlock yet—just two independent locks.

Step 4 – Session 1 Attempts to Lock Row B :

Purpose

To make Session 1 wait for a resource held by Session 2.

SQL Command (Run in Session 1)

UPDATE deadlock_test_b SET description = 'Session 1 trying Row B' WHERE id = 1;

Expected Behavior

• Session 1 will wait because Session 2 holds the lock on Row B.

• Session 1 enters the wait state with enq: TX - row lock contention.

Theoretical Explanation

At this point:

• Session 1 holds Row A and waits for Row B.

• Session 2 holds Row B and is idle (or active).

No deadlock yet, but the stage is set.

Step 5 – Session 2 Attempts to Lock Row A (Deadlock Created) :

Purpose

To complete the circular wait and trigger a deadlock.

SQL Command (Run in Session 2)

UPDATE deadlock_test_a SET description = 'Session 2 trying Row A' WHERE id = 1;

Expected Behavior

• Deadlock occurs immediately.

• Oracle detects the circular wait:

  • Session 1 waits for Row B (held by Session 2).

  • Session 2 waits for Row A (held by Session 1).

• Oracle chooses one session as the victim and rolls back its statement.

• The victim receives:

ORA-00060: deadlock detected while waiting for resource

• The other session proceeds successfully.

Theoretical Explanation

This is the classic deadlock scenario:

Session 1: Locks Row A → Waits for Row B
Session 2: Locks Row B → Waits for Row A
                    ↑__________________________|
                         Circular Wait

Oracle’s deadlock detection mechanism identifies the cycle and resolves it by rolling back one session.

Which Session Becomes the Victim?

Oracle chooses the victim based on the amount of work done:

• The session with less work (fewer undo records, less CPU) is rolled back.

• This minimizes the cost of rollback.

Step 6 – Identify the Deadlock :

Purpose :

To confirm the deadlock and identify the victim session.

6a – Check for ORA-00060 Error :

In the victim session, you will see:

ORA-00060: deadlock detected while waiting for resource

6b – Query V$SESSION for Deadlock Information :

SELECT sid, serial#, username, status, event, blocking_session, seconds_in_wait, sql_id FROM v$session WHERE blocking_session IS NOT NULL;

Expected Result :

After deadlock resolution, the blocking chain is broken. The victim session may show:

The other session proceeds.

6c – Check Alert Log :

SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

Navigate to the trace directory and look for the alert log. You will find entries like:

ORA-00060: deadlock detected. More info in file
/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12345.trc

Explanation :

The alert log records all deadlock occurrences. Each entry includes:

• Timestamp

• Session IDs involved

• Trace file location

Step 7 – Analyze the Deadlock Trace File :

Purpose :

To understand the exact sequence of events that led to the deadlock.

Trace File Contents (Simplified Example) :

*** 2024-10-01 10:15:30.123
*** SESSION ID:(25.12345) 2024-10-01 10:15:30.123
*** CLIENT ID:() 2024-10-01 10:15:30.123
*** SERVICE NAME:(orcl) 2024-10-01 10:15:30.123
*** MODULE NAME:(SQL Developer) 2024-10-01 10:15:30.123
*** ACTION NAME:() 2024-10-01 10:15:30.123

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0005001D-00001234         45      25     X             46      20           X
TX-0006001E-00005678         46      20     X             45      25           X

session 25: DID 0001-002D-00000002   session 20: DID 0001-002E-00000003
session 20: DID 0001-002E-00000003   session 25: DID 0001-002D-00000002

Rows waited on:
  Session 25: obj - rowid = 00012345 - AAA...
  Session 20: obj - rowid = 00012346 - BBB...

----- Information for the OTHER waiting sessions -----
Session 20:
  sid: 20 ser: 54321 audsid: 123456 user: 99/TESTUSER
  flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
  flags2: (0x40009) -/-/INC
  pid: 46 O/S info: user: user, term: PC-NAME, ospid: 1234
  client info: ...
  current SQL:
  UPDATE deadlock_test_a SET description = 'Session 2 trying Row A' WHERE id = 1

----- End of information for the OTHER waiting sessions -----

Information for THIS session:
  current SQL:
  UPDATE deadlock_test_b SET description = 'Session 1 trying Row B' WHERE id = 1

Key Sections of the Trace File :

Theoretical Explanation :

The trace file is the most detailed source of information for deadlock analysis. It shows:

• Which sessions were involved

• What resources they held and waited for

• The exact SQL statements executed

• The rowids of the rows involved

This information is invaluable for developers to fix the application logic that caused the deadlock.

Step 8 – Resolve the Deadlock

Automatic Resolution :

Oracle automatically resolves deadlocks by rolling back one session. No manual intervention is required for the deadlock itself.

Manual Resolution (If Needed) :

If the deadlock persists due to application retry logic, you may need to:

Option A – Rollback the Victim Session :

ROLLBACK;

Option B – Kill the Blocking Session :

ALTER SYSTEM KILL SESSION '20,54321' IMMEDIATE;

Option C – Fix the Application Logic :

The real solution is to modify the application to avoid deadlocks.

Theoretical Explanation :

Oracle’s automatic deadlock resolution ensures database consistency, but it does not fix the root cause. The application must be modified to prevent future deadlocks.

Step 9 – Verify Resolution :

Purpose :

To confirm that the deadlock has been resolved and no sessions are blocked.

SQL Command :

SELECT sid, serial#, username, status, event, blocking_session, seconds_in_wait FROM v$session
WHERE blocking_session IS NOT NULL;

Expected Result :

No rows returned (or only legitimate waits unrelated to the deadlock).

Additional Verification :

Check the alert log for any new deadlock entries:

SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

Navigate to the trace directory and check alert_orcl.log for recent ORA-00060 entries.

Step 10 – Prevent Future Deadlocks :

Best Practices :

Theoretical Explanation :

Deadlocks are a design problem, not a database problem. By ensuring that all sessions access resources in a consistent order, deadlocks can be largely eliminated.

Example: Consistent Lock Ordering :

Bad (Deadlock-Prone) :

Session 1: Lock A → Lock B
Session 2: Lock B → Lock A

Good (Deadlock-Free) :

Session 1: Lock A → Lock B
Session 2: Lock A → Lock B

Summary of Steps :

Quick Reference Queries :

Find Deadlock Victims :

SELECT sid, serial#, username, event, blocking_session FROM v$session WHERE event LIKE '%deadlock%';

Check Alert Log Location :

SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

Find Recent Deadlocks in AWR :

SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot WHERE snap_id IN (
SELECT snap_id FROM dba_hist_sysstat WHERE statistic_name = 'enqueue deadlocks');

Monitor Deadlock Statistics :

SELECT name, value FROM v$sysstat WHERE name LIKE '%deadlock%';

Check Enqueue Deadlocks :

SELECT * FROM v$lock WHERE type = 'TX' AND request > 0;

Difference Between Blocking and Deadlock (Summary)

Conclusion :

Deadlocks are a critical aspect of Oracle Database concurrency control. While Oracle automatically detects and resolves them, understanding their causes and prevention is essential for DBAs and developers.

By following the steps in this guide, you can:

• Create a controlled deadlock scenario for testing

• Detect deadlocks using V$SESSION, alert logs, and trace files

• Analyze deadlock traces to identify root causes

• Resolve deadlocks through rollback or session termination

• Prevent future deadlocks through best practices

The trace file generated during a deadlock is the most valuable diagnostic tool, providing detailed information about the circular wait, the SQL involved, and the rows locked.

Appendix — Deadlock Trace File Sections :

This guide is intended for Oracle Database administrators, developers, and anyone preparing for OCP/OCA certification or DBA interviews.