Analyse des interblocages

Symptôme

'deadlock detected', 'ORA-00060', 'chosen as deadlock victim' : des transactions se verrouillent mutuellement et l'une est interrompue.

1. Les interblocages sont normaux, mais ne doivent pas être fréquents

La base détecte l'interblocage et le résout en choisissant une victime : c'est voulu. L'objectif est d'en réduire la fréquence. L'application doit réessayer la transaction victime. Capturez d'abord le graphe d'interblocage.

2. Obtenez le graphe/les détails de l'interblocage

Quelles deux sessions (ou plus), quelles ressources et quels modes de verrou sont entrés en conflit ?

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. Résolvez le schéma de conflit

La cause la plus fréquente : deux transactions qui accèdent aux ressources dans l'ordre INVERSE. Accédez aux ressources dans le MÊME ordre dans tous les chemins de code. La deuxième cause la plus fréquente : des verrous de plage étendus dus à un index manquant ; ajoutez l'index requis.

4. Réduisez la durée et la portée des verrous

Gardez les transactions courtes (n'incluez pas d'interaction utilisateur dans une transaction). Choisissez un niveau d'isolation approprié. Si nécessaire, acquérez tôt les verrous de ligne (SELECT ... FOR UPDATE avec un ordre déterministe).

5. Le retry dans l'application est obligatoire

Les interblocages ne peuvent pas être totalement éliminés ; implémentez un retry automatique (exponential backoff) pour la transaction victime. L'utilisateur ne voit ainsi pas d'erreur.