ORA-00060 Deadlock: Find It, Fix It, Prevent It

ORA-00060: deadlock detected while waiting for resource is one of the most misunderstood errors
Oracle throws. The two myths that cause the most damage: that it rolled back your transaction (it
didn't — just one statement), and that it's a database tuning problem you fix with a parameter (it
isn't — it's almost always an application bug). Oracle already broke the deadlock for you. Your job is
to read the trace it left, find the two statements that collided, and stop it happening again.

Here's exactly what the error means, how to read the deadlock graph, the handful of patterns that cause
deadlocks, and the fix for each.

The short version. A deadlock is a circular wait — session A holds a row B wants while B holds a
row A wants. Oracle detects it automatically (within a few seconds) and breaks it by rolling
back one statement of one session — the "victim" — which gets ORA-00060. The transaction
survives (everything before that statement is intact); the other session proceeds as if nothing
happened. The trace file holds a deadlock graph naming the two transactions, the rows, and the two
SQL statements. Mode-6 (X) deadlocks mean fix your app's lock order; mode-4 (S) deadlocks mean
look at the data structure (unindexed foreign key, ITL shortage, bitmap index).

What ORA-00060 actually does

When two sessions wait on each other's locks, neither can proceed — that's a deadlock. Oracle runs a
background detector that notices the wait-for cycle (typically within ~3 seconds) and breaks it
without any timeout or configuration from you. Precisely what happens:

• It rolls back one statement, not the transaction. Only the single DML that closed the cycle is undone. Everything that transaction did before it is still there, and still holds its locks.
• The transaction is not terminated. The session that gets ORA-00060 is still in an open transaction, holding a half-finished change. Oracle hands it back to you to decide: retry the statement, roll back to a savepoint, or roll back the whole thing.
• Only one session errors. The other session in the cycle gets its lock and continues normally — it never knows a deadlock occurred. Either session can be the victim; Oracle chooses.
• The application has to handle it. In PL/SQL, declare your own exception and bind it to the error with PRAGMA EXCEPTION_INIT(deadlock_detected, -60), then retry after a short backoff. (There is no built-in DEADLOCK_DETECTED exception — ORA-00060 isn't one of PL/SQL's predefined exceptions.)

So the single most common belief — "a deadlock rolled back my transaction" — is false. It rolled back
one statement and left the rest of your transaction (and its locks) in place, waiting for your code
to react.

Deadlock or just blocking? (the #1 confusion)

A stuck session looks the same whether it's deadlocked or merely blocked — but they're opposite
problems:

	Deadlock (ORA-00060)	Ordinary blocking
Shape	Circular — A waits on B and B waits on A	One-way — A waits on B only
Oracle's response	Auto-detects and breaks it in seconds	Does nothing — waits indefinitely
Ends when	Immediately — victim gets ORA-00060	The blocker commits or rolls back
Wait event	(transient, then the victim errors)	enq: TX - row lock contention
Error raised?	Yes — to the victim	No — just a hung session

The tell: a session parked on enq: TX - row lock contention is blocked, not deadlocked. It
will sit there forever until the holder commits, and it will never raise ORA-00060. Mutual wait =
deadlock; one-way wait = blocking. Don't go hunting for a deadlock graph when the real problem is one
long-running transaction holding a lock — that's a different fix (find and commit/kill the blocker).
The wait interface that surfaces that event is covered in Oracle Wait Events,
Decoded.

Reading the deadlock graph

ORA-00060 is a critical error, so Oracle writes a trace file and notes it in the alert log:

ORA-00060: Deadlock detected. ... More info in file
   /opt/oracle/diag/rdbms/.../trace/<sid>_ora_<pid>.trc

Open that trace and find the Deadlock graph — the whole diagnosis is in this one block:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0006001a-000004f2        19     137     X             24     159           X
TX-00030028-000003a1        24     159     X             19     137           X

Read it as the cycle it describes: session 137 holds a lock (mode X) that session 159 waits
for, and session 159 holds one that 137 waits for. The fields that matter:

• Resource name / enqueue type. TX-… is a transaction (row-level) enqueue — the usual case. TM-… is a table/DML enqueue — the fingerprint of the unindexed-foreign-key problem.
• holds / waits mode. X = exclusive (mode 6); S = share (mode 4 — which also covers the ITL, unique-key, and bitmap cases below); modes 3/5 (SX/SSX) show up in TM (table-lock) cases. This mode is your best clue to the cause.
• Rows waited on. Just below the graph, Oracle prints the exact rowid / object# each session was blocked on — which row, and which table.
• The two SQL statements. The trace then prints each session's current SQL statement — the two DMLs that crossed. That's the bug, named.

The mode is the shortcut: mode 6 (X) = a row-lock collision → fix the application's lock order;
mode 4 (S) = a structural problem → look at the data (a unique-key clash, an ITL shortage, a bitmap
index, or an unindexed FK).

The causes, and the fix for each

ORA-00060 triage: read the deadlock graph, let the enqueue type and lock mode point you at the cause, and fix the cause — not a parameter.

flowchart TD
  A([ORA-00060 in the alert log]) --> B[Open the trace<br/>read the Deadlock graph]
  B --> C{Enqueue type<br/>and mode?}
  C -- "TM (table lock)" --> D[Unindexed foreign key<br/>→ index the FK column]
  C -- "TX mode 6 (X)" --> E[Inconsistent update order<br/>→ lock rows in one consistent order]
  C -- "TX mode 4 (S)" --> F{Which structural cause?}
  F --> F1[Same unique/PK value inserted<br/>→ app logic / sequence]
  F --> F2[ITL shortage on a hot block<br/>→ raise INITRANS / PCTFREE]
  F --> F3[Bitmap index on an OLTP table<br/>→ use a B-tree index]

1. Inconsistent update order — the classic TX mode-6 deadlock. Session A updates row X then Y;
session B updates Y then X. Each holds an exclusive row lock the other wants. Fix: update rows in a
single, deterministic order everywhere (e.g., ascending by primary key) — if every code path locks in
the same order, a cycle is impossible. Batch jobs are the usual offenders; sort the working set before
the DML loop, and keep transactions short.

2. Unindexed foreign keys — the most famous Oracle deadlock cause. When a child table's foreign-key
column is not indexed and a session updates the parent's key, deletes a parent row, or merges into
the parent, Oracle takes a full-table lock on the child table (a TM share lock) — because without
the index it can't cheaply find the referencing rows. That coarse lock collides with other DML and
deadlocks, blocking even unrelated rows. (Note: plain inserts into the parent don't trigger it.)
Fix: put a plain B-tree index on the child's FK column. Oracle's own rule of thumb is that foreign
keys should almost always be indexed — the only exception is a parent key that's never updated or
deleted.

3. Bitmap indexes on OLTP tables — a single bitmap key entry covers many rows, so DML on one row
locks the whole entry, and two sessions updating different rows can collide. Fix: don't put bitmap
indexes on tables with concurrent DML — they're for low-cardinality, read-mostly warehouse data. Use a
B-tree for OLTP.

4. ITL / INITRANS shortage — every block has an Interested Transaction List with one slot per
concurrent transaction touching it; INITRANS sets the initial count (default 1 for tables, 2 for
indexes) and Oracle grows it only if the block has free space. On a hot block with no room to grow,
a transaction waits on the TX enqueue in mode 4 for a slot — and two such waits deadlock. Fix:
rebuild the hot segment with a higher INITRANS and/or PCTFREE, and spread the concurrency. (MAXTRANS
is deprecated — Oracle now allows up to 255 transactions per block automatically, space permitting — so
the lever is INITRANS/PCTFREE.)

5. Autonomous-transaction self-deadlock — a PRAGMA AUTONOMOUS_TRANSACTION routine tries to update a
row the calling transaction already locked. The autonomous transaction waits on the parent's lock, but
the parent is suspended waiting for the autonomous child to return — an unbreakable cycle. Fix: never
let an autonomous transaction touch rows its caller has locked; keep it to independent resources (a
separate logging table).

Prevention checklist

• Index every foreign key whose parent key can be updated or whose parent rows can be deleted. This kills the #1 source.
• Lock resources in one deterministic order (e.g., ascending PK) across the whole application.
• Keep transactions short and commit promptly — don't hold locks across user think-time or remote calls.
• No bitmap indexes on OLTP tables — B-tree instead.
• Raise INITRANS/PCTFREE on hot blocks prone to ITL contention.
• Audit explicit locking — SELECT … FOR UPDATE, LOCK TABLE, and ORM pessimistic locking are where most "explicit override" deadlocks live.
• Make the app retry on ORA-00060 — a deterministic retry after a short backoff resolves the transient cases cleanly. Then read the trace to fix the root pattern.

Want to see one for real? The deadlock lab
induces an actual ORA-00060 on Oracle Database Free — two sessions lock the same two rows in opposite
order — and prints the deadlock graph straight from the trace. Then a fixed drill runs the same
workload in a consistent order so the deadlock never forms. One command each; no Diagnostics Pack.

What teams get wrong

• "The deadlock rolled back my transaction." It rolled back one statement. The transaction is still open, holding its earlier work and locks — your code has to react.
• Confusing a deadlock with blocking. A session on enq: TX - row lock contention is blocked, not deadlocked — it'll wait forever, and no ORA-00060 is coming. Find the blocker; don't hunt a graph.
• Trying to "tune" it away. There's no parameter that fixes a deadlock. The graph names the two SQL statements; the fix is in the application (lock order) or the schema (index that FK).
• Ignoring the lock mode. Mode 6 vs mode 4 tells you whether the bug is your lock order or your data structure. Read it before you guess.

Where this fits

Deadlocks are a concurrency problem, and concurrency shows up in the performance picture — when one is
the headline in your report, you've found it through the wait interface and the AWR Top Events. Name the
event, read the graph, follow it to the SQL: the same method as How to Read an AWR Report Without
Drowning and Oracle Wait Events,
Decoded, applied to the one error that diagnoses itself if you let it.

FAQ

What does ORA-00060 mean?

ORA-00060, "deadlock detected while waiting for resource," means two or more sessions were each holding a lock the other needed — a circular wait. Oracle automatically detected the deadlock and broke it by rolling back one statement of one session (the victim), which receives the ORA-00060 error. The other session proceeds normally. It is almost always caused by an application locking pattern, not by a database misconfiguration.

Does ORA-00060 roll back my whole transaction?

No. Oracle rolls back only the single statement that closed the deadlock cycle, not the entire transaction. The session that receives ORA-00060 is still in an open transaction with all of its prior work and locks intact. You decide what to do next: retry the statement, roll back to a savepoint, or roll back the whole transaction. The application should handle the error and typically retry after a short delay.

What is the difference between a deadlock and blocking in Oracle?

A deadlock is a circular wait — session A waits on B while B waits on A — and Oracle detects and breaks it automatically within seconds, raising ORA-00060 to one victim. Ordinary blocking is one-directional — A waits on B only — and Oracle does nothing about it; the waiting session sits on the wait event "enq: TX - row lock contention" indefinitely until the blocker commits or rolls back, and never raises ORA-00060. A session on enq: TX - row lock contention is blocked, not deadlocked.

How do I find which SQL caused an ORA-00060 deadlock?

Oracle writes a trace file when the deadlock occurs and records its path in the alert log. Open that trace and find the "Deadlock graph" section: it lists the two transactions (as TX or TM enqueues), the lock modes each holds and waits for, the rows each session was waiting on, and — printed just below — the current SQL statement of each session. Those two statements are the colliding DML; that is the bug to fix.

What causes Oracle deadlocks?

The common causes are: inconsistent update ordering (two sessions updating the same rows in opposite order — a TX mode-6 deadlock); unindexed foreign keys (a parent-key update, delete, or merge takes a full-table lock on the child table when the FK column is not indexed); bitmap indexes on tables with concurrent DML; an ITL/INITRANS shortage on a hot block (a TX mode-4 deadlock); and autonomous transactions that touch rows locked by their caller.

How do unindexed foreign keys cause deadlocks?

When a child table's foreign-key column is not indexed and a session updates the parent table's key, deletes a parent row, or merges into the parent, Oracle cannot cheaply identify the referencing child rows, so it locks the entire child table with a table-level (TM) share lock to protect integrity. That coarse lock blocks other DML on the child table — including unrelated rows — and collides with concurrent transactions, producing deadlocks. Indexing the foreign-key column lets Oracle avoid the full-table lock.

How do I prevent ORA-00060 deadlocks?

Index every foreign key whose parent key can be updated or whose rows can be deleted; lock rows in a single deterministic order across the whole application (for example ascending by primary key); keep transactions short and commit promptly; avoid bitmap indexes on OLTP tables; raise INITRANS/PCTFREE on hot blocks prone to ITL contention; audit explicit locking such as SELECT ... FOR UPDATE; and make the application catch ORA-00060 and retry after a short backoff.

Should my application retry after ORA-00060?

Yes. Because Oracle rolls back only one statement and leaves the transaction open, a deadlock is often transient — a deterministic retry after a brief randomized backoff resolves it cleanly. In PL/SQL, associate the error with a named exception using PRAGMA EXCEPTION_INIT(my_deadlock, -60) and handle it. Retrying is a safety net, not a cure: still read the deadlock graph and fix the underlying lock-order or schema problem so it stops recurring.

Originally published at uptimearchitect.com. I write here in a personal capacity — questions or feedback are welcome via the contact page.