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

S’applique à : Base de données Azure SQL Base 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 RCSI (read committed snapshot) 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. Toutefois, des blocages et interblocages peuvent toujours se produire dans les bases de données dans Azure SQL Database, car :

• Les requêtes qui modifient les données peuvent se bloquer les unes les autres.
• Les requêtes peuvent s’exécuter sous des niveaux d’isolement qui augmentent le blocage. Les niveaux d’isolement peuvent être spécifiés par le biais de méthodes de bibliothèque de client, d’indicateurs de requête ou d’instructions SET en Transact-SQL.
• RCSI peut être désactivé, ce qui entraîne l’utilisation de verrous partagés (S) pour protéger l’exécution des instructions SELECT sous le niveau d’isolement read committed. Cela peut augmenter les blocages et 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.

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 processus de surveillance des interblocages détecte une dépendance cyclique, il choisit l’une des tâches comme victime et met fin à sa transaction avec l’erreur 1205, « La transaction (ID de processus N) a été bloquée par un autre processus sur les ressources de verrouillage et a été choisie comme victime de l’interblocage. Réexécutez la transaction ». Ce type de déblocage permet à l’autre tâche concernée par l’interblocage de reprendre ses transactions.

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 délai court et aléatoire avant toute nouvelle tentative afin que le même interblocage ne se reproduise pas. 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). Le mode RCSI modifie le comportement du niveau d’isolement Read Committed pour qu’il utilise le contrôle de version de ligne qui offre une 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 qui lisent les données ne bloquent pas celles qui les modifient.
• Les instructions qui modifient les données ne bloquent pas celles qui les lisent.

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. Cette opération peut être effectuée uniquement lorsque l’isolement d’instantané est activé 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’isolement d’instantané est activé, la colonne snapshot_isolation_state_desc retourne la valeur ON.

Si le mode RCSI a été désactivé pour une base de données d’Azure SQL Database, tâchez d’en déterminer la raison avant de le réactiver. Le code d’application a peut-être été écrit pour que les requêtes qui lisent les données soient bloquées par celles qui les écrivent, ce qui se traduit par des résultats incorrects dus à des conditions de concurrence lorsque le mode RCSI est activé.

Interprétation des é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 est émise après la résolution d’un interblocage individuel. Aucune intervention de l’utilisateur n’est nécessaire pour cet interblocage. Les applications doivent être écrites de manière à inclure une logique de nouvelle tentative afin qu’elles continuent automatiquement après avoir reçu l’erreur 1205, « La transaction (ID de processus N) a été bloquée par un autre processus sur les ressources de verrouillage et a été choisie comme victime de l’interblocage. Réexécutez la transaction. »

Des alertes doivent malgré tout être configurées, car les interblocages peuvent se reproduire. Les alertes relatives aux interblocages vous permettent de déterminer si des interblocages répétés surviennent dans votre base de données, auquel cas vous pouvez prendre des mesures pour empêcher ces interblocages de se reproduire. 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 utiliserons l’exemple de base de données AdventureWorksLT pour configurer des alertes sur les interblocages, provoquer un exemple d’interblocage, analyser le graphique d’interblocage correspondant à l’exemple, et tester des modifications afin d’empêcher l’interblocage de se reproduire.

Nous utiliserons également le client SQL Server Management Studio (SSMS), car il contient des fonctionnalités permettant d’afficher les graphiques d’interblocage en mode visuel interactif. Vous pouvez utiliser d’autres clients, comme Azure Data Studio pour suivre les exemples, mais vous risquez alors de ne pouvoir afficher les graphiques d’interblocage qu’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 :

Sélectionnez le type de cible à utiliser :

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

IF 0 = (SELECT COUNT(*)
    FROM sys.symmetric_keys
    WHERE symmetric_key_id = 101 and name=N'##MS_DatabaseMasterKey##')
BEGIN
    PRINT N'Creating master key';
    CREATE MASTER KEY;
END
ELSE 
BEGIN
    PRINT N'Master key already exists, no action taken';
END
GO

