Oracle Deadlock Resolution: Trace Graph Analysis, v$wait_chains & Unindexed Foreign Key Locks

Root cause analysis of cross-dependency deadlocks and missing foreign key index table locks via trace logs and serialized DML execution.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: Oracle Database 19c / 21c / 23ai

Operational Verification & Reliability Metadata

Last Technical Review: 2 Kasım 2026

Tested VersionsOracle 19c Enterprise Edition, 23ai Free

Required PrivilegesSELECT on v$diag_info, v$session, v$wait_chains

Risk Assessment

HighOracle automatically selects one session as deadlock victim, terminating its statement

Expected OutputDeadlock graph identified in trace file and unindexed foreign keys indexed

Rollback Procedure:Implement serialized updates and application retry logic

Oracle Docs: Deadlocks in Oracle Database

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

Application transactions abort abruptly with deadlock notifications:

plaintext

ORA-00060: deadlock detected while waiting for resource

▸What You See in Production

Two concurrent sessions block each other in a cyclic wait. Oracle detects the deadlock cycle, chooses a victim session, and rolls back the statement while writing a trace graph to diag.

▸Root Cause Matrix

Probability	Root Cause	Description
%55	Inverted Locking Order	Session A locks X then Y; Session B locks Y then X.
%35	Unindexed Foreign Keys	Child tables without indexes on FK columns force entire table locks during parent updates.
%10	Bitmap Indexes on OLTP	Bitmap indexing on high-concurrency tables locking index blocks.

▸EMERGENCY RUNBOOK (0-5 min)

sql

-- 1. Locate trace file
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- 2. Find unindexed foreign keys
SELECT c.table_name, c.constraint_name, cc.column_name
FROM user_constraints c
JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
WHERE c.constraint_type = 'R'
  AND NOT EXISTS (
    SELECT 1 FROM user_ind_columns ic 
    WHERE ic.table_name = c.table_name AND ic.column_name = cc.column_name
  );

▸PERMANENT BLUEPRINT

1Index All Foreign Keys: Create B-Tree indexes on all foreign key constraint columns.
2Deterministic Locking Order: Enforce global sorting (ORDER BY id) before acquiring row locks.
3Use NOWAIT: Use SELECT FOR UPDATE NOWAIT to fail fast.

▸Verification

sql

SELECT * FROM v$wait_chains WHERE blocking_session IS NOT NULL;

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (82)

ORACLE6 min

Oracle ORA-01653: Tablespace Extension & Step-by-Step Fix

Distinguishing ORA-01653, 01654, and 01652, tablespace capacity monitoring, datafile autoextend, and safe sizing with MAXSIZE.

ORACLE3 min

Oracle ORA-00001: unique constraint violated Triage & Fix

Troubleshooting unique key collisions caused by out-of-sync sequences and bulk imports; sequence fast-forwarding and DML error logging.

ORACLE10 min

Oracle ORA-01555: snapshot too old Triage & Permanent Mitigation

Diagnosing premature undo overwrite during long-running batch queries, analyzing v$undostat trends, and tuning UNDO_RETENTION with tablespace autoextend.