[SQL Server] SQL Profiler: identification d’un deadlock

Ce billet présente comment identifier et analyser des deadlocks avec le SQL Profiler. Pour rappel, Un deadlock est un événement au cours duquel une ou plusieurs transactions sont en attente de libération de ressources par une autre transaction, provoquant ainsi une situation d’inter-blocage plus ou moins permanent. Les deadlocks sont assez souvent difficiles à capturer sans trace correctement planifiée dans la mesure où ils peuvent arriver de façon imprévisible.

Vous pouvez jeter un coup d’œil ici si vous souhaitez lire la présentation des verrous sous SQL Server, ainsi que , si vous voulez comprendre la différence entre les modèles de concurrence utilisés par SQL Server. Vous pouvez également jeter un coup d’œil ici pour connaître les niveaux d’isolation sous SQL Server et leurs spécificités, où les anomalies transactionnelles sont déjà présentées du point de vue théorique dont des rappels seront effectués dans ce billet.

Avant de commencer : simulation d’un deadlock

Reprenons l’exemple de création d’un deadlock du billet [SQL Server] Accès concurrentiels : identification et résolution de deadlocks, avec la création de deux tables Voiture et Client, et insérons-y quelques enregistrements :

CREATE TABLE Voiture (Voiture_ID INT NOT NULL, Marque NVARCHAR(20) NOT NULL)
GO
CREATE TABLE Client (Client_ID INT NOT NULL, Client_Nom NVARCHAR(30) NOT NULL)
GO

INSERT Voiture VALUES (1,'Renault')
INSERT Voiture VALUES (2,'Peugeot')
INSERT Client VALUES (1,'Dupont')
INSERT Client VALUES (2,'Durand')
GO

Lançons 2 transactions à partir de deux sessions différentes (en ouvrant 2 fenêtres SQL Server Management Studio, par exemple):

  • Etape 1 : ouverture d’une 1ère session (ou fenêtre SSMS 1) et lancement de la requête suivante :
BEGIN TRAN T1
UPDATE Voiture
SET Marque=N'Citroen'
WHERE Voiture_ID=1
  • Etape 2 : ouverture d’une 2ème session (ou fenêtre SSMS 2) et lancement de la requête suivante :
BEGIN TRAN T2
UPDATE Client
SET Client_Nom=N'Villiers'
WHERE Client_ID=1

UPDATE Voiture
SET Marque=N'Volkswagen'
WHERE Voiture_ID=1

COMMIT
GO
  • Etape 3 : à partir de la 1ère session (ou fenêtre SSMS 1), lancement de la requête suivante :
UPDATE Client
SET Client_Nom=N'Martin'
WHERE Client_ID=1
COMMIT
GO

Le lancement des différentes requêtes doit se faire après la configuration et exécution de la trace SQL Profiler (voir plus loin).

Configuration de la trace

Présentation et sélection des événements utiles

Le tableau ci-dessous dresse la liste des principaux événements permettant de mieux identifier les deadlocks avec le SQL Profiler :

Evénement Description
RPC:Completed Evénement permettant de capturer les procédures s’exécutant à distance en tant que RPC (Remote Procedure Call).
SP:StmtCompleted Evénement indiquant quand un ordre SQL, au sein d’une procédure, a été complétée. Par exemple, s’il y a 5 SELECT exécutés, la trace du SQL Profiler indiquera 5 SP :StmtCompleted.
SQL:BatchStarting Evénement qui se lance toutes les fois où un nouveau batch T-SQL commence. Cela peut concerner aussi bien un batch contenu au sein d’une procédure qu’en dehors.
SQL:BatchCompleted Evénement indiquant la fin d’un batch T-SQL. Si ce batch est contenu au sein d’une procédure stockée, la trace affichera son nom (mais pas son code, sauf si SP :StmtCompleted est sélectionné).
Lock:Deadlock graph Evénement indiquant, en format XML ou graphiquement, comment le deadlock est arrivé.
Lock:Deadlock Evénement déclenché à chaque détection de deadlock.
Lock:Deadlock Chain Evénement montrant sous forme de chaîne tous les traitements impliqués par le déclenchement du deadlock détecté.

