Partager via

Analyser et prévenir les blocages dans Base de données Azure SQL et Base de données SQL Fabric

  • Article

S’applique à :Base de données Azure SQLBase de données SQL dans Fabric

Cet article explique comment identifier les interblocages, utiliser les graphiques d’interblocage et le Magasin des requêtes pour identifier les requêtes au sein de l’interblocage, et planifier et tester les modifications pour empêcher les interblocages de se reproduire. Cet article s’applique à Base de données SQL et à la base de données SQL Fabric, qui partage de nombreuses fonctionnalités de la Base de données Azure SQL.

Cet article se concentre sur l’identification et l’analyse des interblocages dus à une contention de verrouillage. Pour en savoir plus sur d’autres types d’interblocages, consultez Ressources susceptibles de se bloquer.

Comment se produisent les blocages

Chaque nouvelle base de données dans Azure SQL Database a le paramètre de base de données isolation de capture instantanée de lecture validée (RCSI) activé par défaut. Les risques de blocage entre les sessions qui lisent des données et celles qui en écrivent sont réduits en mode RCSI, car celui-ci utilise le contrôle de version de ligne pour augmenter la concurrence. Cependant, le blocage et les interblocages peuvent toujours se produire dans les bases de données Azure SQL Database, car :

  • Les requêtes qui modifient les données peuvent les bloquer les unes les autres.

  • Les requêtes peuvent s’exécuter sous des niveaux d’isolation qui augmentent le blocage. Les niveaux d’isolation peuvent être spécifiés via des méthodes de bibliothèque de client, des indicateurs de requête ou SET TRANSACTION ISOLATION LEVEL dans Transact-SQL.

  • RCSI peut être désactivé, ce qui entraîne l’utilisation par la base de données de verrous partagés (S) pour protéger les instructions SELECT exécutées sous le niveau d’isolation de lecture validée. Cela pourrait augmenter le blocage et les interblocages.

Exemple d’interblocage

Un interblocage survient lorsqu’au moins deux tâches se bloquent mutuellement de façon permanente parce que chacune d’entre elles place un verrou sur une ressource que l’autre tâche essaie de verrouiller. Un interblocage est également appelé « dépendance cyclique » : dans le cas d’un interblocage à deux tâches, la transaction A est dépendante de la transaction B, mais cette dernière ne peut pas s’exécuter car elle est dépendante de la transaction A.

Par exemple :

  1. La session A entame une transaction explicite et exécute une instruction de mise à jour qui acquiert un verrou de mise à jour (U) sur une ligne de la table SalesLT.Product qui est convertie en verrou exclusif (X).

  2. La session B exécute une instruction de mise à jour qui modifie la table SalesLT.ProductDescription. L’instruction de mise à jour se joint à la table SalesLT.Product pour rechercher les lignes appropriées à mettre à jour.

    • La session B acquiert un verrou de mise à jour (U) sur 72 lignes de la table SalesLT.ProductDescription.

    • La session B a besoin d’un verrou partagé 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.

  3. La session A poursuit sa transaction et exécute maintenant une mise à jour sur la table SalesLT.ProductDescription. La session A est bloquée par la session B sur SalesLT.ProductDescription.

Diagramme montrant deux sessions dans un interblocage. Chaque session possède une ressource dont l’autre processus a besoin pour continuer.

Toutes les transactions impliquées dans un interblocage attendent indéfiniment, sauf si l’une des transactions participantes est restaurée, par exemple, parce que sa session a été interrompue.

Le processus de surveillance des interblocages du moteur de base de données vérifie périodiquement l’existence de tâches concernées par un interblocage. Si le moniteur d’interblocage détecte une dépendance cyclique, il choisit l’une des tâches en tant que victime et met fin à sa transaction avec l’erreur 1205 : Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. Briser le blocage de cette façon permet à l’autre tâche ou aux tâches dans le blocage d’effectuer leurs transactions.

Remarque

Pour en savoir plus sur les critères de sélection d’une victime d’interblocage, consultez la section Liste des processus d’interblocage de cet article.

Diagramme d’un interblocage entre deux sessions. Une session est choisie comme victime d’interblocage.

L’application dont la transaction a été choisie comme victime de l’interblocage doit réessayer d’exécuter la transaction, qui se termine généralement après l’exécution des autres transactions impliquées dans l’interblocage.

Il est recommandé d’introduire un court délai aléatoire avant de réessayer pour éviter de rencontrer à nouveau le même blocage. Pour en savoir plus, consultez Logique de nouvelle tentative pour les erreurs temporaires.

Niveau d’isolement par défaut dans Azure SQL Database

Par défaut, les nouvelles bases de données d’Azure SQL Database activent le mode RCSI (Read Committed Snapshot Isolation). RCSI modifie le comportement du niveau d'isolation de lecture validée pour utiliser le contrôle de version de ligne afin d’assurer la cohérence au niveau des instructions sans utiliser de verrous partagés (S) pour les instructions SELECT.

