Datafly.pro

Qu'est-ce qu'un deadlock ?

Pour ceux qui ont besoin d'un petit rafraichissement, un deadlock est un "interblocage" de verrous : j'aime bien l'appeler une "étreinte fatale"...

Un deadlock typique survient lorsque cette séquence d'opérations survient :

• Une transaction n°1 pose un verrou sur une ressource 1
• Une transaction n°2 pose un verrou sur une ressource 2
• La transaction n°1 veut ensuite poser un verrou sur la ressource 2 : elle attend
• La transaction n°2 veut ensuite poser un verrou sur la ressource 1 : elle attend

Comme chaque transaction attend l'autre et réciproquement, l'attente risquerait d'être infinie. Heureusement, SQL Server surveille ces cas de figure, et décide dans ce cas de choisir une "victime" et lui annuler sa transaction (ROLLBACK, avec une belle erreur 1205). Il choisit en principe comme victime la transaction la moins coûteuse à annuler.

Msg 1205, Niveau 13, État 51, Ligne 18
La transaction (ID de processus 64) a été bloquée sur les ressources verrou par un autre processus et a été choisie comme victime. Réexécutez la transaction.

Cet exemple est un cas simple, mais vous pouvez trouver des deadlocks circulaires entre plusieurs transactions, des deadlocks entre plusieurs threads parallélisés, etc... Le résultat fait toujours une victime.

Pour simuler un deadlock, créons quelques tables pour le test :

USE tempdb
GO
CREATE TABLE TABLE1
(C1 INT NOT NULL PRIMARY KEY,
 C2 INT NULL,
 C3 VARCHAR(50) NULL)
GO
CREATE TABLE TABLE2
(C1 INT NOT NULL PRIMARY KEY,
 C2 INT NULL,
 C3 VARCHAR(50) NULL)
GO
DECLARE @val INT
SELECT  @val=1
WHILE   @val < 100
BEGIN  
   INSERT INTO TABLE1(C1, C2, C3) VALUES(@val, @val, 'TEST')
   INSERT INTO TABLE2(C1, C2, C3) VALUES(@val, @val, 'TEST')
   SELECT @val=@val+1
END
GO

Puis lançons simultanément dans deux fenêtres les deux requêtes suivantes :

--> Requête 1
USE tempdb
GO
BEGIN TRANSACTION ;
UPDATE TABLE2
   SET C3 = 'TEST'
WHERE  C2 = 2;
WAITFOR DELAY '00:00:10';
UPDATE TABLE1
   SET C3 = 'TEST'
WHERE  C2 = 1;
COMMIT;

--> Requête 2
USE tempdb
GO
BEGIN TRANSACTION;
UPDATE TABLE1
   SET C3 = 'TEST'
WHERE  C2 = 1;
WAITFOR DELAY '00:00:10';
UPDATE TABLE2
   SET C3 = 'TEST'
WHERE  C2 = 2;
COMMIT;

Tracer les deadlocks

La plupart des deadlocks sont assez simples à analyser, mais encore faut-il les capturer ! Il existe plusieurs moyens : des traceflags (1222 et 1204) pour enregistrer l'évènement dans l'errorlog, des notifications d'évènements, des traces profiler, etc ...

Mais nous allons ici utiliser la session d'évênement étendus "System Health", qui a l'énorme avantage d'être par défaut toujours active sur un serveur SQL Server (sauf si on l'a volontairement stoppée). Et entre autres choses, elle capture les deadlocks.

Pour extraire les informations sur les deadlocks, je vous propose la requête suivante :

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
   from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where object_name = 'xml_deadlock_report'
)
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc;

Celle requête nous renvoie la date de survenance des deadlocks, et une deuxième colonne qui détaille le deadlock graph en xml. A partir de là vous pouvez afficher très facilement le graphe de deadlock dans SQL Server Management Studio :

• cliquez sur le lien du flux XML dans la colonne XdlFile, le XML s'affiche dans une nouvelle fenêtre.
• Sauvegardez cette fenêtre vers un fichier avec l'extension .XDL.
• Fermez la fenêtre, puis rouvrez le fichier .XDL dans une nouvelle fenêtre (Menu Fichier/Fichiers récents)
• Le graphe s'affiche.

A noter que la session "system health" ne conserve qu'une rétenteion de 20 Mo d'évènement : 4 fichiers de 5 Mo. Vous n'aurez donc que des évènements récents. Si nécessaire, vous pouvez augmenter cette rétention en adaptant à vos besoins la requête suivante :

ALTER EVENT SESSION [system_health]
ON SERVER STATE = STOP
GO
ALTER EVENT SESSION [system_health]
ON SERVER DROP TARGET package0.event_file
ALTER EVENT SESSION [system_health]
ON SERVER ADD TARGET package0.event_file
 (SET FILENAME=N'system_health.xel',--name of the session
 max_file_size=(25), --size of each file in MB
 max_rollover_files=(40)) --how many files you want to keep
GO
ALTER EVENT SESSION [system_health]
ON SERVER STATE = START
GO

Bonne chasse aux deadlocks !