ORA-00060 Deadlock — Read the Oracle Trace File Like a Senior DBA

Published Aug 2026 · 6 min read

Your phone goes off at 2 a.m. The application team says orders are failing with ORA-00060: deadlock detected while waiting for resource. They want to know if the database is broken.

It isn't. A deadlock is Oracle doing its job — it spotted two sessions waiting on each other forever and broke the tie. The real work is reading the trace file it left behind, and that file tells you exactly which two statements collided and why. Here is how to read it properly.

What Oracle actually does when it detects a deadlock

Two things surprise people, and both matter when you talk to the application team.

Oracle rolls back one statement, not the whole transaction. The victim session gets ORA-00060, but its transaction is still open and still holds every lock it acquired before the failing statement. If the application catches the error and carries on without a ROLLBACK, those locks stay held and you get a second incident minutes later. This is the single most common reason a "fixed" deadlock keeps coming back.

A deadlock is an application design symptom, not a resource shortage. Adding CPU, memory or a bigger undo tablespace changes nothing. Two sessions took the same locks in a different order, or a missing index widened a lock far more than the developer intended.

Find the trace file

Every deadlock writes a trace file and an alert log entry. Start at the alert log — it names the file for you:

-- Where is the diagnostic destination?
SELECT name, value
FROM   v$diag_info
WHERE  name IN ('Diag Trace','Default Trace File');

-- Recent deadlocks straight from the alert log via ADRCI
adrci> set homepath diag/rdbms/orcl/orcl
adrci> show alert -tail 200 -term | grep -i "ORA-00060"

The alert log line looks like this, and the filename is the part you want:

ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 errors.
More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18244.trc

On a busy system you may have dozens. Sort by time and take the one matching the incident window rather than the newest.

Read the deadlock graph

Open the trace and find the block that starts with DEADLOCK DETECTED. The graph underneath is the whole diagnosis in nine lines:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090016-00002a1b        42     131     X             55     208           X
TX-000a0021-00001f04        55     208     X             42     131           X

session 131: DID 0001-002A-00000B12   session 208: DID 0001-0037-000004C7
session 208: DID 0001-0037-000004C7   session 131: DID 0001-002A-00000B12

Rows waited on:
  Session 131: obj - rowid = 0001A2F4 - AAAaL0AAEAAAAFrAAB
  Session 208: obj - rowid = 0001A2F4 - AAAaL0AAEAAAAFrAAC

Read it as a cycle. Session 131 holds a lock that 208 wants; 208 holds one that 131 wants. Three details decide everything that follows:

• Resource type — TX is a transaction (row-level) enqueue. TM is a table-level DML enqueue. Which one appears changes the diagnosis completely.
• Lock mode — X (mode 6, exclusive) versus S (mode 4, share). An S mode wait on a TX enqueue is a strong hint that no ordinary row conflict is involved.
• Rows waited on — the obj value is the object id. Resolve it and you know the table.

-- Which table were they fighting over?
SELECT owner, object_name, object_type
FROM   dba_objects
WHERE  object_id = TO_NUMBER('0001A2F4','XXXXXXXX');

Further down, the trace prints Current SQL statement for this session and then Information for the OTHER waiting sessions with their SQL. Those two statements are your culprits. Do not skip past them to the stack trace — the SQL is the answer.

The three patterns, and what each one means

1. TX enqueue, both sides mode X — classic ordering deadlock

This is the textbook case and roughly 80% of what you will see. Two sessions update the same two rows in opposite order:

-- Session A                       -- Session B
UPDATE accounts SET bal = bal-100  UPDATE accounts SET bal = bal-50
 WHERE id = 1;                      WHERE id = 2;
UPDATE accounts SET bal = bal+100  UPDATE accounts SET bal = bal+50
 WHERE id = 2;   <-- waits          WHERE id = 1;   <-- waits, deadlock

Fix: make the application touch rows in a deterministic order — ascending primary key is the usual choice. This is a code change; no database setting avoids it. Where you cannot change the code, SELECT ... FOR UPDATE on the full row set up front, in a fixed order, turns the deadlock into an ordinary wait.

2. TX enqueue, waiter in mode S — index or constraint related

A share-mode wait on a TX enqueue almost never means a plain row conflict. The usual causes are a unique index collision (two sessions inserting the same key before either commits), an ITL shortage on a heavily concurrent block, or a bitmap index being updated concurrently.

Fix: for ITL shortage, raise INITRANS on the affected segment and rebuild. For unique key collisions, have the application check-then-insert inside a single statement, or handle DUP_VAL_ON_INDEX. Bitmap indexes simply do not belong on tables with concurrent DML.

3. TM enqueue — the unindexed foreign key

If the graph shows a TM resource, stop looking at the application logic and look for a foreign key with no index on the child column. Deleting or updating a parent key takes a lock on the whole child table when that index is missing, so two unrelated sessions collide.

-- Find foreign keys whose child columns are not indexed
SELECT c.owner, c.table_name, c.constraint_name, cc.column_name
FROM   dba_constraints c
JOIN   dba_cons_columns cc
       ON cc.owner = c.owner
      AND cc.constraint_name = c.constraint_name
WHERE  c.constraint_type = 'R'
AND    c.owner = 'APPUSER'
AND    NOT EXISTS (
         SELECT 1 FROM dba_ind_columns ic
         WHERE  ic.table_owner = c.owner
         AND    ic.table_name  = c.table_name
         AND    ic.column_name = cc.column_name
         AND    ic.column_position = cc.position)
ORDER BY c.table_name;

Fix: index the child column. This one is a genuinely free win — it removes the deadlock and usually speeds up the parent delete as well.

Reproduce it in a lab before you change production

You want to see the trace file appear on demand. Two SQL*Plus sessions and a scratch table are enough:

-- setup (once)
CREATE TABLE dl_demo (id NUMBER PRIMARY KEY, val NUMBER);
INSERT INTO dl_demo VALUES (1,100);
INSERT INTO dl_demo VALUES (2,200);
COMMIT;

-- session 1              -- session 2
UPDATE dl_demo SET val=val-1 WHERE id=1;
                          UPDATE dl_demo SET val=val-1 WHERE id=2;
UPDATE dl_demo SET val=val-1 WHERE id=2;   -- blocks
                          UPDATE dl_demo SET val=val-1 WHERE id=1;   -- ORA-00060

Roughly three seconds later one session raises ORA-00060 and a trace file lands in the trace directory. Practise reading that graph when nothing is on fire — it is a very different experience at 2 a.m.

What to check first, in order

1. Confirm from the alert log that it really was ORA-00060 and not a plain enq: TX - row lock contention wait, which is a different problem.
2. Open the trace and read the deadlock graph — resource type and lock mode.
3. Resolve the object id to a table name.
4. Read both SQL statements printed in the trace.
5. If TM: check for unindexed foreign keys on that table.
6. If TX mode S: check INITRANS and unique indexes.
7. If TX mode X on both sides: it is statement ordering — hand it to the developers with both statements attached.
8. Verify the application issues a ROLLBACK when it catches ORA-00060.

That last point catches more repeat incidents than any of the technical fixes above.

Where this fits in real DBA work

Deadlock analysis is one of those skills that separates someone who restarts the application from someone who tells the development team which two statements to reorder. It sits alongside lock contention, latch waits and undo behaviour — the concurrency layer of Oracle that rarely appears in tutorials but comes up in production and in interviews constantly.

We work through this material with live trace files in the Oracle DBA live training, and the same troubleshooting module is part of the recorded course library if you would rather go at your own pace. Oracle's own reference is ORA-00060 in the Error Messages guide.