Lorsque le mode RCSI est activé :

  • Les instructions lisant des données ne bloquent pas les instructions modifiant les données.
  • Les instructions modifiant des données ne bloquent pas les instructions qui lisent les données.

Le Niveau d’isolement de capture instantanée est également activé par défaut pour les nouvelles bases de données dans Azure SQL Database. L’isolement d’instantané est un niveau d’isolement supplémentaire basé sur des lignes qui fournit une cohérence au niveau des transactions pour les données, et qui utilise des versions de ligne pour sélectionner des lignes à mettre à jour. Pour utiliser l’isolement d’instantané, les requêtes ou les connexions doivent définir explicitement leur niveau d’isolement des transactions sur SNAPSHOT. Cela peut être effectué uniquement lorsque l’isolation d’instantané est activée pour la base de données.

Vous pouvez identifier si l’isolement RCSI et/ou d’instantané est activée avec Transact-SQL. Connectez-vous à votre base de données dans Azure SQL Database et exécutez la requête suivante :

SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Si RCSI est activé, la colonne is_read_committed_snapshot_on retourne la valeur 1. Si l’isolation d’instantané est activée, la colonne snapshot_isolation_state_desc retourne la valeur ON.

Si RCSI est désactivé pour une base de données dans Azure SQL Database, examinez pourquoi RCSI a été désactivé avant de le réactiver. Le code de l’application peut s’attendre à ce que les requêtes lisant des données soient bloquées par les requêtes écrivant des données, ce qui entraîne des résultats incorrects dus à des conditions de concurrence lorsque le RCSI est activé.

Interpréter les événements d’interblocage

