ApsaraDB RDS:Como lidar com deadlocks no ApsaraDB RDS for SQL Server

Descrição do problema

Quando um aplicativo lê ou grava frequentemente na mesma tabela ou recurso, podem ocorrer deadlocks. Quando isso ocorre, o SQL Server encerra uma das transações e retorna uma mensagem de erro ao cliente, como a seguinte:

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.

Solução

1. Conecte-se à instância a partir de um cliente. Para mais informações, consulte Conectar a uma instância do ApsaraDB RDS for SQL Server.

2. Monitore as visualizações relevantes.

   1. Execute a seguinte instrução SQL para monitorar periodicamente SYS.SYSPROCESSES.

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

      Nota

      Este exemplo usa [$Time]. Você pode personalizar o intervalo de sondagem, por exemplo, 00:00:01.

      Nos resultados de múltiplas iterações, o SPID 53 está aguardando um bloqueio do tipo LCK_M_X, e o SPID 56 está aguardando um bloqueio do tipo LCK_M_S. O valor blocked do SPID 53 é 56, e o valor blocked do SPID 56 é 53, indicando que estão bloqueando um ao outro. O waittime de ambas as sessões aumenta a cada iteração.

      Nota

      Nos resultados do monitoramento, a coluna blocked exibe o ID da sessão de bloqueio, e a coluna waitresource exibe o recurso que a sessão bloqueada está aguardando. Os resultados indicam que o SPID 53 e o SPID 56 estão bloqueando um ao outro, formando um deadlock.

   2. Execute a seguinte instrução SQL para monitorar periodicamente visualizações como sys.dm_tran_locks e 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

      Duas sessões estão bloqueando uma à outra, criando um deadlock: request_session_id 56 está bloqueada por blocking_session_id 62 no objeto Tbl2, com request_mode X. Simultaneamente, a sessão 62 está bloqueada pela sessão 56 no objeto Tbl1, com request_mode X. O RequestingText de ambas as sessões exibe instruções INSERT, confirmando uma relação de bloqueio cíclico que resulta em um deadlock.

      A tabela a seguir descreve as colunas retornadas.

      Parâmetro	Descrição
      DBName	O banco de dados acessado pela sessão solicitante.
      request_session_id	O ID da sessão bloqueada.
      blocking_session_id	O ID da sessão de bloqueio.
      BlockedObjectName	O objeto acessado pela sessão bloqueada.
      resource_type	O tipo de recurso aguardado.
      RequestingText	A instrução executada pela sessão bloqueada.
      BlockingText	A instrução em execução pela sessão de bloqueio.
      request_mode	O modo de bloqueio solicitado pela sessão bloqueada.

   3. Se a sua instância executa o ApsaraDB RDS for SQL Server 2012, você também pode usar o SQL Server Profiler para monitorar e capturar um deadlock graph.

      Na caixa de diálogo Trace Properties, na aba Events Selection, expanda a categoria Locks e selecione os eventos Deadlock graph e Lock:Deadlock.

      Deadlock graph capturado:

      Os resultados do rastreamento do SQL Server Profiler exibem dois eventos Lock:Deadlock Chain: o SPID 52 mantém um bloqueio 5-X (exclusivo) no banco de dados test_shrink (transação 1374753), enquanto o SPID 55 mantém um bloqueio 3-S (compartilhado) (transação 1374762). Isso aciona um evento Lock:Deadlock. O deadlock graph mostra que o SPID 52 e o SPID 55 estão bloqueando recursos de bloqueio de chave no índice i de test_shrink.dbo.Th12 e test_shrink.dbo.Th11, respectivamente. Os dois processos solicitam os bloqueios mantidos um pelo outro (Request Mode X/S), criando uma dependência circular que resulta em um deadlock.

Recomendações

Use as recomendações a seguir para resolver e evitar deadlocks.

• Encerre a sessão de bloqueio para resolver o deadlock rapidamente.

• Verifique se há transações de longa duração e confirme-as prontamente para liberar os bloqueios.

• Se o deadlock for causado por um bloqueio compartilhado e seu aplicativo puder tolerar uma leitura suja (dirty read), use a dica de consulta WITH (NOLOCK). Por exemplo, adicione SELECT * FROM table WITH (NOLOCK); à sua consulta. Isso permite que a consulta seja executada sem solicitar um bloqueio, ajudando a evitar um deadlock.

• Revise a lógica do aplicativo para garantir que os recursos sejam acessados em uma ordem consistente.

Operações relacionadas

Você pode visualizar os detalhes do deadlock da sua instância do ApsaraDB RDS for SQL Server no console do ApsaraDB RDS. Para mais informações, consulte Exibir estatísticas de deadlock.