Guide des interblocages

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Base de données SQL dans Microsoft Fabric

Cet article décrit en détail les interblocages dans le moteur de base de données SQL Server. Les interblocages sont provoqués par des verrous concurrents et simultanés dans la base de données, souvent dans les transactions en plusieurs étapes. Pour plus d’informations sur le verrouillage des transactions, consultez Guide du verrouillage des transactions et du contrôle de version de ligne.

Pour plus d’informations spécifiques sur l’identification et la prévention des interblocages spécifiques à la base de données Azure SQL, consultez Analyser et empêcher les interblocages dans la base de données Azure SQL.

Comprendre les interblocages

Un interblocage 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. Par exemple :

• La transaction A obtient un verrou partagé sur la ligne 1.

• La transaction B obtient un verrou partagé sur la ligne 2.

• La transaction A demande un verrou exclusif sur la ligne 2, mais elle est bloquée jusqu’à la fin de la transaction B qui libérera le verrou partagé sur la ligne 2.

• La transaction B demande un verrou exclusif sur la ligne 1, mais elle est bloquée jusqu’à la fin de la transaction A qui libérera le verrou partagé sur la ligne 1.

La transaction A ne peut pas se terminer tant que la transaction B n'est pas terminée, mais la transaction B est bloquée par la transaction A. Il s'agit d'une dépendance cyclique : la transaction A est dépendante de la transaction B, mais celle-ci ne peut pas s'exécuter car elle est dépendante de la transaction A.

Les deux transactions en interblocage attendent indéfiniment que la situation soit débloquée par un processus externe. Le processus de surveillance des interblocages du moteur de base de données SQL Server vérifie périodiquement l’existence de tâches concernées par un interblocage. S'il détecte une situation de dépendance cyclique, il désigne une des tâches comme victime et met fin à sa transaction avec un message d'erreur. Cela permet à l'autre tâche de terminer sa transaction. L'application qui exécutait la transaction abandonnée peut effectuer une nouvelle tentative qui réussit en général une fois que l'autre transaction se termine.

L'interblocage est souvent confondu avec le blocage ordinaire. Lorsqu'une transaction demande un verrou sur une ressource verrouillée par une autre transaction, elle attend que le verrou soit libéré. Par défaut, les transactions SQL Server n’ont pas de délai d’expiration, à moins que LOCK_TIMEOUT ne soit activé. La transaction qui demande le verrou est bloquée, mais pas indéfiniment puisqu'elle n'a rien fait pour bloquer la transaction détenant le verrou. La transaction qui détient le verrou va finir par se terminer et libérer le verrou ; l'autre transaction peut alors obtenir son verrou et s'exécuter normalement. Les interblocages sont résolus presque immédiatement, tandis que le blocage peut, en théorie, persister indéfiniment. Les interblocages sont parfois appelés « étreintes fatales ».

Un interblocage peut se produire sur tout système multithread, pas uniquement sur les systèmes de gestion de bases de données relationnelles. Il peut également concerner des ressources autres que les verrous sur les objets de base de données. Par exemple, un thread dans un système multithread peut acquérir une ou plusieurs ressources (par exemple, des blocs de mémoire). Si la ressource acquise appartient actuellement à une autre thread, la première thread devra éventuellement attendre que le thread propriétaire libère la ressource cible. le thread en attente a une dépendance sur le thread propriétaire de cette ressource particulière. Dans une instance du moteur de base de données de SQL Server, les sessions peuvent se bloquer lors de l’acquisition de ressources autres que des objets de base de données, notamment des blocs de mémoire ou des threads.

Dans l’illustration, la transaction T1 est dépendante de la transaction T2 pour la ressource de verrou de table Part. De même, la transaction T2 est dépendante de T1 pour la ressource de verrou de table Supplier. Comme ces dépendances forment un cycle, il y a interblocage entre les transactions T1 et T2.