Un événement d’interblocage est émis lorsque le gestionnaire d’interblocage d’Azure SQL Database détecte un interblocage et sélectionne une transaction comme victime. En d’autres termes, si vous configurez des alertes pour les interblocages, la notification se déclenche après la résolution d’un interblocage individuel. Il n’existe aucune action de l’utilisateur qui doit être effectuée pour ce blocage. Les applications doivent être écrites pour inclure logique de nouvelle tentative afin qu’elles continuent automatiquement après la réception de l’erreur 1205 : Transaction (Process ID <N>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Toutefois, il est utile de configurer des alertes, car les interblocages peuvent se reproduire. Les alertes d’interblocage vous permettent d’examiner si un modèle d’interblocages récurrents se produit dans votre base de données, auquel cas vous pouvez choisir d’agir pour empêcher la répétition des interblocages. Pour en savoir plus sur les alertes, consultez la section Surveiller et alerter sur les interblocages de cet article.

Principales méthodes à utiliser pour éviter les interblocages

L’approche la moins risquée pour empêcher les interblocages de se reproduire est généralement de paramétrer des index non cluster de manière à optimiser les requêtes impliquées dans l’interblocage.

  • Avec cette approche le risque est faible, car le paramétrage d’index non cluster ne nécessite pas de modifications du code de requête lui-même, ce qui réduit le risque d’erreur de la part de l’utilisateur lors de la réécriture du code Transact-SQL qui provoque le renvoi de données incorrectes à l’utilisateur.

  • Un bon paramétrage d’index non cluster facilite la recherche des données à lire et à modifier pour les requêtes. En réduisant la quantité de données auxquelles une requête doit accéder, la probabilité de blocage est réduite et les interblocages sont souvent évités.

Dans certains cas, la création ou le paramétrage d’un index cluster peut réduire les blocages et interblocages. Étant donné que l’index cluster est inclus dans toutes les définitions d’index non cluster, la création ou la modification d’un index cluster peut s’avérer gourmande en E/S et très longue sur les tables volumineuses comportant des index non cluster. Pour en savoir plus, consultez Recommandations sur la conception d’index cluster.

Lorsque le paramétrage des index ne permet pas d’éviter les interblocages, d’autres méthodes sont disponibles :

  • Si l’interblocage ne survient que lorsqu’un plan particulier est choisi pour l’une des requêtes impliquées dans l’interblocage, le fait de forcer un plan de requête auprès du Magasin des requêtes peut empêcher les interblocages de se reproduire.

  • La réécriture du code Transact-SQL pour une ou plusieurs transactions impliquées dans l’interblocage peut également prévenir les interblocages. Le fractionnement des transactions explicites en plus petites transactions nécessite un codage et des tests minutieux afin de garantir la validité des données en cas de modifications simultanées.

Pour en savoir plus sur chacune de ces approches, consultez la section Empêcher un interblocage de se reproduire de cet article.

Surveiller et alerter sur les interblocages

Dans cet article, nous utilisons l’exemple de base de données AdventureWorksLT pour configurer des alertes pour les interblocages, provoquer un exemple d’interblocage, analyser le graphique d’interblocage pour l’exemple d’interblocage et tester les modifications pour empêcher l’interblocage de se reproduire.

Nous utilisons le client SQL Server Management Studio (SSMS) dans cet article, car il contient des fonctionnalités permettant d’afficher des graphiques d’interblocage en mode visuel interactif. Vous pouvez utiliser d’autres clients tels que Azure Data Studio pour suivre les exemples, mais vous ne pourriez voir que des graphiques d’interblocage au format XML.

Créer la base de données AdventureWorksLT

Pour suivre les exemples, créez une base de données dans Azure SQL Database et sélectionnez Exemples de données dans le champ Source de données.

Pour obtenir des instructions détaillées sur la création de AdventureWorksLT via le portail Azure, Azure CLI ou PowerShell, sélectionnez l’approche de votre choix dans Démarrage rapide : Créer une base de données Azure SQL Database.

Configurer des alertes pour les interblocages sur le portail Azure

Pour configurer des alertes pour les événements d’interblocage, suivez la procédure décrite dans l’article Créer des alertes pour Azure SQL Database et Azure Synapse Analytics à l’aide du portail Azure.

Sélectionnez Interblocages comme nom de signal pour l’alerte. Configurez le Groupe d’actions pour qu’il vous avertisse via la méthode de votre choix, par exemple le type d’action E-mail/SMS/Push/Voix.

Collecter des graphiques d’interblocage dans Azure SQL Database avec des événements étendus

Les graphiques d’interblocage constituent une source d’informations très intéressante sur les processus et les verrous impliqués dans un interblocage. Pour collecter des graphiques d’interblocage avec des événements étendus (XEvents) dans Azure SQL Database, capturez l’événementsqlserver.database_xml_deadlock_report.

Vous pouvez collecter des graphiques d’interblocage avec XEvents à l’aide de la cible de mémoire tampon en anneau ou d’une cible de fichier d’événements. Les considérations relatives à la sélection du type de cible approprié sont résumées dans le tableau suivant :

Approche Avantages Considérations Scénarios d’usage
Cible de mémoire tampon en anneau - Configuration simple avec Transact-SQL uniquement. - Les données d’événement sont effacées lorsque la session XEvents est arrêtée pour une raison quelconque, comme mettre la base de données hors ligne ou effectuer un basculement de base de données.

- Les ressources de base de données sont utilisées pour conserver des données dans la mémoire tampon en anneau et interroger les données de session.		 - Collecter des exemples de données de trace pour le test et l’apprentissage.

- Créer pour des besoins à court terme si vous ne pouvez pas configurer une session à l’aide d’une cible de fichier d’événement immédiatement.

- Utilisez comme zone d'atterrissage pour les données de trace, lorsque vous avez configuré un processus automatisé pour enregistrer les données de trace dans une table.
Cible de fichier d’événements - Conserve les données d’événement dans un objet blob dans stockage Azure afin que les données soient disponibles même après l’arrêt de la session.

- Les fichiers d’événements peuvent être téléchargés à partir du portail Azure ou 'Explorateur Stockage Azure et analysés localement, ce qui ne nécessite pas l’utilisation de ressources de base de données pour interroger les données de session.		 - La configuration est plus complexe et nécessite la configuration d’un conteneur de stockage Azure et d’informations d’identification au niveau de la base de données. - Utilisation générale lorsque vous souhaitez que les données d’événement soient conservées même après l’arrêt de la session d’événements.

- Vous souhaitez exécuter une trace qui génère de plus grandes quantités de données d’événement que vous ne voudriez conserver en mémoire.

Sélectionnez le type de cible à utiliser :

La cible de mémoire tampon en anneau est pratique et facile à configurer, mais sa capacité est limitée, ce qui peut entraîner la perte d’événements plus anciens. La mémoire tampon en anneau ne conserve pas les événements dans le stockage et la cible de mémoire tampon en anneau est effacée lorsque la session XEvents est arrêtée. Cela signifie que les XEvents collectés ne sont pas disponibles lorsque le moteur de base de données redémarre pour une raison quelconque, par exemple un basculement. La cible de mémoire tampon en anneau convient le mieux à l’apprentissage et aux besoins à court terme si vous n’avez pas la possibilité de configurer immédiatement une session XEvents sur une cible de fichier d’événements.

Cet exemple de code crée une session XEvents qui capture des graphiques d’interblocage en mémoire à l’aide de la cible de mémoire tampon en anneau. La mémoire maximale autorisée pour la cible de mémoire tampon en anneau est de 4 Mo et la session s’exécute automatiquement lorsque la base de données est en ligne, par exemple après un basculement.

Pour créer, puis démarrer une session XEvents pour l’événement sqlserver.database_xml_deadlock_report qui écrit dans la cible de mémoire tampon en anneau, connectez-vous à votre base de données et exécutez le code Transact-SQL suivant :

CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO

Cause d’un interblocage dans AdventureWorksLT

Remarque

Cet exemple fonctionne dans la base de données AdventureWorksLT avec le schéma et les données par défaut lorsque RCSI est activé. Consultez la section Créer la base de données AdventureWorksLT pour obtenir des instructions sur la création de la base de données.

Pour provoquer un blocage, vous devez connecter deux sessions à la base de données AdventureWorksLT. Nous appelons ces sessions session A et session B.

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, session B a besoin d’un verrou partagé (S) sur les lignes de la table SalesLT.Product, y compris la ligne verrouillée par Session A. 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 dans Session A est bloquée par la Session B sur le 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.

Après quelques secondes, le moniteur d’interblocage identifie que les transactions dans session A et session B se bloquent mutuellement, et que ni l’un ni l’autre ne peut progresser. Un interblocage doit alors se produire, avec la session A comme victime de celui-ci. Un message d’erreur s’affiche dans session A avec du texte similaire à ce qui suit :

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

La session B s'achève avec succès.

Si vous avez configuré des alertes pour les interblocages sur le portail Azure, vous recevrez une notification peu après l’interblocage.

Afficher les graphiques d’interblocage à partir d’une session XEvents

Si vous configurez une session XEvents pour collecter des interblocages et qu’un interblocage se produit après le démarrage de la session, vous pouvez afficher une représentation graphique interactive du graphe d’interblocage et le code XML pour le graphe d’interblocage.

Différentes méthodes sont disponibles pour obtenir des informations sur l’interblocage pour la cible de mémoire tampon en anneau et les cibles de fichiers d’événements. Sélectionnez la cible que vous avez utilisée pour votre session XEvents :

Si vous configurez une session XEvents qui écrit dans la mémoire tampon en anneau, vous pouvez interroger les informations relatives à l’interblocage en utilisant le code Transact-SQL suivant. Avant d’exécuter la requête, remplacez la valeur de @tracename par le nom de votre session XEvents.

DECLARE @tracename AS sysname = N'deadlocks';

WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'ring_buffer'),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Afficher et enregistrer un graphique d’interblocage au format XML

