Oracle ORA-00060 deadlock detected: read the trace file and fix the real cause

#Symptom

An application gets:

ORA-00060: deadlock detected while waiting for resource

and the alert log shows:

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/prod/PROD1/trace/PROD1_ora_12345.trc.

Oracle resolves the deadlock itself by rolling back one statement (not the whole transaction) in one session. The application must handle the error, usually by rolling back and retrying. The fix is to stop the deadlock happening.

#Step 1: find the trace

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

Or with ADRCI:

adrci> show homes
adrci> set home diag/rdbms/prod/PROD1
adrci> show alert -p "message_text like '%ORA-00060%'"

#Step 2: read the deadlock graph

The top of the trace has a graph like this:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0006001a-00000c3e        25     133     X             30     150           X
TX-000a0012-00001b21        30     150     X             25     133           X

Below it you'll find the SQL each session was running, and "Rows waited on" with the object and rowid.

The lock mode tells you which kind of deadlock it is:

#Cause 1: application row order (TX, mode X / X)

Session A updates order 1 then order 2; session B updates order 2 then order 1. Fix in the application:

• Always lock rows in a consistent order (for example by primary key).
• Lock all needed rows upfront: SELECT ... FOR UPDATE in key order.
• Keep transactions short; don't wait for user input or external calls while holding locks.

#Cause 2: unindexed foreign keys (TM locks)

When a parent row is deleted or its key updated, Oracle needs to check the child table. Without an index on the child's foreign key column, it takes a table-level lock on the child, and concurrent DML on parent and child deadlocks.

Find foreign keys whose leading columns aren't 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 = 'APP'
  AND NOT EXISTS (
    SELECT 1 FROM dba_ind_columns ic
    WHERE ic.table_owner = cc.owner
      AND ic.table_name  = cc.table_name
      AND ic.column_name = cc.column_name
      AND ic.column_position = cc.position)
ORDER BY c.table_name, cc.position;

Create the missing indexes (online, outside peak hours):

CREATE INDEX app.order_lines_order_id_ix ON app.order_lines (order_id) ONLINE;

#Cause 3: ITL shortage (TX, mode S)

Each block has a small list of "interested transaction" slots. When many sessions update different rows in the same block and the block is full, a session waits in mode S for a slot. Two such waits can deadlock.

Check for ITL waits:

SELECT owner, object_name, value
FROM v$segment_statistics
WHERE statistic_name = 'ITL waits' AND value > 0
ORDER BY value DESC FETCH FIRST 10 ROWS ONLY;

Fix by raising INITRANS and rebuilding so existing blocks get the new setting:

ALTER TABLE app.stock INITRANS 10;
ALTER TABLE app.stock MOVE ONLINE;           -- rebuilds blocks with the new INITRANS
ALTER INDEX app.stock_pk REBUILD INITRANS 10 ONLINE;

ALTER TABLE ... MOVE ONLINE needs 12.2 or later; plan space and time for large tables.

#Cause 4: bitmap indexes on OLTP tables

Bitmap index entries cover many rows, so concurrent DML on different rows locks the same bitmap piece. Deadlocks with mode S appear under concurrency. Bitmap indexes belong on read-mostly data warehouse tables; replace them with B-tree indexes on OLTP tables.

#Cause 5: primary/unique key insert collisions

Two sessions inserting the same key values in different orders (for example, batch loads with overlapping keys) wait on each other in mode S. Fix by partitioning the work so sessions don't insert overlapping keys, or by ordering inserts.

#RAC note

In RAC, deadlock detection across instances happens via LMD processes and can take a little longer. The trace is written by LMD on one instance; check all instances' trace directories.

#Prevent it next time

• Index every foreign key column used in parent deletes/updates as a standard.
• Alert on ORA-00060 in the alert log and collect the trace automatically.
• Make the application retry on ORA-00060 after a rollback, with a short random delay.
• Related: ORA-01555 snapshot too old.