$ guides / oracle-database / oracle-database-ora-00060-deadlock-detected ▌

Operations Guides

ORA-00060: deadlock detected while waiting for resource

ORA-00060 fires when Oracle’s background deadlock detector finds a wait cycle among sessions competing for enqueues. By the time you see it, Oracle has already resolved it: one session was picked as the victim, its current statement was rolled back, and the other sessions in the cycle continued. The victim session is still connected and its transaction is still open. The application must commit or roll it back.

The work is finding which sessions deadlocked, what SQL each was running, and why their lock ordering conflicted. Oracle’s trace file states this explicitly: ORA-00060 is a user application design issue, not an Oracle error. The fix lives in the application or the schema, not in a database parameter.

What this means

Oracle serializes access with row-level locks (TX enqueues) and table-level locks (TM enqueues). A deadlock is a circular wait: two or more sessions each hold a resource the other needs, so neither can progress. The internal deadlock detector runs in the background and typically breaks cycles within about 3 seconds.

When it fires, Oracle rolls back one session’s current statement (not the whole transaction) and raises ORA-00060 to that session. The other sessions in the cycle continue. The victim’s application must decide whether to retry, roll back, or surface the error to the user.

Do not confuse this with a long lock chain. A long lock chain is one session blocking many others by holding locks without committing; its signals are growing enq: TX - row lock contention waits and rising BLOCKING_SESSION chains. A deadlock is specifically a circular wait that Oracle had to break.

The classic two-session row lock deadlock:

sequenceDiagram
    participant A as Session A
    participant DB as Oracle Database
    participant B as Session B
    A->>DB: UPDATE accounts SET bal=100 WHERE id=1
    Note over DB: Row 1 locked by A (TX)
    B->>DB: UPDATE accounts SET bal=200 WHERE id=2
    Note over DB: Row 2 locked by B (TX)
    A->>DB: UPDATE accounts SET bal=300 WHERE id=2
    Note over A: Waits for B
    B->>DB: UPDATE accounts SET bal=400 WHERE id=1
    Note over B: Waits for A (cycle)
    Note over DB: Deadlock detector fires (~3s)
    DB-->>A: ORA-00060 (statement rolled back)
    Note over B: Transaction continues

Common causes

Cause	What it looks like	First thing to check
Inconsistent row lock ordering	TX enqueue deadlock in the trace graph; two sessions updating the same rows in different order	Rowids and SQL text in the deadlock graph
Unindexed foreign key	TM enqueue deadlock; parent DELETE or UPDATE triggers a child table lock	Whether FK columns have a matching index
ITL slot exhaustion	TX enqueue with “allocate ITL entry” in the wait event; high concurrency on the same block	INITRANS on the segment
Different execution plans on same DML	Two sessions running the same SQL deadlock because different index access paths lock rows in different order	Capture both `PLAN_HASH_VALUE` values and reproduce in the target release/patch level

This pattern is not guaranteed to reproduce identically across every 19c+ patch level; retain the trace evidence and repeat the two-session test in the affected environment before treating it as the confirmed cause.

Inconsistent row lock ordering is the textbook deadlock. Unindexed foreign keys are the most frequently missed schema issue. ITL slot exhaustion is rare except on extremely hot blocks.

Quick checks

All read-only, safe to run during an active incident:

# Find recent ORA-00060 entries and the trace file each references
adrci exec="show alert -tail 200" | grep -A5 "ORA-00060"

# List recent incidents and the trace files they produced
adrci exec="show incident -mode basic -orderby created desc"