L’affichage d’un graphique d’interblocage au format XML vous permet de copier le paramètre inputbuffer des instructions Transact-SQL impliquées dans l’interblocage. Vous pouvez également analyser les interblocages dans un format textuel.

Si vous avez utilisé une requête Transact-SQL pour renvoyer des informations sur le graphique d’interblocage, pour afficher le code XML de celui-ci, sélectionnez la valeur figurant dans la colonne deadlock_xml sur n’importe quelle ligne pour ouvrir le code XML du graphique d’interblocage dans une nouvelle fenêtre de SSMS.

Le code XML de cet exemple de graphique d’interblocage est le suivant :

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Silver';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Pour enregistrer le graphique d’interblocage sous forme de fichier XML :

  1. Sélectionnez Fichier et Enregistrer sous....
  2. Dans le champ Type de fichier, conservez la valeur par défaut, Fichiers XML (*.xml).
  3. Dans le champ Nom de fichier, entrez le nom de votre choix.
  4. Sélectionnez Enregistrer.

Enregistrer un graphique d’interblocage sous forme de fichier XDL pouvant être affiché de manière interactive dans SSMS

L’affichage d’une représentation interactive d’un graphique d’interblocage offre un rapide aperçu des processus et des ressources impliqués dans l’interblocage et permet d’identifier rapidement la victime de l’interblocage.

Pour enregistrer un graphique d’interblocage sous forme de fichier pouvant être affiché graphiquement par SSMS :

  1. Sélectionnez la valeur figurant dans la colonne deadlock_xml sur n’importe quelle ligne pour ouvrir le code XML du graphique d’interblocage dans une nouvelle fenêtre de SSMS.

  2. Sélectionnez Fichier et Enregistrer sous....

  3. Dans le champ Type de fichier, sélectionnez Tous les fichiers.

  4. Dans le champ Nom de fichier, entrez le nom de votre choix et sélectionnez l’extension .xdl.

  5. Sélectionnez Enregistrer.

    Capture d’écran dans SSMS de l’enregistrement d’un fichier XML de graphique d’interblocage dans un fichier avec l’extension xsd.

  6. Fermez le fichier en sélectionnant la croix (X) dans l’onglet situé en haut de la fenêtre, ou en sélectionnant Fichier, puis Fermer.

  7. Rouvrez le fichier dans SSMS en sélectionnant Fichier, Ouvrir, puis Fichier. Sélectionnez le fichier que vous avez enregistré au format .xdl.

    Le graphique d’interblocage s’affiche désormais dans SSMS avec une représentation visuelle des processus et des ressources impliqués dans l’interblocage.

    Capture d’écran d’un fichier XDL ouvert dans SSMS. Le graphique d’interblocage s’affiche graphiquement, avec des processus indiqués par des ovales et des ressources de verrouillage sous forme de rectangles.

Analyser un interblocage pour Azure SQL Database

Un graphique d’interblocage comporte généralement trois nœuds :

  • Victim-list. Identificateur du processus victime de l’interblocage.

  • Process-list. Informations sur tous les processus impliqués dans l’interblocage. Les graphiques d’interblocage utilisent le terme « processus » pour représenter une session exécutant une transaction.

  • Resource-list. Informations sur les ressources impliquées dans l’interblocage.

