[SQL Server] Accès concurrentiels : identification de blocages et de deadlocks

Ce billet aborde le cas des blocages et des deadlocks (verrous mortels, interblocages) sous SQL Server et la façon dont on peut les identifier et traiter.

Il existe diverses façons d’identifier les deadlocks, mais nous nous intéresserons principalement au SQL Server Profiler, aux DMV et au trace flag 1222 (sans oublier 1204).

Vous pouvez jeter un coup d’œil ici si vous souhaitez lire la présentation des verrous et des blocages 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.

Notions de blocages et de deadlock

  Qu’est-ce qu’un blocage et un deadlock ?

Un blocage est un événement au cours duquel une transaction maintient un verrou sur une ressource spécifique et une autre transaction tente d’acquérir un verrou sur cette même ressource. Il s’agit d’une situation parfaitement normale car elle permet d’assurer l’intégrité des données. En revanche, là où la situation devient anormale, c’est quand elle mène à un deadlock.

Un deadlock (ou verrou mortel) correspond à une situation particulière au cours de laquelle plusieurs transactions sont en conflit pour l’accès à une même ressource non-disponible. Dans ce genre de situation, la seule façon de résoudre ledit conflit est de « tuer » l’une d’elles.

Par exemple, supposons que :

  • Une transaction T1 pose un verrou sur une donnée A et a besoin de verrouiller une donnée B pour mener à bien son travail.
  • Une transaction T2 a déjà verrouillé une donnée B, et a besoin d’en faire de même sur la donnée A pour terminer son traitement.

Cela mène à une situation où chacune de ces 2 transactions attend que l’autre libère « sa » donnée pour compléter son exécution, et où la seule solution est soit un abandon de l’une des 2 transactions, soit une intervention extérieure qui permettra de forcer l’une des transactions à céder la main.

Détection et analyse d’un deadlock par SQL Server

SQL Server, fort heureusement, est capable de détecter un deadlock et d’y mettre fin, après examen des transactions concernées afin de savoir laquelle (ou lesquelles) annuler. SQL Server utilise une batterie de processus lui permettant de vérifier régulièrement la présence d’inter-blocages, y compris au niveau des transactions distribuées. Le délai de détection est généralement instantané.

Les principaux facteurs permettant à SQL Server de choisir quelle(s) transaction(s) « tuer » sont les suivants :

  • Son niveau de priorité (quand il est spécifié). SQL Server tuera la transaction dont le niveau de priorité est le plus bas.
  • Sa volumétrie transactionnelle. En effet, SQL Server, par défaut, élimine les transactions les moins gourmandes en ressources afin de n’épargner que celles plus volumineuses, tant que cela puisse permettre d’annuler le deadlock. Cette solution est surtout utilisée lorsque les transactions concernées ont le même niveau de priorité.

Concernant le niveau de priorité, il peut être spécifié comme suit :

SET DEADLOCK_PRIORITY
GO

<Niveau> peut correspondre à:

  • Une valeur numérique comprise entre -10 et 10.
  • Une valeur alphanumérique : HIGH (équivalente à une valeur comprise entre 0 et 5), NORMAL (entre -5 et 0) ou LOW (entre -10 et -5). NORMAL est la valeur par défaut.

La spécification se fait au niveau de la session à laquelle appartient la transaction que l’on souhaite traiter.

Identification d’un blocage et d’un deadlock

Pour simuler un deadlock, créons 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 Client VALUES (1,'Dupont')
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
GO
 
  • 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

Ce modus operandi de création d’un deadlock donnera lieu au message d’erreur suivant (sur la 2ème session dont la transaction a apparemment été choisie pour victime) :

Msg 1205, Level 13, State 45, Line 6
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Nous allons maintenant expliquer comment identifier un deadlock (en prenant en compte notre exemple ci-dessus)…

… avec sp_lock