CREATE DATABASE SCOPED CREDENTIAL
    [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH
        IDENTITY = 'SHARED ACCESS SIGNATURE', 
        SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=pUNbbsmDiMzXr1vuNGZh84zyOMBFaBjgWv53IhOzYWQ%3D'
    ;
GO

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
    (SET filename =
    'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
    )
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

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

Cause d’un interblocage dans AdventureWorksLT

Pour provoquer un interblocage, vous devez connecter deux sessions à la base de données AdventureWorksLT. Nous appellerons 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 TRAN

    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
    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';

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 sera 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
        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';

La deuxième instruction de mise à jour de la session A sera 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. Un message d’erreur apparaît dans la session A avec un texte semblable au suivant :

Msg 1205, Niveau 13, État 51, Ligne 7 La transaction (ID de processus 91) a été bloquée par un autre processus sur les ressources de verrouillage et a été choisie comme victime de l’interblocage. Relancez la transaction.

La session B se termine 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 avez configuré une session XEvents pour collecter les interblocages et qu’un interblocage s’est produit après le démarrage de la session, vous pouvez afficher une représentation graphique interactive du graphique d’interblocage ainsi que le code XML de celui-ci.

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 :

DECLARE @tracename sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    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

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

DECLARE @tracename sysname = N'deadlocks_eventfile',
    @filename nvarchar(2000);

WITH eft as (SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    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'event_file'
)
SELECT @filename = ft.evtdata.value('(@name)[1]','nvarchar(2000)') 
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') as ft(evtdata);

WITH xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/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

declare @filename nvarchar(2000) = N'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/yourfilename.xel';

with xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/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. Selon vos préférences, vous pouvez également analyser les interblocages dans un format texte.

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.

   

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.

   

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.

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 du processus serveur, également appelé ID de session ou 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, la session_id 89 a utilisé une part inférieure du journal des transactions et a été sélectionnée comme victime de l’interblocage.

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.

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 l’ID de 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 utiliserons 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 la 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 la session A a joué un rôle dans l’interblocage en bloquant la session B. La mémoire tampon d’entrée, query_hash et les informations associées de la première instruction exécutée par la session A ne sont pas incluses dans le graphique d’interblocage.

Pour identifier l’intégralité du code Transact-SQL exécuté dans le cadre d’une transaction multi-instructions impliquée dans un interblocage, vous devez soit trouver 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 d’événements étendus pour capturer les instructions complètes exécutées par les sessions impliquées dans l’interblocage au moment où il se produit. Si une instruction impliquée dans l’interblocage a été 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 :

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 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
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval 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

En fonction des paramètres CLEANUP_POLICY ou QUERY_CAPTURE_MODE du Magasin des requêtes, vous n’obtiendrez peut-être pas de plan d’exécution de la requête à partir de votre Magasin des requêtes. 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 déplacé et la ligne suivante est 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’isolement Read Committed.

• Modifications apportées aux colonnes référencées dans les contraintes FOREIGN KEY. Lorsque vous modifiez les colonnes d’une table référencée dans une contrainte FOREIGN KEY, le moteur de base de données doit rechercher les lignes associées dans la table de référence. Les versions de ligne ne peuvent pas être utilisées pour ces lectures. Lorsque les mises à jour ou les suppressions en cascade sont activées, le niveau d’isolement 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 indicateurs peuvent empêcher les conditions de concurrence et garantir la validité des données. Si nécessaire, vous pouvez tenter de conserver ces indicateurs et de prévenir de futurs interblocages en utilisant une des autres méthodes décrites dans la section Empêcher un interblocage de se reproduire de cet article.

  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

De nombreuses techniques permettent d’empêcher les interblocages de se reproduire, comme le paramétrage d’index, le fait de forcer des plans auprès du 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 :

  exec sp_helpindex 'SalesLT.Product';
  GO
  

  Examiner 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, index_description contient le mot « cluster ».

  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 le paramétrage d’un index cluster peut réduire ou éliminer les blocages et interblocages. Dans d’autres cas, vous devrez peut-être employer une autre technique, comme celles répertoriées ici.

• 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.

  

  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 Rechercher les modèles qui accentuent les blocages de cet article, les vues indexées faisant référence à plusieurs tables peuvent accentuer les blocages 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 les interblocages impliquent des modifications de colonnes référencées dans des contraintes FOREIGN KEY, assurez-vous que les index de la table de référence de la contrainte FOREIGN KEY permettent de trouver facilement les 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, les frais de maintenance de celle-ci baisseront 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 d’instantané pour une ou plusieurs des transactions impliquées dans un interblocage peut empêcher les blocages et 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’isolement Read Committed 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ù le mode RCSI a été activé, les requêtes SELECT n’ont pas besoin de verrous partagés (S). Par conséquent, les risques d’interblocage sont plus grands entre les transactions qui modifient les données. En cas d’interblocages entre plusieurs transactions modifiant des données, l’isolement des instantanés 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. Il se peut que l’une des requêtes associées à l’interblocage comporte plusieurs plans d’exécution et que l’interblocage ne se produise que 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 le code Transact-SQL pour éviter que l’interblocage ne se reproduise. 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.

Supprimer une session XEvents

Vous souhaiterez peut-être laisser ouverte pendant de longues périodes une session XEvents collectant des informations concernant les interblocages sur des bases de données critiques. Sachez que si vous utilisez une cible de fichier d’événements, l’enchaînement de plusieurs interblocages peut générer des fichiers volumineux. Vous pouvez supprimer des fichiers blob de 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 :

• Créer un conteneur d’objets blob pour les données de session XEvent
• Obtenir la signature d’accès partagé (SAS) d’un conteneur d’objets blob
  • Comme mentionné dans Collecter des graphiques d’interblocage dans Azure SQL Database avec des événements étendus, des autorisations de lecture, d’écriture et d’énumération sont requises.
  • Supprimez tout caractère ? de début de Query string pour utiliser la valeur comme secret lors de la création d’informations d’identification de niveau base de données.
• Afficher et télécharger des fichiers d’événements étendus à partir d’un conteneur d’objets blob.

Téléchargez l’Explorateur Stockage Azure..

Étapes suivantes

Découvrez-en plus sur les performances dans Azure SQL Database :

• Comprendre et résoudre les problèmes de blocage d’Azure SQL Database
• Guide du verrouillage des transactions et du contrôle de version de ligne
• Guide des interblocages
• SET TRANSACTION ISOLATION LEVEL
• Azure SQL Database : amélioration du réglage des performances grâce au réglage automatique
• Offrez des performances cohérentes avec Azure SQL
• Logique de nouvelle tentative pour les erreurs temporaires.