ApsaraDB RDS:Handle blocking issues in RDS for SQL Server

Descrição do problema

Ocorre um bloqueio em uma instância do ApsaraDB RDS for SQL Server.

Causa

O bloqueio acontece quando uma aplicação realiza leituras ou gravações frequentes em uma tabela ou recurso específico. Bloqueios severos tornam a execução de instruções mais lenta na sua aplicação.

Solução de problemas

Para solucionar problemas de bloqueio em uma instância do ApsaraDB RDS for SQL Server, siga estas etapas.

1. Monitore continuamente SYS.SYSPROCESSES para coletar informações sobre bloqueios. Execute o seguinte comando:

   WHILE 1 = 1
   BEGIN
       SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
       WAITFOR DELAY '00:00:01';
   END;

   Nota

   É possível personalizar o intervalo de consulta. Este exemplo utiliza 00:00:01.

   O resultado da consulta mostra que spid=56 está bloqueado por spid=53 (blocked=53), aguardando um bloqueio do tipo LCK_M_S, e o recurso de espera é KEY: 5:72057594038845440 (98ec012aa510). Ao longo de vários ciclos de consulta, o valor de waittime aumenta sequencialmente (9205, 10206, 11207, 12208 e 13209), indicando que o bloqueio não foi liberado.

   Nota

   A coluna blocked exibe o session_id da sessão que causa o bloqueio, enquanto a coluna waitresource identifica o recurso que a sessão bloqueada está aguardando. Para obter mais informações sobre os campos retornados, consulte a documentação oficial do sys.sysprocesses.

2. Monitore continuamente views como sys.dm_tran_locks e sys.dm_os_waiting_tasks para identificar toda a cadeia de bloqueio. Execute o seguinte comando:

   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 '00:00:01';
   END;

   Exemplo de resultado da consulta: No banco de dados jacky, a sessão com request_session_id igual a 62 está bloqueada pela sessão com blocking_session_id igual a 56. O objeto bloqueado é Tbl1, o tipo de recurso é KEY, a instrução solicitante (RequestingText) é (@1 int,@2 int)INSERT INTO [dbo].[Tbl2]([id],[c..., a instrução causadora do bloqueio (BlockingText) é BEGIN TRAN INSERT into dbo.Tbl1 (id, col) VALUE... e o modo de bloqueio solicitado é S.

   A tabela a seguir descreve os parâmetros retornados:

   Parâmetro	Descrição
   DBName	Nome do banco de dados.
   request_session_id	ID da sessão bloqueada (solicitante).
   blocking_session_id	ID da sessão que causa o bloqueio.
   BlockedObjectName	Objeto que a sessão bloqueada tenta acessar.
   resource_type	Tipo de recurso em espera.
   RequestingText	Instrução executada pela sessão bloqueada (solicitante).
   BlockingText	Instrução em execução pela sessão que causa o bloqueio.
   request_mode	Modo de bloqueio solicitado pela sessão solicitante.

Recomendações de ajuste

Siga estas recomendações para melhorar o desempenho.

1. Encerre a conexão da sessão causadora do bloqueio para resolver o problema imediatamente.

2. Identifique e confirme prontamente quaisquer transações longas não confirmadas.

3. Caso o bloqueio seja causado por um bloqueio compartilhado (S) e sua aplicação tolere leitura suja, utilize a dica de consulta WITH (NOLOCK). Por exemplo, escreva SELECT * FROM table WITH (NOLOCK); para evitar que a consulta solicite um bloqueio, contornando assim o problema.

4. Revise a lógica da sua aplicação para garantir que os recursos sejam acessados em uma ordem consistente.

Operações relacionadas

• Em situações de emergência, consulte Como resolver rapidamente problemas de bloqueio no ApsaraDB RDS for SQL Server.

• Utilize o console do ApsaraDB RDS para configurar métricas de desempenho e regras de alerta, permitindo detectar e responder rapidamente a problemas de desempenho do banco de dados. Para mais detalhes, veja Monitoramento e alertas.

• Para saber mais sobre estratégias de ajuste de desempenho de banco de dados, como otimização de índices, consultas e armazenamento, acesse Otimização e diagnóstico de desempenho.