-- Cumulative deadlock count since instance startup.
-- Sample twice with an interval to compute a rate.
SELECT ss.value
FROM v$sysstat ss
JOIN v$statname sn USING (statistic#)
WHERE sn.name = 'enqueue deadlocks';

-- Current enqueue waiters. The deadlock is already resolved, but this catches the
-- long lock chains that often coexist with the deadlock-prone workload.
SELECT s.sid, s.serial#, s.username, s.event, s.seconds_in_wait,
       s.sql_id, s.blocking_session
FROM v$session s
WHERE s.event LIKE 'enq:%' AND s.state = 'WAITING'
ORDER BY s.seconds_in_wait DESC;

-- Unindexed single-column foreign keys on tables involved in TM deadlocks.
-- Multi-column FKs need a separate check on index column ordering.
SELECT c.owner, c.table_name, c.constraint_name, cc.column_name
FROM dba_constraints c
JOIN dba_cons_columns cc
  ON c.owner = cc.owner
 AND c.constraint_name = cc.constraint_name
WHERE c.constraint_type = 'R'
  AND c.owner = '&schema_owner'
  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
  )
ORDER BY c.table_name;

For a multi-column FK, the useful index must lead with the FK columns (in any order). The query above only checks whether each individual FK column appears in some index; keep it as a conservative screening query, then inspect DBA_IND_COLUMNS for one composite index that covers the FK columns.

-- Current blocking chain depth
SELECT blocking_session, sid, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

How to diagnose it

Find the trace file. The alert log entry for ORA-00060 prints a Trace file: line with the absolute path. The default location is $ORACLE_BASE/diag/rdbms/<db_unique_name>/<instance_name>/trace/ with a filename like <instance>_ora_<pid>.trc. adrci exec="show incident -mode basic" also lists incidents and their trace files.
Read the deadlock graph. The trace contains a Deadlock graph section showing the blocker and waiter sessions, resource type (TX or TM enqueue), lock mode, and the rowids of the contended rows. Above the graph there is boilerplate stating the deadlock is not an Oracle error but a user application design issue. Read the graph, not the boilerplate.
Classify the enqueue type. This determines the fix:
- TX enqueue with row lock contention: inconsistent lock ordering across transactions.
- TX enqueue with allocate ITL entry: insufficient ITL slots on a hot block.
- TM enqueue: unindexed foreign key producing a child table lock during parent DML.
Extract the SQL text. The trace usually prints the statement each session was executing when the deadlock fired. If the text is truncated, pull the full statement from v$sql using the SQL_ID from the trace or from v$session.sql_id. Capture both SQL_ID and PLAN_HASH_VALUE for each side of the graph.
Map rowids to objects. For TX enqueues, the graph shows rowids. The trace usually prints the object name alongside each rowid. If it does not, use DBMS_ROWID.ROWID_OBJECT to resolve the object and query the suspect table by rowid to confirm the row.
Reconstruct the lock ordering. For the inconsistent-ordering case, list the sequence of DML statements each session ran before the deadlock. The fix is to make all transactions touching that row set acquire locks in the same order.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`enqueue deadlocks` (v$sysstat)	Cumulative deadlocks since instance startup	Any non-zero delta over the sampling interval
Alert log ORA-00060 entries	Each entry points to a trace file with the full deadlock graph	Any occurrence in production
`enq: TX - row lock contention` waits	Lock waits that are not (yet) deadlocks	Growing total wait time or session count in this wait
`enq: TM - contention` waits	Table-level lock from an unindexed FK	Any occurrence on an OLTP system
`v$session BLOCKING_SESSION` chains	Current lock dependency chains	Chains deeper than 2-3 levels or growing
TPS (user commits + rollbacks)	Throughput impact from lock waits	Sustained drop correlating with enqueue waits

enqueue deadlocks is cumulative since startup. Sample twice and compute the delta to get a rate. A non-zero rate means deadlocks are actively occurring and the application bug is not fixed.

Fixes

Inconsistent row lock ordering (TX enqueue)

Classic deadlock: Session A locks row 1 then row 2; Session B locks row 2 then row 1. Neither can proceed.

The fix is in the application code. All transactions touching the same row set must acquire locks in the same order. Common patterns:

Sort the rows to be updated by primary key before issuing DML, so all sessions lock in the same sequence.
Use a deterministic order when updating parent and child rows in the same transaction.
Remove code paths that update the same rows in different orders under different entry points.