Lors de l’analyse d’un interblocage, il est utile de parcourir ces nœuds.

Liste des victimes d’interblocage

La liste des victimes d’interblocage présente le processus choisi comme victime de l’interblocage. Dans la représentation visuelle d’un graphique d’interblocage, les processus sont représentés par des ovales. Le processus victime de l’interblocage est reconnaissance à la croix (X) qui recouvre l’ovale.

Capture d’écran de l’affichage visuel d’un interblocage. L’ovale représentant le processus sélectionné comme victime est barré d’un X.

Dans la vue XML d’un graphique d’interblocage, le nœud victim-list attribue un ID au processus qui a été victime de l’interblocage.

Dans notre exemple d’interblocage, l’ID du processus victime est process24756e75088. Nous pouvons utiliser cet ID lors de l’examen des nœuds process-list et resource-list pour en savoir plus sur le processus victime et les ressources qu’il verrouillait ou demandait à verrouiller.

Liste des processus d’interblocage

La liste des processus d’interblocage est une source d’informations très utile sur les transactions impliquées dans le blocage.

La représentation visuelle du graphique d’interblocage n’affiche qu’un sous-ensemble des informations contenues dans le code XML du graphique d’interblocage. Les ovales contenus dans le graphique d’interblocage représentent le processus et affichent différents types d’informations :

  • ID de session, également appelé SPID.

  • Priorité d’interblocage de la session. 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. Dans cet exemple, les deux sessions ont la même priorité d’interblocage.

  • Part du journal des transactions utilisé par la session, en octets. Si les deux sessions ont la même priorité d’interblocage, le processus de surveillance des interblocages choisit la session la moins coûteuse à restaurer comme victime de l’interblocage. Le coût est déterminé en comparant le nombre d’octets du journal écrit à ce stade dans chaque transaction.

    Dans notre exemple d’interblocage, session_id 89 avait utilisé une quantité inférieure de journal des transactions et a été sélectionné comme victime du blocage.

En outre, vous pouvez afficher la mémoire tampon d’entrée de la dernière instruction exécutée dans chaque session avant l’interblocage en passant la souris sur chaque processus. La mémoire tampon d’entrée apparaît dans une info-bulle.

Capture d’écran d’un graphique de blocage affiché visuellement dans SSMS. Deux ovales représentent des processus. La mémoire tampon d’entrée d’un processus s’affiche.

Des informations supplémentaires sont disponibles pour les processus dans la vue XML du graphique d’interblocage, notamment :

  • Informations d’identification de la session, telles que le nom du client, le nom de l’hôte et le nom de connexion.

  • Code de hachage de plan de requête de la dernière instruction exécutée par chaque session avant l’interblocage. Le code de hachage de plan de requête permet de récupérer des informations supplémentaires sur la requête à partir du Magasin des requêtes.

Dans notre exemple d’interblocage :

  • Nous pouvons voir que les deux sessions ont été exécutées à l’aide du client SSMS sous la connexion chrisqpublic.

  • Le code de hachage de plan de requête de la dernière instruction exécutée avant l’interblocage par la victime de l’interblocage est 0x02b0f58d7730f798. Nous pouvons voir le texte de cette instruction dans la mémoire tampon d’entrée.

  • Le code de hachage de plan de requête de la dernière instruction exécutée par l’autre session dans notre interblocage est également 0x02b0f58d7730f798. Nous pouvons voir le texte de cette instruction dans la mémoire tampon d’entrée. Dans ce cas, les deux requêtes ont le même code de hachage de plan de requête, car les requêtes sont identiques, à l’exception d’une valeur littérale utilisée comme prédicat d’égalité.

Nous utilisons ces valeurs plus loin dans cet article pour trouver des informations supplémentaires dans le Magasin des requêtes.

Limitations de la mémoire tampon d’entrée dans la liste des processus d’interblocage

Certaines limitations doivent être prises en compte concernant les informations relatives à la mémoire tampon d’entrée dans la liste des processus d’interblocage.

Le texte de requête peut être tronqué dans la mémoire tampon d’entrée. La mémoire tampon d’entrée est limitée aux 4 000 premiers caractères de l’instruction exécutée.

En outre, certaines instructions impliquées dans l’interblocage peuvent ne pas être incluses dans le graphique d’interblocage. Dans notre exemple, la session A a exécuté deux instructions de mise à jour au sein d’une même transaction. Seule la deuxième instruction de mise à jour, celle qui a provoqué l’interblocage, est incluse dans le graphique d’interblocage. La première instruction de mise à jour exécutée par session A a joué un rôle dans l’interblocage en bloquant session B. La mémoire tampon d’entrée, query_hashet les informations associées pour la première instruction exécutée par session A n’est pas incluse dans le graphique d’interblocage.

