Анализ взаимоблокировок

Симптом

'deadlock detected', 'ORA-00060', 'chosen as deadlock victim' — транзакции блокируют друг друга, и одна из них прерывается.

1. Взаимоблокировки нормальны, но не должны быть частыми

БД обнаруживает взаимоблокировку и разрешает её, выбирая жертву — так задумано. Цель — снизить частоту. Приложение должно повторять транзакцию-жертву. Сначала получите граф взаимоблокировки.

2. Получите граф/детали взаимоблокировки

Какие две (или более) сессии, какие ресурсы и какие режимы блокировки конфликтовали?

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. Устраните паттерн конфликта

Самая частая причина: две транзакции обращаются к ресурсам в ПРОТИВОПОЛОЖНОМ порядке. Обращайтесь к ресурсам в ОДНОМ И ТОМ ЖЕ порядке во всех ветках кода. Вторая по частоте причина: широкие блокировки диапазонов из-за отсутствующего индекса — добавьте нужный индекс.

4. Сократите длительность и область блокировок

Держите транзакции короткими (не включайте взаимодействие с пользователем в транзакцию). Выберите подходящий уровень изоляции. При необходимости захватывайте блокировки строк заранее (SELECT ... FOR UPDATE с детерминированным порядком).

5. Повтор в приложении обязателен

Полностью исключить взаимоблокировки невозможно; реализуйте автоматический повтор (exponential backoff) для транзакции-жертвы. Так пользователь не увидит ошибку.