Si vous lancez la procédure stockée système sp_lock durant l’exécution des requêtes (ou pour être exact : durant le blocage), vous aurez droit à cet exemple de résultat :


La présence d’un statut WAIT pour un processus donné (dans notre exemple, les processus de spid 53 et 54) indique qu’il y a attente de libération de verrou. Concrètement :

  • Le processus de spid 53 est en attente d’obtention d’un verrou de mise-à-jour (mode U) sur la ressource 1:127:0 (fichier d’identifiant 1, page numéro 127, identifiant de ligne ou row ID ou RID 0), et il ne pourra l’obtenir que si le spid 53 libère ladite ressource (mode X).
  • Le processus de spid 54 est en attente d’obtention d’un verrou de mise-à-jour sur la ressource 1:121:0, et il ne pourra l’obtenir que si le spid 53 libère ladite ressource.

Par la suite, comme évoqué plus haut, ce type de message d’erreur s’affichera :

Msg 1205, Level 13, State 45, Line 6
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Les résultats de sp_lock étant volatiles, si vous souhaitez garder y garder les informations pendant un certain temps au sein d’une table, vous pouvez exécuter le script suivant (la colonne TimestampInfo est utilisée pour dater les informations récupérées) :

CREATE TABLE sp_lock_results
(spid INT,
 DBID INT,
[Object_ID] INT,
 IndID INT,
 Type NVARCHAR (20),
 Resource NVARCHAR (100),
 Mode NVARCHAR (20),
 Status NVARCHAR (20),
 TimestampInfo DATETIME DEFAULT CURRENT_TIMESTAMP)
GO

INSERT INTO sp_lock_results (spid,DBID,[Object_ID],IndID,Type,Resource,Mode,Status)
 EXEC sp_lock
GO

Autrement dit, la transaction associée au spid 54 a été choisie comme victime par SQL Server.

A noter que dans le même style que sp_lock, la procédure stockée système sp_who2 peut être utilisée pour identifier les blocages.


On peut noter que la session de spid 54 est bloquée (BlkBy ou Blocked By) par celle de spid 53. Un deadlock (non-visible ci-dessus) peut être potentiellement identifié si, en plus d’un tel blocage, le spid 54 bloque également le spid 53 sur une autre ressource que ce dernier a besoin d’acquérir pour la complétion de ses traitements sur la ressource ayant nécessité le blocage du spid 54.

… avec les trace flags 1204 et 1222

Les trace flags 1204 et 1222 permettent de fournir des informations sur les deadlocks détectés par SQL Server. En l’occurrence :

  • Le trace flag 1204 donne des informations détaillées au sein du journal d’erreurs (error log) en format textuel.
  • Le trace flag 1222 est similaire au trace flag 1204, à la différence qu’il fournit les informations en format XML.

Pour activer la ou les trace flags en question, exécutez la requête suivante (nous avons ici opté pour 1222) :

DBCC TRACEON(1222, 3605,-1)
GO

Bien entendu, les trace flags 1204 et 1222 peuvent être activés ensemble. En outre, à titre d’information, l’utilisation de 3605 permet, ici, juste de rediriger les sorties DBCC vers le journal d’erreurs, tandis que -1 sert à afficher les résultats dans ledit journal.

Pour récupérer les résultats de la trace, vous pouvez jeter un coup d’œil au journal d’erreurs (error log) de SQL Server, dont voici un extrait, dans notre cas précis :