Des interblocages peuvent également se produire quand une table est partitionnée et que le paramètre LOCK_ESCALATION de ALTER TABLE a la valeur AUTO. Quand LOCK_ESCALATION a la valeur AUTO, la concurrence augmente en permettant au moteur de base de données de SQL Server de verrouiller des partitions de table au niveau HoBT plutôt qu’au niveau des tables. Toutefois, lorsque des transactions distinctes maintiennent des verrous de partition dans une table et souhaitent un verrou sur l'autre partition de transactions, cela provoque un interblocage. Ce type d’interblocage peut être évité en réglant LOCK_ESCALATION sur TABLE. Toutefois, ce paramètre réduit la concurrence en forçant les mises à jour volumineuses vers une partition pour attendre un verrou de table.

Détecter et interrompre les interblocages

Un interblocage 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.

  

Le moteur de base de données SQL Server détecte automatiquement les cycles d’interblocage dans SQL Server. Le moteur de base de données SQL Server 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 des 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 interblocage.

• 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 interblocage, 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. Pour plus d’informations, consultez Utilisation de MARS (Multiple Active Result Sets) dans SQL Server Native Client.

  • 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 d’abord 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 le pseudocode 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 SQL Server 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 :

    

Détection de blocage

Toutes les ressources énumérées dans la section Ressources susceptibles de se bloquer sont visées par le dispositif de détection de blocage du moteur de base de données SQL Server. 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 SQL Server. 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 descend 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 SQL Server refait passer l’intervalle de recherche à 5 secondes.

• Si un interblocage vient d'être détecté, les prochains threads qui doivent attendre un verrou entrent dans le cycle d’interblocage. Les premières attentes de verrous postérieures à une détection de blocage déclenchent 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 lance immédiatement le détecteur de blocage. Si cette attente de verrou est impliquée dans un blocage, celui-ci est détecté immédiatement et non lors de la prochaine recherche de blocage.