Pour identifier l'ensemble de l'exécution de Transact-SQL dans une transaction à plusieurs instructions impliquée dans un interblocage, vous devez soit rechercher les informations pertinentes dans la procédure stockée ou le code d'application qui a exécuté la requête, soit exécuter une trace à l'aide des événements étendus pour capturer des instructions complètes exécutées par des sessions impliquées dans un interblocage au moment où elles se produisent. Si une instruction impliquée dans l’interblocage est tronquée et que seul un code Transact-SQL partiel apparaît dans la mémoire tampon d’entrée, vous trouverez le code Transact-SQL de l’instruction dans le Magasin des requêtes avec le plan d’exécution.

Liste des ressources d’interblocage

La liste des ressources d’interblocage indique les ressources de verrouillage détenues et attendues par les processus concernés par l’interblocage.

Les ressources sont représentées par des rectangles dans la représentation visuelle de l’interblocage :

Capture d’écran d’un graphique d’interblocage affiché visuellement dans SSMS. Des rectangles indiquent les ressources impliquées dans l’interblocage.

Remarque

Les noms de base de données sont représentés sous forme de GUID (uniqueidentifier) dans des graphiques de blocage pour les bases de données dans Azure SQL Database. Il s’agit du physical_database_name de la base de données répertoriée dans les vues de gestion dynamique sys.databases et sys.dm_user_db_resource_governance.

Dans cet exemple d’interblocage :

  • La victime de l’interblocage, que nous avons appelée Session A :

    • possède un verrou exclusif (X) sur une clé de l’index PK_Product_ProductID de la table SalesLT.Product ;

    • demande un verrou de mise à jour (U) sur une clé de l’index PK_ProductDescription_ProductDescriptionID de la table SalesLT.ProductDescription.

  • L’autre processus, que nous avons appelé Session B :

    • possède un verrou de mise à jour (U) sur une clé de l’index PK_ProductDescription_ProductDescriptionID de la table SalesLT.ProductDescription ;

    • demande un verrou partagé (S) sur une clé de l’index PK_ProductDescription_ProductDescriptionID de la table SalesLT.ProductDescription.

Les mêmes informations sont visibles dans le code XML du graphique d’interblocage sur le nœud resource-list.

Rechercher des plans d’exécution de requêtes dans le Magasin des requêtes

Il est souvent utile d’examiner les plans d’exécution des requêtes pour trouver les instructions impliquées dans l’interblocage. Ces plans d’exécution sont souvent accessibles dans le Magasin des requêtes à l’aide du code de hachage de plan de requête de la vue XML de la liste des processus du graphique d’interblocage.

Cette requête Transact-SQL recherche les plans de requête correspondant au code de hachage de plan de requête que nous avons trouvé pour notre exemple d’interblocage. Dans Azure SQL Database, connectez-vous à la base de données utilisateur pour exécuter la requête.

DECLARE @query_plan_hash AS BINARY (8) = 0x02b0f58d7730f798;

SELECT qrsi.end_time AS interval_end_time,
       qs.query_id,
       qp.plan_id,
       qt.query_sql_text,
       TRY_CAST (qp.query_plan AS XML) AS query_plan,
       qrs.count_executions
FROM sys.query_store_query AS qs
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_runtime_stats_interval AS qrsi
         ON qrs.runtime_stats_interval_id = qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

Vous ne pourrez peut-être pas obtenir un plan d'exécution de requête à partir de Query Store, en fonction de vos paramètres de CLEANUP_POLICY ou de QUERY_CAPTURE_MODE. Dans ce cas, pour obtenir les informations nécessaires, il suffit souvent d’afficher le plan d’exécution estimé de la requête.

Rechercher les modèles qui accentuent les blocages