No database parameter fixes this. It requires a code change.

Unindexed foreign keys (TM enqueue)

Without an index on the FK column, Oracle takes a table-level lock (TM enqueue) on the child table during DELETE or UPDATE on the parent. Two concurrent sessions operating on parent and child rows can then deadlock.

Add an index on the FK column(s):

-- Add an index on the FK column
CREATE INDEX idx_child_fk ON child_table(fk_column_id)
  TABLESPACE users;

Check every FK constraint in the schema for a matching index. This is the most common schema-level fix for TM enqueue deadlocks.

ITL slot exhaustion (TX enqueue, allocate ITL entry)

Each data block header has an Interested Transaction List (ITL) with a fixed number of slots, controlled by INITRANS. When more concurrent transactions than available ITL slots try to modify the same block, sessions wait to allocate an ITL entry. Under extreme contention on a single hot block, this can form a deadlock cycle.

Increase INITRANS and rebuild the segment so existing blocks pick up the new setting:

-- ONLINE allows DML during the move but still has documented restrictions; verify locks and plan it as maintenance.
-- After any MOVE, check DBA_INDEXES.STATUS. Offline moves commonly make indexes UNUSABLE;
-- online moves may retain usable indexes or support UPDATE INDEXES, depending on release/object type.
ALTER TABLE hot_table INITRANS 16;
ALTER TABLE hot_table MOVE ONLINE;
-- Rebuild only indexes that DBA_INDEXES.STATUS shows as UNUSABLE.
ALTER INDEX hot_table_pk REBUILD ONLINE;

Oracle documents ALTER TABLE ... MOVE ONLINE without a blanket edition restriction in the 19c SQL reference, but object type, partitioning, licensing, and patch-level behavior can affect an individual case. Confirm the exact feature and license position before production use.

New blocks created after the change use the new INITRANS automatically. Existing blocks only get the benefit after a rebuild.

Application-level retry

ORA-00060 is designed to be retryable: the victim statement was rolled back, but the session and its transaction are still alive. The application can catch ORA-00060, roll back the transaction, and retry the operation.

Retry is a safety net, not a fix. A high retry rate wastes resources, increases latency, and can itself cause further contention. Fix the root cause first. Use retry to absorb the occasional unavoidable deadlock during batch and OLTP overlap.

Prevention

Index every foreign key. Eliminates the most common TM enqueue deadlock source. Make it a schema review checklist item for any new FK constraint.
Review lock ordering in transactional code. Any code path that updates multiple rows in a single transaction must use a deterministic order, typically sorted by primary key.
Set INITRANS on hot tables at creation time. Raising it later requires a rebuild.
Monitor the enqueue deadlocks counter. Sample periodically and compute the rate. Any non-zero rate warrants investigation, even if the application retries transparently.
Use SQL Plan Baselines for critical DML. If divergent execution plans on the same SQL can produce different row lock orders, DBMS_SPM prevents the plan divergence.

How Netdata helps

Deadlock counter trending. Netdata collects the enqueue deadlocks statistic from v$sysstat at per-second granularity. A spike or sustained non-zero rate surfaces immediately, even if the application silently retries.
Correlation with enqueue waits. Pair the deadlock counter with enq: TX and enq: TM wait event trends to distinguish a one-off deadlock from a systemic locking problem.
Throughput context. Correlate deadlocks with TPS and active session count to tell whether retries are absorbing the impact or whether users are seeing latency.
Alert log integration. ORA-00060 entries in the alert log include the trace file reference. Surfacing alert log errors alongside metric anomalies speeds triage.

The Netdata solution

Oracle Database monitoring with Netdata

Netdata monitors Oracle Database with per-second metrics and automatic dashboards. Watch wait events, redo and archive-log activity, tablespace and undo space, and session and lock activity so the failure modes in these runbooks surface before the instance hangs.

See Oracle monitoring → Start monitoring free