En règle générale, le moteur de base de données SQL Server 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 le thread est en attente. Il recherche ensuite 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 SQL Server met fin à un blocage en choisissant l’un des threads comme victime. Le moteur de base de données SQL Server 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 SQL Server 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 du common language runtime (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 des informations sur l’interblocage, le moteur de base de données SQL Server fournit des outils de surveillance sous la forme de la session XEvent system_health, de deux indicateurs de trace ainsi que de l’événement Deadlock Graph dans SQL Profiler.

Événement étendu de blocage

Dans SQL Server 2012 (11.x) et versions ultérieures, l’Extended Event (XEvent) xml_deadlock_report doit être utilisé à la place de la classe d’événements Deadlock Graph dans SQL Trace ou SQL Profiler.

En cas d’interblocage, la session system_health capture déjà tous les événements XEvent xml_deadlock_report qui contiennent le graphique d’interblocage. Étant donné que la session system_health est activée par défaut, il n’est pas nécessaire qu’une session XEvent distincte soit configurée pour capturer les informations d’interblocage. Aucune action supplémentaire n’est requise pour capturer les informations d’interblocage avec l’événement XEvent xml_deadlock_report.

L’événement Deadlock Graph capturé a généralement trois nœuds distincts :

• victim-list. Identificateur du processus victime de l’interblocage.
• process-list. Informations sur tous les processus impliqués dans l’interblocage.
• resource-list. Informations sur les ressources impliquées dans l’interblocage.

Lors de l’ouverture du fichier ou de la mémoire tampon en anneau de la session system_health, si l’événement XEvent xml_deadlock_report est enregistré, Management Studio affiche une représentation graphique des tâches et ressources impliquées dans un interblocage, comme indiqué dans l’exemple suivant :

La requête suivante peut afficher tous les événements d’interblocage capturés par la mémoire tampon en anneau de la session system_health :

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
       xdr.query('.') AS [Event_Data]
FROM (SELECT CAST ([target_data] AS XML) AS Target_Data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
      CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

Voici le jeu de résultats.

L’exemple suivant illustre la sortie après avoir sélectionné le lien en Event_Data dans la première ligne du résultat :

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

Pour plus d’informations, consultez Utiliser la session system_health

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. L’indicateur de trace 1204 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.

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=2022-02-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=2022-02-05T11:22:42.733
   lastbatchcompleted=2022-02-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=AdventureWorks2022.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=2022-02-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=2022-02-05T11:22:44.077
   lastbatchcompleted=2022-02-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=AdventureWorks2022.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=AdventureWorks2022.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=AdventureWorks2022.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 à SQL Profiler qui présente une description graphique des tâches et des ressources impliquées dans un interblocage. L’exemple suivant illustre la sortie obtenue à partir de SQL Profiler quand l’événement Deadlock Graph est activé.

Pour plus d’informations sur l’événement de blocage, consultez Classe d’événements Lock:Deadlock. Pour plus d’informations sur l’exécution de l’événement Deadlock Graph SQL Profiler, consultez Enregistrer les événements du graphique d’interblocage (SQL Server Profiler).

Il existe des équivalents pour les classes d’événements SQL Trace dans Extended Events, consultez Voir les équivalents d’Extended Events aux classes d’événements SQL Trace. Extended Events sont recommandés sur SQL Traces.

Gérer les interblocages

Quand une instance du moteur de base de données SQL Server choisit une transaction comme victime d’un interblocage, elle met fin au lot en cours, annule la transaction, puis retourne le message d’erreur 1205 à l’application.

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Dans la mesure où toute application soumettant des requêtes Transact-SQL peut être choisie comme victime de blocage, les applications doivent intégrer un gestionnaire d’erreurs capable d’intercepter le message d’erreur 1205. Si une application n'intercepte pas cette erreur, elle peut continuer en ignorant que sa transaction a été annulée, et des erreurs peuvent se produire.

L'implémentation d'un gestionnaire d'erreurs capable d'intercepter le message d'erreur 1205 permet à une application de gérer les situations de blocage et de réagir en conséquence, par exemple en re-soumettant automatiquement la requête impliquée dans le blocage. Cette nouvelle soumission automatique rend la gestion de l’interblocage entièrement transparente pour l’utilisateur.

L'application doit marquer un bref temps d'arrêt avant de soumettre à nouveau la requête. Cela permet à l'autre transaction impliquée dans le blocage d'aboutir et de libérer ses verrous qui faisaient partie du cycle de blocage. Les risques qu'un blocage se reproduise au moment où la requête de nouveau soumise demande ses verrous sont ainsi réduits.

Gérer avec un bloc TRY...CATCH

Vous pouvez utiliser un bloc TRY...CATCH pour gérer les interblocages. L’erreur de la victime d’interblocage 1205 peut être interceptée par le bloc CATCH, et la transaction peut être restaurée jusqu’à ce que les threads soient déverrouillés.

Pour plus d’informations, consultez Gestion des interblocages.

Réduire les interblocages

Même si les interblocages ne peuvent pas être totalement évités, le respect de certaines conventions de codage peut minimiser le risque d’en générer. La réduction des blocages peut augmenter le débit des transactions et réduire la charge du système, car il y a moins de transactions :

• restaurées, en annulant ce qui a été accompli par la transaction ;
• resoumises par les applications, car ces transactions ont été restaurées lors du blocage.

Pour réduire le nombre de blocages :

• Accédez aux objets dans le même ordre.
• Évitez les interactions utilisateur dans les transactions.
• Créez des transactions courtes dans le même traitement.
• Utilisez un niveau d'isolement plus faible.
• Utilisez un niveau d'isolement basé sur le contrôle de version de ligne.
  • Affectez à l’option de base de données READ_COMMITTED_SNAPSHOT la valeur ON pour activer les transactions read-committed afin d’utiliser la gestion de versions de ligne.
  • Utilisez un isolement d’instantané.
• Utilisez des connexions liées.

Accédez aux objets dans le même ordre

Si toutes les transactions concurrentes accèdent aux objets dans le même ordre, le risque de blocage diminue. Par exemple, si deux transactions concurrentes obtiennent un verrou sur la table Supplier et ensuite sur la table Part, l’une des transactions est bloquée sur la table Supplier jusqu’à ce que l’autre transaction se termine. Après la validation ou la restauration de la première transaction, la seconde continue et aucun blocage ne se produit. L'utilisation de procédures stockées pour toutes les modifications de données peut standardiser l'ordre d'accès aux objets.

Évitez les interactions utilisateur dans les transactions

Évitez d'écrire des transactions comprenant une interaction utilisateur, car la vitesse d'exécution des traitements sans intervention de l'utilisateur est beaucoup plus rapide que la vitesse à laquelle un utilisateur doit répondre manuellement aux requêtes telles que la demande d'un paramètre requis par une application. Par exemple, si une transaction attend une entrée de la part de l'utilisateur, et si ce dernier va déjeuner ou rentre chez lui pour le week-end, l'utilisateur empêche la transaction de se terminer. Ceci dégrade les performances du système, car tous les verrous détenus par la transaction ne sont libérés qu'une fois la transaction validée ou restaurée. Même si une situation d’interblocage ne se produit pas, d’autres transactions en attente de la même ressource sont bloquées en attendant que la transaction se termine.

Transactions courtes dans un seul traitement

Un blocage se produit souvent lorsque plusieurs transactions longues sont exécutées de manière concurrente dans la même base de données. Plus la transaction est longue, plus la durée de détention du verrou exclusif ou de mise à jour est importante, ce qui bloque les autres activités et peut entraîner une situation de blocage.

La création de transactions courtes dans un seul traitement limite les allers-retours sur le réseau en réduisant les délais potentiels d'achèvement de la transaction et de suppression des verrous.

Pour plus d’informations sur la mise à jour des verrous, consultez Guide du verrouillage des transactions et du contrôle de version de ligne.

Utilisez un niveau d’isolement plus faible

Déterminez si une transaction peut être exécutée à un niveau d'isolement faible. L'implémentation de la lecture validée (read committed) permet à une transaction de lire des données lues auparavant (non modifiées) par une autre transaction, sans attendre la fin de la première transaction. Un niveau d'isolement faible (comme la lecture validée, par exemple) permet de conserver les verrous partagés pendant une durée inférieure à celle d'un niveau d'isolement supérieur, comme le niveau sérialisable. et réduit ainsi la contention de verrouillage.

Niveau d’isolement basé sur le contrôle de version de ligne

Quand l’option de base de données READ_COMMITTED_SNAPSHOT a la valeur ON, une transaction qui s’exécute sous un niveau d’isolation read committed utilise le contrôle de version de ligne plutôt que les verrous partagés lors des opérations de lecture.

L'isolement d'instantané utilise également le contrôle de version de ligne, qui n'emploie pas de verrous partagés pendant les opérations de lecture. Pour qu’une transaction puisse s’exécuter sous une isolation d’instantané, l’option de base de données ALLOW_SNAPSHOT_ISOLATION doit avoir la valeur ON.

Implémentez ces niveaux d'isolement pour minimiser les blocages pouvant survenir entre les opérations de lecture et d'écriture.

Utiliser des connexions liées

En utilisant des connexions liées, deux connexions ou plus ouvertes par la même application peuvent coopérer entre elles. Tout verrou acquis par la connexion secondaire apparaît comme s'il avait été posé par la connexion primaire et vice-versa. Ils ne se bloquent donc pas réciproquement.

Arrêter une transaction

Dans un scénario d’interblocage, la transaction victime est automatiquement arrêtée et restaurée. Il n’est pas nécessaire d’arrêter une transaction dans un scénario d’interblocage.

Provoquer un interblocage

Pour provoquer un interblocage, vous devez connecter deux sessions à la base de données AdventureWorksLT2019. Nous allons faire référence à ces sessions en tant que Session A et Session B. Vous pouvez créer ces deux sessions simplement en créant deux fenêtres de requête dans SQL Server Management Studio (SSMS).

Dans la session A, exécutez le code Transact-SQL suivant. Ce code démarre une transaction explicite et exécute une instruction unique qui met à jour la table SalesLT.Product. Pour ce faire, la transaction acquiert un verrou de mise à jour (U) sur une ligne de la table SalesLT.Product qui est convertie en verrou exclusif (X). Nous laissons la transaction ouverte.

BEGIN TRANSACTION;

UPDATE SalesLT.Product
    SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

À présent, dans la session B, exécutez le code Transact-SQL suivant. Ce code ne démarre pas explicitement une transaction. Il travaille plutôt en mode de validation automatique. Cette instruction met à jour la table SalesLT.ProductDescription. La mise à jour supprime un verrou de mise à jour (U) sur 72 lignes de la table SalesLT.ProductDescription. La requête se joint à d’autres tables, notamment la table SalesLT.Product.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

Pour effectuer cette mise à jour, la session B a besoin d’un verrou partagé (S) sur les lignes de la table SalesLT.Product, y compris la ligne verrouillée par la session A. La session B est bloquée sur SalesLT.Product.

Revenez à la session A. Exécutez l’instruction Transact-SQL suivante. Cette opération exécute une deuxième instruction UPDATE dans le cadre de la transaction ouverte.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

La deuxième instruction de mise à jour de la session A est bloquée par la session B sur SalesLT.ProductDescription.

La session A et la session B se bloquent mutuellement. Aucune des transactions ne peut se poursuivre, car chacune a besoin d’une ressource verrouillée par l’autre.

Au bout de quelques secondes, le processus de surveillance des interblocages identifie que les transactions de la session A et de la session B se bloquent mutuellement, et qu’aucune ne peut progresser. Un interblocage doit alors se produire, avec la session A comme victime de celui-ci. La session B se termine avec succès. Un message d’erreur apparaît dans la session A avec un texte semblable à l’exemple suivant :

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

Si un interblocage n’est pas déclenché, vérifiez que READ_COMMITTED_SNAPSHOT est activé dans votre exemple de base de données. Les interblocages peuvent se produire dans n’importe quelle configuration de base de données, mais cet exemple nécessite que READ_COMMITTED_SNAPSHOT soit activé.

Vous pouvez ensuite afficher les détails de l’interblocage dans la cible ring_buffer de la session Extended Events system_health, qui est activée et active par défaut dans SQL Server. Considérez la requête suivante :

WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
    FROM sys.dm_xe_sessions AS xs
         INNER JOIN sys.dm_xe_session_targets AS xst
             ON xs.[address] = xst.event_session_address
    WHERE xs.[name] = 'system_health'
          AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS c
      CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;

Vous pouvez afficher le code XML dans la colonne Deadlock_XML à l’intérieur de SSMS en sélectionnant la cellule qui s’affiche sous forme de lien hypertexte. Enregistrez cette sortie sous la forme d’un fichier .xdl, fermez, puis ouvrez de nouveau le fichier .xdl dans SSMS pour le diagramme d’interblocage visuel. Votre diagramme d’interblocage devrait ressembler à l’image suivante.

Verrouillage optimisé et interblocages

S’applique à : Azure SQL Database

Le Verrouillage optimisé a introduit une méthode différente pour verrouiller les mécanismes qui modifient la façon dont les interblocages impliquant des verrous TID exclusifs peuvent être signalés. Sous chaque ressource du rapport d’interblocage <resource-list>, chaque élément <xactlock> signale les ressources sous-jacentes et des informations spécifiques pour les verrous de chaque membre d’un interblocage.

Prenez l’exemple suivant où le verrouillage optimisé est activé :

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);
GO

Les commandes Transact-SQL suivantes dans deux sessions créent un interblocage sur la table t2 :

Dans la session 1 :

--session 1
BEGIN TRANSACTION foo;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

Dans la session 2 :

--session 2:
BEGIN TRANSACTION bar;

UPDATE t2
    SET b = b + 10
WHERE a = 2;

Dans la session 1 :

--session 1:
UPDATE t2
    SET b = b + 100
WHERE a = 2;

Dans la session 2 :

--session 2:
UPDATE t2
    SET b = b + 20
WHERE a = 1;

Ce scénario d’instructions concurrentes UPDATE entraîne un interblocage. Dans ce cas, une ressource de verrouillage de clés, où chaque session contient un verrou X sur son propre TID et attend le verrou S sur l’autre TID, ce qui entraîne un interblocage. Le code XML suivant, capturé en tant que rapport d’interblocage, contient des éléments et des attributs spécifiques au verrouillage optimisé :

Contenu connexe

• Aperçu des événements étendus
• sys.dm_tran_locks (Transact-SQL)
• Deadlock Graph, classe d’événements
• Interblocages avec le niveau d’isolation REPEATABLE READ
• Classe d'événements Lock:Deadlock Chain
• Classe d'événements Lock:Deadlock
• SET DEADLOCK_PRIORITY (Transact-SQL)
• Analyser et empêcher les blocages dans Azure SQL Database
• Ouvrir, voir et imprimer un fichier de blocage dans SSMS (SQL Server Management Studio)