2013-05-28 19:07:09.670 spid26s deadlock-list
2013-05-28 19:07:09.670 spid26s deadlock victim=process2f1210cf8
2013-05-28 19:07:09.670 spid26s process-list
2013-05-28 19:07:09.670 spid26s process id=process2f1210cf8 taskpriority=0 logused=236 waitresource=RID: 5:1:127:0 waittime=1963 ownerId=87643 transactionname=T1 lasttranstarted=2013-05-28T19:06:55.547 XDES=0x2fd07a6f8 lockMode=U schedulerid=3 kpid=10616 status=suspended spid=53 sbid=
2013-05-28 19:07:09.670 spid26s executionStack
2013-05-28 19:07:09.670 spid26s frame procname=adhoc line=1 stmtstart=60 sqlhandle=0x020000007ffff5061692876c713e6a963369738afeccf6b70000000000000000000000000000000000000000
2013-05-28 19:07:09.670 spid26s UPDATE [Client] set [Client_Nom] = @1 WHERE [Client_ID]=@2
2013-05-28 19:07:09.670 spid26s frame procname=adhoc line=1 stmtstart=8 stmtend=148 sqlhandle=0x020000002b01c807a11936a7d31a66468fb2dca52f1d3c540000000000000000000000000000000000000000
2013-05-28 19:07:09.670 spid26s UPDATE Client
2013-05-28 19:07:09.670 spid26s SET Client_Nom =N’Martin’
2013-05-28 19:07:09.670 spid26s WHERE Client_ID = 1
2013-05-28 19:07:09.670 spid26s inputbuf
2013-05-28 19:07:09.670 spid26s UPDATE Client
2013-05-28 19:07:09.670 spid26s SET Client_Nom =N’Martin’

2013-05-28 19:07:09.670 spid26s WHERE Client_ID = 1

2013-05-28 19:07:09.670 spid26s COMMIT

2013-05-28 19:07:09.670 spid26s process id=process2f1210928 taskpriority=0 logused=264 waitresource=RID: 5:1:121:0 waittime=7159 ownerId=87649 transactionname=T2 lasttranstarted=2013-05-28T19:07:02.513 XDES=0x2fd07b078 lockMode=U schedulerid=3 kpid=7580 status=suspended spid=54 sbid=0

2013-05-28 19:07:09.670 spid26s executionStack

2013-05-28 19:07:09.670 spid26s frame procname=adhoc line=6 stmtstart=60 sqlhandle=0x02000000762b1b2e1e65a6d59c262982a23a4ba4035785b50000000000000000000000000000000000000000

2013-05-28 19:07:09.670 spid26s UPDATE [Voiture] set [Marque] = @1 WHERE [Voiture_ID]=@2

2013-05-28 19:07:09.670 spid26s frame procname=adhoc line=6 stmtstart=200 stmtend=344 sqlhandle=0x02000000f9fac12e5e53247de10a6f7c1737c64138c0950f0000000000000000000000000000000000000000

2013-05-28 19:07:09.670 spid26s UPDATE Voiture

2013-05-28 19:07:09.670 spid26s SET Marque =N’Volkswagen’

2013-05-28 19:07:09.670 spid26s WHERE Voiture_ID = 1

2013-05-28 19:07:09.670 spid26s inputbuf

2013-05-28 19:07:09.670 spid26s BEGIN TRAN T2

2013-05-28 19:07:09.670 spid26s UPDATE Client

2013-05-28 19:07:09.670 spid26s SET Client_Nom =N’Villiers’

2013-05-28 19:07:09.670 spid26s WHERE Client_ID = 1

2013-05-28 19:07:09.670 spid26s UPDATE Voiture

2013-05-28 19:07:09.670 spid26s SET Marque =N’Volkswagen’

2013-05-28 19:07:09.670 spid26s WHERE Voiture_ID = 1

2013-05-28 19:07:09.670 spid26s COMMIT

2013-05-28 19:07:09.670 spid26s resource-list

2013-05-28 19:07:09.670 spid26s ridlock fileid=1 pageid=127 dbid=5 objectname=mydatabase.dbo.Client id=lock2fd6d7e00 mode=X associatedObjectId=72057594039566336

2013-05-28 19:07:09.670 spid26s owner-list

2013-05-28 19:07:09.670 spid26s owner id=process2f1210928 mode=X

2013-05-28 19:07:09.670 spid26s waiter-list

