Blog

XEvent : Deadlock


Un deadlock se produit lorsque deux processus (ou plus) se bloquent mutuellement de façon permanente. Dans ce cas, chaque processus positionne un verrou sur une ressource que le ou les autres processus tentent de verrouiller. Le Moteur de base de données SQL Server détecte automatiquement le conflit et choisit l’une des sessions comme victime. La transaction en cours se termine par une erreur, ce qui met fin à la situation de conflit.

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Expert SQL Server - XEvent : Deadlock - SQL Server  - deadlock

La détection de conflit est déjà réalisée dans la trace XEvent par défaut system_health. Attention, cette liste de deadlocks n’est pas exhaustive. On est tributaire du dernier redémarrage de l’instance SQL Server et de la mémoire disponible au moment de la collecte (perte d’évènements). Pour consulter les deadlocks rétroactivement, lancer la commande suivante :

Pour consigner les deadlocks avec une rétention plus contrôlable, la création d’une trace XEvent passera par plusieurs étapes.

Etape 1

Nous allons créer une arborescence de répertoires sur laquelle le compte de service SQL Server aura le droit d’écrire. Le répertoire deadlock stockera les traces XEvent concernées.

Expert SQL Server - XEvent : Deadlock - SQL Server  - xevent_dir

Etape 2

Création de la trace XEvent en spécifiant le nom deadlock et le chemin D:\XEvent\deadock\deadock.xel. Notez qu’il y a un rollover de défini sur 5 fichiers par défaut pour une taille limitée à 5 Mo par fichier, les traces n’étant pas supposée saturer les disques.

Etape 3

Démarrage de la trace XEvent deadlock

Consultation des deadlocks

Le détail des deadlocks peut être consulté en parsant le XML des traces XEvent générées et le résultat reste à historiser dans une table au besoin. Il est désormais possible de savoir qui a tué qui, à quelle heure avec les requêtes associées aux processus vainqueur et victime.

Deadlock Grah

Pour obtenir des détails sur le deadlock, cliquer sur la colonne DeadLockGraph :

Expert SQL Server - XEvent : Deadlock - SQL Server  - deadloc_result

Voici le résultat obtenu, une vue au format XML pour l’analyse du deadlock :

Expert SQL Server - XEvent : Deadlock - SQL Server  - deaclock_xml

Pour obtenir une vue graphique, enregistrer le fichier en utilisant l’extension xdl à la place de xml par défaut.

Expert SQL Server - XEvent : Deadlock - SQL Server  - deadlock_save_xdl

Ouvrir le fichier DeadLockGraph1.xdl depuis SQL Mangement Studio. On obtient une vue graphique, version TV noir & blanc.

Expert SQL Server - XEvent : Deadlock - SQL Server  - deadlock_graph_ssms

Ouvrir le fichier DeadLockGraph1.xdl depuis SQL Sentry Plan Explorer, un outil d’analyse des plans d’exécution mais pas que. On obtient toujours une vue graphique mais version TV couleur.

Expert SQL Server - XEvent : Deadlock - SQL Server  - deadlock_open_sentry

Expert SQL Server - XEvent : Deadlock - SQL Server  - deadlock_graph_sql_sentry

Trace flags

Notez que les deadlocks peuvent être également consignés dans les logs SQL Server :

Au format XML

Au format texte

Éradication des deadlocks

Les deadlocks ne sont pas une fatalité, j’ai entendu trop fois que ce phénomène était purement applicatif et donc pas de la responsabilité du DBA. C’est faux. Ce qui est vrai en revanche, c’est que la marche de manœuvre sur une solution éditeur est beaucoup plus réduite pour éliminer les deadlocks. Cela étant, on peut toujours intervenir sur l’indexation ou encore appliquer des plans guides au besoin et remonter les deadlocks relevés à l’éditeur, à titre d’exemple. Sur une application développée en interne, voici quelques axes d’optimisation à envisager :

– Design de la base de données
– Niveau d’isolation
– Séquençage objets (ordre d’appel des objets identique d’une PS à l’autre)
– Transaction la plus courte possible
– Optimisation de code
– Indexation
(…)

Pour avoir plus d’info sur la concurrence (ex : processus bloqués) :

Auteur

Expert SQL Server - XEvent : Deadlock - SQL Server  - avatar_ninja_tete-150x150
Sarah Béquet
Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
error: