Deadlocks SQL Server : lire le rapport, trouver la cause, corriger.

Ce qu'est un interblocage

Deux sessions se bloquent mutuellement : A détient un verrou dont B a besoin, et B détient un verrou dont A a besoin. Aucune ne peut avancer. SQL Server détecte le cycle toutes les quelques secondes, choisit une victime (la transaction la moins coûteuse à annuler), l'annule et renvoie l'erreur 1205 à son application. L'autre session continue.

Ne le confondez pas avec un simple blocage, où l'attente finit quand l'autre session termine : voir l'article sur les blocages.

Retrouver les interblocages passés

Inutile d'avoir une trace en cours : la session d'événements étendus system_health, active par défaut, enregistre chaque interblocage. Cette requête extrait, pour chacun, les sessions impliquées, leur niveau d'isolation, la ressource attendue et la requête exécutée.

;WITH ev AS (
    SELECT CAST(event_data AS xml) AS e
    FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
    WHERE object_name = N'xml_deadlock_report'
)
SELECT
    ev.e.value('(/event/@timestamp)[1]', 'datetime2')       AS date_utc,
    pr.p.value('@spid', 'int')                              AS session_id,
    pr.p.value('@isolationlevel', 'nvarchar(60)')           AS niveau_isolation,
    pr.p.value('@waitresource', 'nvarchar(200)')            AS ressource_attendue,
    pr.p.value('@hostname', 'nvarchar(128)')                AS poste,
    pr.p.value('@clientapp', 'nvarchar(256)')               AS application,
    pr.p.value('(inputbuf)[1]', 'nvarchar(max)')            AS requete
FROM ev
CROSS APPLY ev.e.nodes('/event/data/value/deadlock/process-list/process') AS pr(p)
ORDER BY date_utc DESC;

Les heures sont en UTC. Pour voir le graphe complet, extrayez l'élément deadlock du XML, enregistrez-le dans un fichier d'extension .xdl, puis ouvrez-le dans SSMS : le graphe montre les deux transactions et les verrous qu'elles se disputent. La lecture des fichiers est détaillée dans l'article sur les XEvents.

Lire le rapport

Le rapport XML contient trois parties :

• victim-list : la ou les transactions annulées.
• process-list : les sessions impliquées. Pour chacune : la requête (inputbuf), le niveau d'isolation, le poste, l'application, et la ressource attendue (waitresource).
• resource-list : les verrous en jeu. Chaque ressource (keylock, pagelock, ridlock, objectlock) précise l'objet, l'index, le mode, qui la détient (owner-list) et qui l'attend (waiter-list).

La lecture consiste à suivre la boucle : la session 1 détient la ressource X et attend Y, la session 2 détient Y et attend X. Les noms d'index et de tables indiquent où chercher.

Les causes les plus fréquentes

• Ordre d'accès inverse : une procédure met à jour la table A puis B, une autre B puis A. Deux exécutions simultanées se croisent.
• Lecture puis écriture sur la même ligne : une session lit une ligne (verrou partagé), une autre fait de même, puis toutes deux veulent la modifier (verrou exclusif) et s'attendent. C'est la forme classique de la conversion de verrou.
• Recherche dans un index non clusterisé puis accès à la table : une requête de lecture verrouille l'index puis la ligne de table, une mise à jour fait l'inverse. Un index couvrant supprime ce cas.
• Index manquant : une requête qui parcourt toute la table verrouille beaucoup de lignes et croise toutes les autres.
• Déclencheurs (triggers) qui modifient d'autres tables et allongent les transactions.

Corriger durablement

• Uniformiser l'ordre d'accès aux tables dans toutes les procédures qui modifient plusieurs objets.
• Raccourcir les transactions : moins de temps à détenir des verrous, moins de risques de croisement.
• Prendre le verrou final d'emblée pour les séquences lecture puis écriture : SELECT ... FROM dbo.Compteurs WITH (UPDLOCK) WHERE ... évite la conversion de verrou.
• Ajouter ou élargir un index pour couvrir la requête de lecture et éviter l'accès en deux temps.
• Passer en isolation par versions de lignes (RCSI) : les lecteurs ne prennent plus de verrous partagés, ce qui supprime une grande partie des interblocages entre lecture et écriture. Voir l'article dédié.
• Désigner la victime pour protéger un traitement important : SET DEADLOCK_PRIORITY LOW; dans les traitements secondaires.

Prévoir une nouvelle tentative

Un interblocage peut toujours survenir de façon sporadique. L'application doit donc réessayer la transaction annulée. En T-SQL :

DECLARE @essais int = 0;

WHILE @essais < 3
BEGIN
    BEGIN TRY
        BEGIN TRAN;
            -- ... instructions de la transaction ...
        COMMIT;
        BREAK;                                -- succès : on sort de la boucle
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        IF ERROR_NUMBER() = 1205 AND @essais < 2
        BEGIN
            SET @essais += 1;
            WAITFOR DELAY '00:00:01';         -- courte pause avant de réessayer
        END
        ELSE
            THROW;                            -- autre erreur, ou trop d'essais
    END CATCH;
END;

Dans une application, le même principe s'applique : intercepter l'erreur 1205, attendre brièvement, recommencer la transaction entière.

Surveiller

Un interblocage isolé n'est pas inquiétant. Une série en quelques minutes ou la répétition du même couple de requêtes indique un défaut à corriger. Comptez les rapports par jour dans system_health, et complétez avec le compteur de performance « Number of Deadlocks/sec ».