2013-05-28 19:07:09.670 spid26s waiter id=process2f1210cf8 mode=U requestType=wait

2013-05-28 19:07:09.670 spid26s ridlock fileid=1 pageid=121 dbid=5 objectname=mydatabase.dbo.Voiture id=lock2fd6e0100 mode=X associatedObjectId=72057594039500800

2013-05-28 19:07:09.670 spid26s owner-list

2013-05-28 19:07:09.670 spid26s owner id=process2f1210cf8 mode=X

2013-05-28 19:07:09.670 spid26s waiter-list

2013-05-28 19:07:09.670 spid26s waiter id=process2f1210928 mode=U requestType=wait

Comme on peut le souligner :

  • Le processus process2f1210cf8 a exécuté la transaction nommée (transactionname) T1 sous la session de spid 53. Cette transaction exécute une requête de type ad hoc qui tentait de faire une mise-à-jour (mode U) sur une ligne de la colonne Client_Nom de la table Client d’identifiant Client_ID=1. Mais la ligne de données étant déjà verrouillée (en mode X) par le processus process2f1210928 de spid 54 exécutant la transaction T2, il est resté bloqué.
  • Le processus process2f1210928 exécutant la transaction nommée (transactionname) T1 sous la session de spid 53. Cette transaction exécute une requête de type ad hoc qui tentait de faire une mise-à-jour (mode U) sur une ligne de la colonne Marque de la table Voiture d’identifiant Voiture_ID=1. Mais la ligne de données étant déjà verrouillée (en mode X) par le processus process2f1210cf8 de spid 53 exécutant la transaction T1, il est resté bloqué.

Pour résoudre ce deadlock, SQL Server a choisi la transaction exécutée par le processus process2f1210cf8 comme victime. Et pour ce faire, il s’est basé sur les points suivants :

  • Etape 1 : vérification du niveau de priorité à vu qu’il est identique pour les 2 transactions (niveau 0, donc NORMAL), passage à l’étape suivante.
  • Etape 2 : vérification de la volumétrie des transactions à T1 (logused=236) étant plus légère que T2 (logused=264), cette transaction a donc été choisie comme victime.

… avec le SQL Profiler

A venir…

… avec les DMV

Voir ici.

Conseils pour la réduction des risques de deadlocks

Voici une liste non-exhaustive de recommandations permettant de minimiser les risques de deadlocks :

  • Assurez-vous que vos transactions soient suffisamment courtes. Si ce n’est pas le cas, essayez de les diviser en plusieurs batches plus petits.
  • Si le parallélisme génère trop de threads en attente conduisant ainsi à des deadlocks, envisagez un passage du MAXDOP à 1.
  • Revoyez votre politique de verrouillage en réadaptant le niveau d’isolation (un niveau d’isolation trop élevé pouvant être source de deadlocks intempestifs). Vous pouvez éventuellement envisager l’utilisation d’un niveau d’isolation d’instantané, sous réserve d’une bonne optimisation de tempDB.
  • Revoyez, si possible, la politique d’ordonnancement des accès à vos données au niveau transactionnel. Par exemple, pour reprendre notre étude de cas sur Voiture et Client, il serait souhaitable en sorte que T1 (ou T2) fasse ses traitements sur les 2 tables avant de céder la main à T2 (ou T1), au lieu de les chevaucher.
  • Si possible, si plusieurs sessions accèdent fréquemment aux mêmes données, envisagez l’idée de les associer de façon à pouvoir permettre aux sessions de partager les mêmes transactions, les mêmes verrous,… Plus de détails ici : http://msdn.microsoft.com/fr-fr/library/ms177480.aspx.
  • Limitez l’utilisation de curseurs.
  • Envisagez l’idée de poser un verrou NOLOCK si la donnée traitée, bien que sujette à des deadlocks à chaque modification, est rarement modifiée en temps normal.
  • Vérifiez, bien sûr, que le schéma relationnel de la base de données est correctement normalisé.
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