Du côté des colonnes à sélectionner, celles par défaut suffisent.

Lancement et analyse de la trace

Lors du lancement de la trace, et en cas de détection d’un deadlock, voici un exemple de résultat :


Un clic ou une mise en surbrillance de l’événement Deadlock graph donne le résultat suivant :


La figure ovale barrée d’une croix bleue, à gauche, représente la transaction qui a été choisie comme deadlock victim par le moteur SQL au détriment de celle de droite qui représente la transaction ayant pu, finalement, s’exécuter avec succès après le deadlock.

Chaque figure ovale contient des informations utiles sur la transaction qu’elle représente. Ainsi :

  • Server process : identifiant du processus.
  • Server batch ID: numéro de référence interne du batch dans lequel le code T-SQL est encapsulé.
  • Execution context ID : numéro de référence interne du thread du processus identifié. 0 représente le thread principal ou le thread parent.
  • Deadlock priority ID : niveau d’importance d’une transaction face à un deadlock. Plus sa valeur est élevée, moins il a de chances de devenir la victime. Par défaut, toutes les transactions sont sur le même pied d’égalité, et quand la valeur de la priorité vaut 0, cela signifie que la transaction concernée n’a aucun niveau de priorité assigné.
  • Log used : espace consommé par la transaction dans le journal des logs. Le moteur SQL utilise cette information afin de savoir quelle transaction a consommé le plus de ressources, de sorte que celle ayant consommé le moins de ressources soient choisie comme victime.
  • Owner ID : numéro de référence interne de la transaction.
  • Transaction descriptor : état de la transaction.

Entre les deux ovales se trouvent 2 rectangles représentant les ressources (généralement des objets de base de données, telles que les tables, les vues, les procédures, indexes,…) sur lesquels les 2 transactions (ou ovales) étaient en train de se « disputer » (sur la figure ci-dessus, on peut voir que ce sont des indexes qui sont la source du « litige »).


Les flèches, quant à elle, représente les relations qu’on les transactions avec les ressources. Dans notre cas, il s’agit du type de lock (verrou) qu’a chaque transaction sur les ressources (dans notre cas, « X » représente le verrou de type exclusif et « U », le verrou de mise-à-jour (update)).

Pour déterminer le contenu de la transaction de chaque ovale, il suffit de placer le curseur de la souris sur la transaction concernée. Dans le cas :

  • De la transaction sortie « victorieuse » du deadlock :


  • De la transaction victime du deadlock :


Forts de toutes ces informations, nous pouvons donc interpréter le deadlock graph comme suit :

  1. La transaction représentée par le processus d’identifiant 59 (appelons-la « Ovale droite »)
    lance une requête et reçoit un lock exclusif sur la table Client.
  2. La transaction représentée par le processus d’identifiant 54 (appelons-la « Ovale gauche » lance une requête et reçoit un lock exclusif sur la table Voiture.
  3. Pour mener à bien son travail, Ovale gauche lance une requête d’update lock sur la table Client pour la verrouiller et la mettre à jour mais voyant qu’elle est déjà verrouillée par Ovale droite, décide donc d’attendre.
  4. Pour mener à bien, elle aussi, son travail, Ovale droite lance une requête exclusive
    lock sur la table Voiture pour la verrouiller mais voyant qu’elle est déjà bloquée par Ovale gauche, se met alors en attente.
  5. Les points 3 et 4 conduisant à un deadlock, le moteur SQL décide d’intervenir comme arbitre en analysant et comparant les informations d’Ovale gauche à celle d’Ovale droite, et vice-versa afin de les départager. Les deux Ovale ayant le même niveau de deadlock priority, le moteur SQL choisit alors de déterminer celle ayant consommé le moins de ressources transactionnelles.
  6. Ovale gauche ayant été la moins gourmande, le moteur SQL la sélectionne comme deadlock victim, en la « tuant ».

Pour aller plus loin…

Vous pouvez aller ici pour les conseils relatifs à la résolution de longs blocages/deadlocks. Et vous pouvez également aller , si vous souhaitez savoir comment obtenir rétrospectivement la liste des deadlocks en se basant sur la session d’événements étendus de SQL Server (merci à SQLPro, pour la suggestion).

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s