Lors de l’examen des plans d’exécution de requêtes impliqués dans les interblocages, recherchez les modèles susceptibles de contribuer aux blocages et aux interblocages.

  • Analyses de table ou d’index. Lorsque des requêtes modifiant des données sont exécutées en mode RCSI, le choix des lignes à mettre à jour se fait au moyen d’une analyse des blocages. Au cours de celle-ci, un verrou de mise à jour (U) est acquis sur la ligne de données au fur et à mesure que les valeurs des données sont lues. Si la ligne de données ne répond pas aux critères de mise à jour, le verrou de mise à jour est libéré et la ligne suivante est verrouillée et analysée.

    Le paramétrage des index pour faciliter la recherche des lignes par les requêtes de modification réduit le nombre de verrous de mise à jour émis. Cela réduit les risques de blocage et d’interblocage.

  • Vues indexées faisant référence à plusieurs tables. Lorsque vous modifiez une table qui est référencée dans une vue indexée, le moteur de base de données doit également gérer la vue indexée. Cela oblige à supprimer davantage de verrous et peut entraîner une augmentation des blocages et des interblocages. Les vues indexées peuvent également entraîner l’exécution interne d’opérations de mise à jour sous le niveau d’isolation de lecture validée.

  • Modifications apportées aux colonnes référencées dans les contraintes FOREIGN KEY. Lorsque vous modifiez des colonnes d’une table référencée dans une contrainte de FOREIGN KEY, le moteur de base de données doit rechercher des lignes associées dans la table de référencement. Les versions de lignes ne peuvent pas être utilisées pour ces opérations de lecture. Lorsque les mises à jour ou les suppressions en cascade sont activées, le niveau d’isolation peut être relevé au niveau Sérialisable pour la durée de l’instruction en guise de protection contre les insertions fantômes.

  • Indicateurs de verrou. Recherchez les indicateurs de table qui spécifient les niveaux d’isolement nécessitant davantage de verrous. Ces indicateurs incluent HOLDLOCK (qui équivaut à Sérialisable), SERIALIZABLE, READCOMMITTEDLOCK (qui désactive le mode RCSI) et REPEATABLEREAD. En outre, les indicateurs tels que PAGLOCK, TABLOCK, UPDLOCK et XLOCK peuvent augmenter les risques de blocage et d’interblocage.

    Si ces indicateurs sont présents, recherchez pourquoi ils ont été implémentés. Ces indices peuvent éviter les situations de course et garantir la validité des données. Il est possible de laisser ces indices en place et d’empêcher les blocages futurs à l’aide d’une autre méthode dans la section Empêcher un blocage de se reproduire de cet article si nécessaire.

    Remarque

    Pour en savoir plus sur le comportement lié à la modification des données à l’aide du contrôle de version de ligne, consultez le Guide du verrouillage des transactions et du contrôle de version de ligne.

Lors de l’examen de l’intégralité du code d’une transaction, que ce soit dans un plan d’exécution ou dans le code de requête de l’application, recherchez d’autres modèles problématiques :

  • Interactions utilisateur dans les transactions. Les interactions utilisateur à l’intérieur d’une transaction multi-instructions explicite augmentent considérablement la durée des transactions. Il est donc plus probable que ces transactions se chevauchent et que des blocages et des interblocages surviennent.

    De même, le maintien d’une transaction ouverte et l’interrogation d’une base de données ou d’un système non lié au milieu de la transaction augmentent considérablement les risques de blocage et d’interblocage.

  • Transactions accédant à des objets dans des ordres différents. Les risques d’interblocage sont moindres lorsque des transactions multi-instructions explicites simultanées suivent les mêmes modèles et accèdent aux objets dans le même ordre.

Empêcher un interblocage de se reproduire

Il existe plusieurs techniques permettant d’empêcher les interblocages de se reproduire, telles que le réglage d’index, le forçage des plans avec le Magasin des requêtes et la modification des requêtes Transact-SQL.

  • Examiner l’index cluster de la table. La plupart des tables tirent parti des index cluster, mais il arrive souvent que les tables soient accidentellement implémentées sous forme de tas.

    Pour vérifier la présence d’un index cluster, vous pouvez utiliser la procédure stockée système sp_helpindex. Par exemple, nous pouvons voir un récapitulatif des index de la table SalesLT.Product en exécutant l’instruction suivante :

    EXECUTE sp_helpindex 'SalesLT.Product';
GO

    Passez en revue la colonne index_description. Une table ne peut avoir qu’un seul index cluster. Si un index cluster a été implémenté pour la table, le index_description contient le mot clustered.

    Si aucun index cluster n’est présent, la table est un tas. Dans ce cas, vérifiez si la table a été créée intentionnellement en tant que tas pour résoudre un problème de performances spécifique. Implémentez un index cluster en fonction des recommandations sur la conception d’index cluster.

    Dans certains cas, la création ou l'ajustement d'un index clusterisé peut réduire ou éliminer les blocages lors des interblocages. Dans d’autres cas, vous pouvez utiliser une technique supplémentaire telle que les autres de cette liste.

  • Créer ou modifier des index non cluster. Le paramétrage d’index non cluster peut aider vos requêtes de modification à trouver plus rapidement les données à mettre à jour, ce qui réduit le nombre de verrous de mise à jour nécessaires.

    Dans notre exemple d’interblocage, le plan d’exécution de requête trouvé dans le Magasin des requêtes contient une analyse d’index cluster sur l’index PK_Product_ProductID. Le graphique d’interblocage indique qu’une attente de verrou partagé (S) sur cet index est un composant de l’interblocage.

    Capture d’écran d’un plan d’exécution de requête. Une analyse d’index cluster est effectuée sur l’index PK_Product_ProductID sur la table Produit.

    Cette analyse d’index est effectuée parce que notre requête de mise à jour doit modifier une vue indexée nommée vProductAndDescription. Comme mentionné dans la section intitulée "Rechercher des modèles qui augmentent le blocage" de cet article, les vues indexées faisant référence à plusieurs tables peuvent augmenter le blocage et la probabilité d’interblocages.

    Si nous créons l’index non cluster suivant dans la base de données AdventureWorksLT qui « couvre » les colonnes de SalesLT.Product référencées par la vue indexée, cela facilite la recherche des lignes pour la requête :

    CREATE INDEX IX_Product_ProductID_Name_ProductModelID
    ON SalesLT.Product(ProductID, Name, ProductModelID);
