ApsaraDB RDS:How to handle deadlocks in ApsaraDB RDS for SQL Server

Description du problème

Lorsqu'une application lit ou écrit fréquemment dans la même table ou sur la même ressource, des interblocages peuvent se produire. Dans ce cas, SQL Server met fin à l'une des transactions et renvoie un message d'erreur au client, comme illustré ci-dessous :

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

Solution

1. Connectez-vous à l'instance depuis un client. Pour plus d'informations, consultez la rubrique Connexion à une instance ApsaraDB RDS for SQL Server.

2. Surveillez les vues pertinentes.

   1. Exécutez l'instruction SQL suivante pour surveiller périodiquement la vue SYS.SYSPROCESSES.

      WHILE 1 = 1
      BEGIN
      SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
      WAITFOR DELAY '[$Time]';
      END;

      Remarque

      Cet exemple utilise le paramètre [$Time]. Vous pouvez personnaliser l'intervalle de sondage, par exemple en définissant la valeur 00:00:01.

      Dans les résultats obtenus après plusieurs itérations, le SPID 53 attend un verrou de type LCK_M_X, tandis que le SPID 56 attend un verrou de type LCK_M_S. La valeur blocked du SPID 53 est 56, et celle du SPID 56 est 53, ce qui indique qu'ils se bloquent mutuellement. Le temps d'attente (waittime) des deux sessions augmente à chaque boucle.

      Remarque

      Dans les résultats de surveillance, la colonne blocked affiche l'ID de session de la session bloquante, et la colonne waitresource indique la ressource attendue par la session bloquée. Ces résultats confirment que les SPID 53 et 56 se bloquent mutuellement, formant ainsi un interblocage.

   2. Exécutez l'instruction SQL suivante pour surveiller périodiquement des vues telles que sys.dm_tran_locks et sys.dm_os_waiting_tasks.

      while 1=1
          Begin
          SELECT
          db.name DBName,
          tl.request_session_id,
          wt.blocking_session_id,
          OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
          tl.resource_type,
          h1.TEXT AS RequestingText,
          h2.TEXT AS BlockingText,
          tl.request_mode
          FROM sys.dm_tran_locks AS tl
          INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
          INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
          INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
          INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
          INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
          CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
          CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
          waitfor delay '[$Time]'
          End

      Deux sessions se bloquent mutuellement, créant un interblocage : le request_session_id 56 est bloqué par le blocking_session_id 62 sur l'objet Tbl2, avec un request_mode de type X. Simultanément, la session 62 est bloquée par la session 56 sur l'objet Tbl1, avec un request_mode de type X. Le champ RequestingText des deux sessions affiche des instructions INSERT, confirmant une relation de blocage cyclique aboutissant à un interblocage.

      Le tableau suivant décrit les colonnes renvoyées.

      Paramètre	Description
      DBName	Base de données à laquelle la session demanderesse accède.
      request_session_id	ID de la session bloquée.
      blocking_session_id	ID de la session bloquante.
      BlockedObjectName	Objet auquel la session bloquée accède.
      resource_type	Type de ressource en attente.
      RequestingText	Instruction exécutée par la session bloquée.
      BlockingText	Instruction exécutée par la session bloquante.
      request_mode	Mode de verrou demandé par la session bloquée.

   3. Si votre instance exécute ApsaraDB RDS for SQL Server 2012, vous pouvez également utiliser SQL Server Profiler pour surveiller et capturer un graphique d'interblocage.

      Dans la boîte de dialogue Trace Properties, sous l'onglet Events Selection, développez la catégorie Locks et sélectionnez les événements Deadlock graph et Lock:Deadlock.

      Graphique d'interblocage capturé :

      Les résultats de la trace SQL Server Profiler montrent deux événements Lock:Deadlock Chain : le SPID 52 détient un verrou exclusif (5-X) sur la base de données test_shrink (transaction 1374753), tandis que le SPID 55 détient un verrou partagé (3-S) (transaction 1374762). Cela déclenche ensuite un événement Lock:Deadlock. Le graphique d'interblocage indique que les SPID 52 et 55 verrouillent des ressources de verrouillage de clés sur l'index i de test_shrink.dbo.Th12 et test_shrink.dbo.Th11, respectivement. Les deux processus demandent les verrous détenus par l'autre (le mode de demande est X/S), créant une dépendance circulaire qui entraîne un interblocage.

Recommandations

Suivez les recommandations ci-dessous pour résoudre et prévenir les interblocages.

• Mettez fin à la session bloquante pour résoudre rapidement l'interblocage.

• Vérifiez s'il existe des transactions de longue durée et validez-les rapidement afin de libérer les verrous.

• Si l'interblocage est causé par un verrou partagé et que votre application peut tolérer une lecture sale, utilisez l'indicateur de requête WITH (NOLOCK). Par exemple, ajoutez SELECT * FROM table WITH (NOLOCK); à votre requête. Cela permet à la requête de s'exécuter sans demander de verrou, ce qui aide à prévenir les interblocages.

• Examinez la logique de l'application pour garantir que les ressources sont accessibles dans un ordre cohérent.

Opérations connexes

Vous pouvez consulter les détails des interblocages de votre instance ApsaraDB RDS for SQL Server dans la console ApsaraDB RDS. Pour plus d'informations, consultez la rubrique Affichage des statistiques d'interblocage.