Détection et fin des blocages
Un blocage se produit lorsque deux tâches ou plus se bloquent mutuellement de façon permanente. Dans ce cas, chaque tâche place un verrou sur une ressource que la ou les autres tâches essaient de verrouiller. Le graphique suivant présente un aperçu d'un état de blocage où :
La tâche T1 a placé un verrou sur la ressource R1 (indiquée par la flèche reliant R1 à T1) et a demandé un verrou sur la ressource R2 (indiquée par la flèche reliant T1 à R2).
La tâche T2 a placé un verrou sur la ressource R2 (indiquée par la flèche reliant R2 à T2) et a demandé un verrou sur la ressource R1 (indiquée par la flèche reliant T2 à R1).
Dans la mesure où aucune des deux tâches ne peut continuer tant qu'il n'y a pas de ressource disponible et que ni l'une ni l'autre des ressources ne peut être libérée avant la poursuite d'une tâche, un état de blocage se produit.
.gif "Diagramme affichant des tâches dans un état de blocage")
Le Moteur de base de données SQL Server détecte automatiquement les cycles de blocage dans SQL Server. Le Moteur de base de données choisit l'une des sessions comme victime et la transaction en cours se termine par une erreur, ce qui met fin à la situation de blocage.
Ressources susceptibles de se bloquer
Chaque session utilisateur peut avoir une ou plusieurs tâches en cours d'exécution, chacune de ces tâches pouvant obtenir ou être en attente d'obtention de diverses ressources. Les types de ressources susceptibles de provoquer un blocage sont les suivants :
Verrous. L'attente d'obtention de verrous sur des ressources, telles qu'objets, pages, lignes, métadonnées et applications peut provoquer un blocage. Par exemple, la transaction T1 a un verrou partagé (S) sur la ligne r1 et elle attend d'obtenir un verrou exclusif (X) sur r2. La transaction T2 a un verrou partagé (S) sur r2 et elle attend d'obtenir un verrou exclusif (X) sur la ligne r1. Il en résulte un cycle de verrouillage où T1 et T2 attendent l'une de l'autre la libération des ressources que chacune a verrouillées.
Threads de travail. Une tâche en attente d'un thread de travail disponible peut provoquer un blocage. Si la tâche en file d'attente est propriétaire des ressources qui bloquent tous les threads de travail, un blocage en résulte. Par exemple, la session S1 démarre une transaction et obtient un verrou partagé (S) sur la ligne r1 pour ensuite se mettre en veille. Les sessions actives en cours d'exécution sur tous les threads de travail disponibles essaient d'obtenir des verrous exclusifs (X) sur la ligne r1. Étant donné que la session S1 ne peut pas obtenir de thread de travail, elle ne peut pas valider la transaction et libère le verrou au niveau sur la ligne r1. Cela produit un blocage.
Mémoire. Lorsque des demandes concurrentes sont en attente d'allocation de mémoire qui ne peut être satisfaite faute de mémoire suffisante, un blocage peut se produire. Par exemple, deux demandes concurrentes, Q1 et Q2, qui s'exécutant en tant que fonctions définies par l'utilisateur, obtiennent respectivement 10 Mo et 20 Mo de mémoire. Si chaque requête nécessite 30 Mo et que la quantité de mémoire disponible est de 20 Mo, Q1 et Q2 doivent attendre que chacune libère la mémoire, ce qui entraîne un blocage.
Ressources liées à l'exécution de requêtes parallèles. Les threads de coordination, production ou consommation associées à un port d'échange peuvent se bloquer mutuellement et provoquer un blocage qui se produit généralement lors de l'introduction d'au moins un autre processus étranger à la requête parallèle. De même, quand commence l'exécution d'une requête parallèle, SQL Server détermine le degré de parallélisme, ou le nombre de threads de travail, en fonction de la charge de travail en cours. Si la charge de travail change de façon inattendue, par exemple si de nouvelles requêtes commencent à s'exécuter sur le serveur ou que le système se trouve à court de threads de travail, il peut s'ensuivre un blocage.
Ressources MARS (Multiple Active Result Sets). Ces ressources servent à contrôler l'entrelacement de plusieurs demandes actives sous MARS (consultez Environnement d'exécution des traitements et MARS).
Ressource utilisateur. Lorsqu'un thread est en attente d'une ressource potentiellement contrôlée par une application d'utilisateur, la ressource est considérée comme étant une ressource externe ou utilisateur et est traitée comme un verrou.
Exclusion mutuelle de session Les tâches exécutées au cours d'une session sont entrelacées, ce qui signifie que seule une tâche peut s'exécuter à un moment donné dans le cadre de la session. Avant de pouvoir s'exécuter, la tâche doit disposer d'un accès exclusif à l'exclusion mutuelle de la session.
Exclusion mutuelle de transaction Toutes les tâches qui s'exécutent lors d'une transaction sont entrelacées, ce qui signifie que seule une tâche peut s'exécuter à un moment donné dans le cadre de la transaction. Avant de pouvoir s'exécuter, la tâche doit disposer d'un accès exclusif à l'exclusion mutuelle de la transaction.
Pour pouvoir s'exécuter sous MARS, une tâche doit obtenir l'exclusion mutuelle de session. Si la tâche s'exécute dans le cadre d'une transaction, elle doit obtenir l'exclusion mutuelle de transaction. Vous serez ainsi assuré qu'il n'y a qu'une seule tâche active à la fois pour une session et une transaction données. Dès lors que les exclusions mutuelles requises ont été acquises, la tâche peut s'exécuter. Quand la tâche est terminée ou qu'elle aboutit au milieu de la demande, elle libère l'exclusion mutuelle de transaction avant l'exclusion mutuelle de session, c'est-à-dire dans l'ordre inverse de leur acquisition. Cependant, des blocages peuvent se produire avec ces ressources. Dans l'exemple de code suivant, deux tâches, la demande d'utilisateur U1 et la demande d'utilisateur U2, s'exécutent lors d'une même session.
U1: Rs1=Command1.Execute("insert sometable EXEC usp_someproc"); U2: Rs2=Command2.Execute("select colA from sometable");La procédure stockée qui s'exécute à partir de la demande d'utilisateur U1 a obtenu l'exclusion mutuelle de session. Si son exécution se prolonge, le Moteur de base de données considère que la procédure stockée attend une entrée de données de la part de l'utilisateur. La demande d'utilisateur U2 attend l'exclusion mutuelle de session alors que l'utilisateur attend le jeu de résultats d'U2, et U1 attend une ressource utilisateur. Il s'agit d'un état de blocage logiquement illustré ainsi :
.gif "Diagramme logique montrant le blocage de processus utilisateur.")
Détection de blocage
Toutes les ressources énumérées dans la section précédente sont visées par le dispositif de détection de blocage du Moteur de base de données. La détection de blocage est mise en œuvre par un thread de contrôle des verrous qui lance périodiquement une recherche sur toutes les tâches d'une instance du Moteur de base de données. Le processus de recherche présente les caractéristiques suivantes :
L'intervalle par défaut est de 5 secondes.
Si le thread de contrôle des verrous détecte des blocages, de 5 secondes, l'intervalle de détection de blocage pourra descendre jusqu'à 100 millisecondes, en fonction de la fréquence des blocages.
Si le thread de contrôle des verrous ne détecte plus de blocages, le Moteur de base de données refera passer l'intervalle de recherche à 5 secondes.
Si un blocage vient d'être détecté, les prochains threads qui doivent attendre un verrou sont supposés entrer dans le cycle de blocage. Les deux premières attentes de verrous postérieures à une détection de blocage déclencheront immédiatement une recherche de blocage sans attendre le prochain intervalle de détection de blocage. Par exemple, si l'intervalle courant est de 5 secondes et qu'un blocage vient d'être détecté, la prochaine attente de verrou lancera immédiatement le détecteur de blocage. Si cette attente de verrou est impliquée dans un blocage, celui-ci sera détecté sur le champ et non lors de la prochaine recherche de blocage.
En règle générale, le Moteur de base de données n'opère qu'une détection de blocage périodique. Puisque le nombre de blocages rencontrés dans le système est généralement faible, la détection de blocages périodique permet de réduire l'intendance des détections de blocage dans le système.
Lorsque le contrôleur de verrous initialise une recherche de blocage pour une thread particulière, il identifie la ressource sur laquelle la thread est en attente. Il recherche ensuite le ou les propriétaires de la ressource concernée et continue la recherche de façon récursive, jusqu'à ce qu'il trouve un cycle. Un cycle identifié de cette manière forme un blocage.
Dès lors qu'un blocage est détecté, le Moteur de base de données met fin à un blocage en choisissant l'un des threads comme victime. Le Moteur de base de données met fin au traitement en cours d'exécution pour le thread, annule la transaction de la victime, puis retourne une erreur 1205 à l'application. L'annulation de la transaction de la victime du blocage a pour effet de libérer tous les verrous détenus par la transaction. Cela permet aux transactions des autres threads de se débloquer et de continuer. L'erreur de victime de blocage 1205 enregistre des informations sur les threads et les ressources impliqués dans un blocage dans le journal des erreurs.
Par défaut, le Moteur de base de données choisit comme victime du blocage la session qui exécute la transaction dont l'annulation est la moins coûteuse. Un utilisateur peut également spécifier la priorité des sessions dans une situation de blocage au moyen de l'instruction SET DEADLOCK_PRIORITY. DEADLOCK_PRIORITY accepte les valeurs LOW, NORMAL ou HIGH, voire toute valeur entière comprise entre -10 et 10. La valeur par défaut de la priorité de blocage est NORMAL. Si deux sessions ont des priorités de blocage différentes, c'est la session qui a la priorité la plus basse qui est choisie comme victime. Si les deux sessions ont la même priorité de blocage, c'est celle dont la transaction est la moins coûteuse à annuler qui est choisie. Si les sessions impliquées dans le cycle de blocage présentent une priorité de blocage et un coût identiques, la victime est choisie de façon aléatoire.
Lorsque les fonctionnalités CLR sont utilisées, le moniteur de blocage détecte automatiquement le blocage des ressources de synchronisation (moniteurs, verrou de lecture/écriture et jointure de thread) qui font l'objet d'accès à l'intérieur des procédures gérées. Toutefois, le blocage est résolu par la levée d'une exception dans la procédure qui a été sélectionnée comme victime du blocage. Il est important de comprendre que l'exception ne libère pas automatiquement les ressources actuellement détenues par la victime ; les ressources doivent être libérées explicitement. Conformément au comportement des exceptions, l'exception utilisée pour identifier une victime de blocage peut être interceptée et annulée.
Outils d'information sur les blocages
Pour afficher les informations sur le blocage, le Moteur de base de données fournit des outils de surveillance sous la forme de deux indicateurs de trace, ainsi que l'événement Deadlock Graph dans SQL Server Profiler.
Indicateur de trace 1204 et indicateur de trace 1222
En cas de situation de blocage, l'indicateur de trace 1204 et l'indicateur de trace 1222 retournent des informations qui sont recueillies dans le journal des erreurs SQL Server 2005. L'indicateur de trace 1024 signale les informations de blocage mises en forme par chaque nœud impliqué dans le blocage. L'indicateur de trace 1222 met en forme les informations de blocage, en commençant par les processus et en poursuivant avec les ressources. Il est possible d'activer deux indicateurs de trace pour obtenir deux représentations du même événement de blocage.
En dehors de la définition des propriétés des indicateurs de trace 1204 et 1222, le tableau suivant contient également les ressemblances et les différences.
Propriété |
Indicateur de trace 1204 et indicateur de trace 1222 |
Indicateur de trace 1204 uniquement |
Indicateur de trace 1222 uniquement |
|---|---|---|---|
Format de sortie |
La sortie est capturée dans le journal des erreurs de SQL Server 2005. |
Les nœuds impliqués dans le blocage sont privilégiés. Chaque nœud dispose d'une section dédiée, tandis que la section finale décrit la victime du blocage. |
Retourne des informations dans un format de type XML, mais non conforme au schéma XSD (XML Schema Definition). Le format possède trois sections principales. La première déclare la victime du blocage. La deuxième décrit chaque processus impliqué dans le blocage. La troisième décrit les ressources synonymes des nœuds de l'indicateur de trace 1204. |
Identification d'attributs |
SPID:<x> ECID:<x>. Identifie le thread de l'ID du processus système en cas de traitements parallèles. L'entrée SPID:<x> ECID:0, où la valeur SPID remplace <x>, représente le thread principal. L'entrée SPID:<x> ECID:<y>, où la valeur SPID remplace <x> et où <y> est supérieur à 0, représente les sous-threads du même SPID. BatchID (sbid pour l'indicateur de trace 1222). Identifie le traitement à partir duquel l'exécution du code demande ou détient un verrou. Quand l'option MARS (Multiple Active Result Sets) est désactivée, BatchID a pour valeur 0. Quand elle est activée, la valeur des traitements actifs est comprise entre 1 et n. Si la session ne comporte pas de traitements actifs, BatchID a pour valeur 0. Mode. Spécifie, pour une ressource particulière, le type de verrou demandé, accordé ou attendu par un thread. Les différents modes sont IS (intent partagé), S (partagé), U (mise à jour), IX (intent exclusif), SIX (partagé avec intent exclusif) et X (exclusif). Pour plus d'informations, consultez Modes de verrouillage. Line # (line pour l'indicateur de trace 1222). Indique le numéro de ligne du traitement qui était en cours d'exécution lorsque le blocage s'est produit. Input Buf (inputbuf pour l'indicateur de trace 1222). Dresse la liste de toutes les instructions du traitement en cours. |
Node. Il s'agit du numéro d'entrée dans la chaîne de blocage. Lists. Le propriétaire du verrou peut faire partie des listes suivantes :
Statement Type. Décrit le type d'instructions DML (SELECT, INSERT, UPDATE ou DELETE) sur lesquelles les threads disposent d'autorisations. Victim Resource Owner. Spécifie le thread choisi comme victime par SQL Server pour rompre le cycle de blocage. Il est alors mis fin au thread choisi et à tous les sous-threads existants. Next Branch. Représente les deux sous-threads (ou plus) du même SPID qui participent au cycle de blocage. |
deadlock victim. Représente l'adresse de mémoire physique de la tâche (consultez sys.dm_os_tasks (Transact-SQL)) qui a été sélectionnée comme victime du blocage. Elle est égale à 0 (zéro) en cas de non résolution du blocage. Une tâche en cours d'annulation ne peut pas être choisie comme victime de blocage. executionstack. Représente le code Transact-SQL en cours d'exécution lorsque le blocage se produit. priority. Représente la priorité de blocage. Dans certains cas, le Moteur de base de données peut choisir de modifier la priorité de blocage pendant un bref laps de temps afin de favoriser la concurrence. logused. Espace journal utilisé par la tâche. owner id. ID de la transaction qui contrôle la demande. status. État de la tâche. Il prend l'une des valeurs suivantes :
waitresource. Ressource convoitée par la tâche. waittime. Délai d'attente de la ressource en millisecondes. schedulerid. Planificateur associé à cette tâche. Consultez sys.dm_os_schedulers (Transact-SQL). hostname. Nom de la station de travail. isolationlevel. Niveau d'isolement des transactions en cours. Xactid. ID de la transaction qui contrôle la demande. currentdb. ID de la base de données. lastbatchstarted. Dernière fois qu'un processus client a démarré une exécution de traitement. lastbatchcompleted. Dernière fois qu'un processus client a terminé une exécution de traitement. clientoption1 et clientoption2. Options définies pour cette connexion cliente. Il s'agit d'un masque de bits qui contient des informations sur les options habituellement contrôlées par les instructions SET, telles que SET NOCOUNT et SET XACTABORT. associatedObjectId. Représente l'ID HoBT (Heap or B-tree, segment de mémoire ou arborescence binaire). |
Attributs des ressources |
RID. Identifie la ligne d'une table pour laquelle un verrou est détenu ou demandé. RID est représenté en tant que RID: db_id:file_id:page_no:row_no. Par exemple, RID: 6:1:20789:0. OBJECT. Identifie la table pour laquelle un verrou est détenu ou demandé. OBJECT est représenté en tant qu'OBJECT: db_id:object_id. Par exemple, TAB: 6:2009058193. KEY. Identifie la plage de clés d'un index pour laquelle un verrou est détenu ou demandé. KEY est représenté en tant que KEY: db_id:hobt_id (index key hash value). Par exemple, KEY: 6:72057594057457664 (350007a4d329). PAG. Identifie la ressource de page pour laquelle un verrou est détenu ou demandé. PAG est représenté en tant que PAG: db_id:file_id:page_no. Par exemple, PAG: 6:1:20789. EXT. Identifie la structure d'extension. EXT est représenté en tant que EXT: db_id:file_id:extent_no. Par exemple, EXT: 6:1:9. DB. Identifie le verrou de base de données. DB est représenté de l'une des manières suivantes :
APP. Identifie le verrou pris par une ressource d'application. APP est représenté en tant que APP: lock_resource. Par exemple, APP: Formf370f478. METADATA. Représente les ressources de métadonnées impliquées dans un blocage. Comme METADATA possède de nombreuses sous-ressources, la valeur retournée dépend de la sous-ressource bloquée. Par exemple, METADATA.USER_TYPE retourne user_type_id = <integer_value>. Pour plus d'informations sur les ressources et sous-ressources METADATA, consultez sys.dm_tran_locks (Transact-SQL). HOBT. Représente un segment de mémoire ou d'arbre B (B-Tree) impliqué dans un blocage. |
Non exclusif à cet indicateur de trace. |
Non exclusif à cet indicateur de trace. |
Exemple d'indicateur de trace 1204
L'exemple suivant illustre la sortie obtenue quand l'indicateur de trace 1204 est activé. Dans ce cas, la table du nœud 1 est un segment de mémoire sans index et la table du nœud 2 est un segment de mémoire avec un index non-cluster. La clé d'index du nœud 2 est en cours de mise à jour lorsque le blocage se produit.
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
RID: 6:1:20789:0 CleanCnt:3 Mode:X Flags: 0x2
Grant List 0:
Owner:0x0315D6A0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
Input Buf: Language Event:
BEGIN TRANSACTION
EXEC usp_p2
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0
Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)
Node:2
KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
Grant List 0:
Owner:0x0315D140 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
Input Buf: Language Event:
BEGIN TRANSACTION
EXEC usp_p1
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)
Exemple d'indicateur de trace 1222
L'exemple suivant illustre la sortie obtenue quand l'indicateur de trace 1222 est activé. Dans ce cas, une table est un segment de mémoire sans index et l'autre table un segment de mémoire avec un index non-cluster. Dans la seconde table, la clé d'index est en cours de mise à jour lorsque le blocage se produit.
deadlock-list
deadlock victim=process689978
process-list
process id=process6891f8 taskpriority=0 logused=868
waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444
transactionname=user_transaction
lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0
lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54
sbid=0 ecid=0 priority=0 transcount=2
lastbatchstarted=2005-09-05T11:22:42.733
lastbatchcompleted=2005-09-05T11:22:42.733
clientapp=Microsoft SQL Server Management Studio - Query
hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
isolationlevel=read committed (2) xactid=310444 currentdb=6
lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=AdventureWorks.dbo.usp_p1 line=6 stmtstart=202
sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;
frame procname=adhoc line=3 stmtstart=44
sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
EXEC usp_p1
inputbuf
BEGIN TRANSACTION
EXEC usp_p1
process id=process689978 taskpriority=0 logused=380
waitresource=KEY: 6:72057594057457664 (350007a4d329)
waittime=5015 ownerId=310462 transactionname=user_transaction
lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U
schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0
priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077
lastbatchcompleted=2005-09-05T11:22:44.077
clientapp=Microsoft SQL Server Management Studio - Query
hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
isolationlevel=read committed (2) xactid=310462 currentdb=6
lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=AdventureWorks.dbo.usp_p2 line=6 stmtstart=200
sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;
frame procname=adhoc line=3 stmtstart=44
sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
EXEC usp_p2
inputbuf
BEGIN TRANSACTION
EXEC usp_p2
resource-list
ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks.dbo.T2
id=lock3136940 mode=X associatedObjectId=72057594057392128
owner-list
owner id=process689978 mode=X
waiter-list
waiter id=process6891f8 mode=U requestType=wait
keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks.dbo.T1
indexname=nci_T1_COL1 id=lock3136fc0 mode=X
associatedObjectId=72057594057457664
owner-list
owner id=process6891f8 mode=X
waiter-list
waiter id=process689978 mode=U requestType=wait
Evénement Deadlock Graph de SQL Profiler
Il s'agit d'un événement propre au SQL Server Profiler qui présente une description graphique des tâches et des ressources impliquées dans un blocage. L'exemple suivant illustre la sortie obtenue à partir de SQL Server Profiler quand l'événement Deadlock Graph est activé.
.gif "Diagramme de flux logique montrant le blocage de processus utilisateur.")
Pour plus d'informations sur l'exécution du Deadlock Graph SQL Server Profiler, consultez Analyse des blocages à l'aide du Générateur de profils SQL Server.