Deadlock / lock conflict (ORA-00060, 1213, 40P01)

You hit a deadlock (Oracle ORA-00060, MySQL 1213, PG 40P01, MSSQL 1205). Capture the graph, work out the pattern, prevent recurrence. Deadlocks are resolved by design — the goal is to reduce their frequency.

First capture the deadlock graph

Which two (or more) sessions, which resources, which lock modes collided? Do not apply blind fixes without seeing this.

-- Check the trace file referenced in the alert log (deadlock graph). For history:
SELECT * FROM dba_hist_active_sess_history WHERE event LIKE 'enq: TX%' ORDER BY sample_time DESC FETCH FIRST 20 ROWS ONLY;

• Got the graph — two transactions lock resources in reverse order
• Many transactions pile up on a single row/table (hotspot)
• There are wide range locks (many rows get locked)

Does the locking query use an index?

Without an index, an UPDATE/DELETE locks many rows (even the whole table) to satisfy the WHERE clause → a source of deadlocks. Check its plan.

EXPLAIN <the UPDATE/DELETE involved in the deadlock>;  -- type=ALL means a full scan, no index

• No index / it does a full scan
• There is an index, but it still conflicts

Root cause: reverse lock order

The most common cause of deadlock: two transactions access resources in different orders. When A→B and B→A happen at the same time, they deadlock.

Root cause: missing index → wide locks

An UPDATE/DELETE without an index locks more rows than necessary and makes deadlocks far more likely.

Root cause: hotspot

Many transactions pile up at once on a single resource such as a counter row/key.