GO

    Après la création de cet index, l’interblocage ne se reproduit plus.

    Lorsque des interblocages impliquent des modifications apportées aux colonnes référencées dans les contraintes de clé étrangère, assurez-vous que les index de la table de référencement du FOREIGN KEY prennent en charge efficacement la recherche de lignes associées.

    Bien que les index puissent considérablement améliorer les performances des requêtes dans certains cas, ils entraînent également une surcharge de traitement et des coûts de gestion. Passez en revue les Instructions générales sur la création d’index pour évaluer les avantages des index avant d’en créer, notamment si vous souhaitez créer des index larges et des index sur des tables volumineuses.

  • Évaluer la valeur des vues indexées. Une autre option pour empêcher notre exemple d’interblocage de se reproduire consiste à supprimer la vue indexée SalesLT.vProductAndDescription. Si cette vue indexée n’est pas utilisée, cela réduit la surcharge liée à la maintenance de la vue indexée au fil du temps.

  • Utiliser un isolement d’instantané. Dans certains cas, la définition du niveau d’isolation de la transaction sur le niveau de capture instantanée pour une ou plusieurs des transactions impliquées dans un interblocage peut empêcher les blocages et les interblocages de se reproduire.

    Cette technique a plus de chances de réussir lorsqu’elle est utilisée sur des instructions SELECT et que le mode RCSI est désactivé dans une base de données. Lorsque le mode RCSI est désactivé, les requêtes SELECT qui utilisent le niveau d’isolation de lecture validée ont besoin des verrous partagés (S). L’utilisation de l’isolement des instantanés sur ces transactions supprime la nécessité de verrous partagés, ce qui peut empêcher les blocages et interblocages.

    Dans les bases de données où l’isolation de capture instantanée de lecture validée est activée, les requêtes SELECT ne nécessitent pas de verrous partagés (S), de sorte que les interblocages sont plus susceptibles de se produire entre les transactions qui modifient les données. En cas d’interblocages entre plusieurs transactions modifiant des données, l’isolation de capture instantanée peut entraîner un conflit de mise à jour au lieu d’un interblocage. Cela nécessite de la même manière que l’une des transactions réessaie son opération.

  • Forcer un plan auprès du Magasin des requêtes. Vous pouvez constater qu’une des requêtes dans le blocage comporte plusieurs plans d’exécution et que le blocage se produit uniquement lorsqu’un plan spécifique est utilisé. Vous pouvez alors empêcher l’interblocage en forçant un plan dans le Magasin des requêtes.

  • Modifier le code Transact-SQL. Vous devrez peut-être modifier Transact-SQL pour empêcher l’interblocage de se reproduire. La modification du code Transact-SQL doit être effectuée avec soin et les changements doivent être rigoureusement testés pour vérifier que les données sont correctes lorsque les modifications sont exécutées simultanément. Lors de la réécriture du code Transact-SQL, pensez à :

    • ordonner les instructions dans les transactions afin qu’elles accèdent aux objets dans le même ordre ;

    • diviser les transactions en transactions plus petites lorsque cela est possible ;

    • utiliser des indicateurs de requête, si nécessaire, pour optimiser les performances. Vous pouvez appliquer des indicateurs sans modifier le code d’application à l’aide de Magasin des requêtes.

Découvrez d’autres façons de réduire les interblocages dans le Guide des interblocages.

Remarque

Dans certains cas, vous pouvez ajuster la priorité d’interblocage d’une ou plusieurs sessions impliquées dans un interblocage s’il est important que l’une des sessions se termine correctement sans réessayer, ou lorsque l’une des requêtes impliquées dans le blocage n’est pas critique et doit toujours être choisie comme victime. Bien que cela n’empêche pas l’interblocage de se reproduire, cela peut réduire l’effet des blocages futurs.

Supprimer une session XEvents

Vous pouvez laisser une session XEvents collecter des informations d’interblocage s’exécutant sur des bases de données critiques pendant de longues périodes. Si vous utilisez un fichier cible pour les événements, cela pourrait entraîner des fichiers volumineux si plusieurs interblocages se produisent. Vous pouvez supprimer des fichiers blob du Stockage Azure pour une trace active, à l’exception du fichier en cours d’écriture.

Lorsque vous souhaitez supprimer une session XEvents, le code Transact-SQL de suppression est le même, quel que soit le type de cible sélectionné.

Pour supprimer une session XEvents, exécutez le code Transact-SQL suivant. Avant d’exécuter le code, remplacez le nom de la session par la valeur appropriée.

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Utiliser l’Explorateur Stockage Azure

Explorateur Stockage Azure est une application autonome qui simplifie l’utilisation des cibles de fichier d’événements stockées dans des objets blob du Stockage Azure. Vous pouvez utiliser l’Explorateur Stockage pour :

Télécharger l’Explorateur Stockage Azure.

Contenu connexe