Deadlock analysis

Symptom

'deadlock detected', 'ORA-00060', 'chosen as deadlock victim' — transactions lock each other and one is aborted.

1. Deadlocks are normal, but should not be frequent

The DB detects the deadlock and resolves it by choosing a victim — this is by design. The goal is to reduce the frequency. The application should retry the victim transaction. First capture the deadlock graph.

2. Get the deadlock graph/details

Which two (or more) sessions, which resources and which lock modes conflicted?

Oracle

-- Check the trace file referenced in the alert log (deadlock graph). Also:
SELECT * FROM dba_hist_active_sess_history WHERE event LIKE 'enq: TX%' ORDER BY sample_time DESC;

PostgreSQL

-- log_lock_waits=on; deadlock details are in postgresql.log. Check the deadlock_timeout setting.

MySQL

SHOW ENGINE INNODB STATUS\G  -- LATEST DETECTED DEADLOCK section

SQL Server

-- Deadlock XML in the system_health Extended Events session (ring_buffer, recent events):
SELECT x.ev.value('@timestamp','datetime2') AS event_time, x.ev.query('.') AS deadlock_xml
FROM (SELECT CAST(t.target_data AS XML) td FROM sys.dm_xe_session_targets t
      JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
      WHERE s.name = N'system_health' AND t.target_name = N'ring_buffer') r
CROSS APPLY r.td.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(ev);
-- For longer history, the event_file target:
-- SELECT CAST(event_data AS XML) FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) WHERE object_name = N'xml_deadlock_report';

3. Resolve the conflict pattern

The most common cause: two transactions accessing resources in OPPOSITE order. Access resources in the SAME order in all code paths. The second most common cause: wide range locks due to a missing index — add the required index.

4. Reduce lock duration and scope

Keep transactions short (do not include user interaction inside a transaction). Choose an appropriate isolation level. When needed, acquire row locks early (SELECT ... FOR UPDATE with deterministic ordering).

5. Retry in the application is mandatory

Deadlocks cannot be eliminated completely; implement automatic retry (exponential backoff) for the victim transaction. This keeps the user from seeing an error.