Solução para o problema de solicitações bloqueadas em uma instância do ApsaraDB RDS for SQL Server - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

Importante

Este tópico descreve como resolver o bloqueio de solicitações em uma instância do ApsaraDB RDS for SQL Server em situações de emergência. Para eliminar a causa raiz do bloqueio, identifique e analise seus motivos e desenvolva uma solução com base nessa análise. Na maioria dos casos, a falta de índices ou transações extensas causam esse problema. Nessas situações, a execução da transação demora mais e os locks são mantidos por períodos prolongados, o que bloqueia um grande número de solicitações. Para obter um guia detalhado sobre a análise de causa raiz e a prevenção, consulte How do I resolve the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance?

Visão geral

O bloqueio ocorre quando uma sessão mantém um lock em um recurso e outra sessão solicita um lock conflitante no mesmo recurso. A segunda sessão aguarda até que a primeira libere o lock. Diferentemente de um deadlock, em que duas sessões formam uma dependência circular, detectada e resolvida automaticamente pelo SQL Server, o bloqueio é uma espera unidirecional. A sessão bloqueada aguarda até que a sessão detentora do lock termine a operação ou seja encerrada.

Bloqueios de curta duração são normais durante cargas de trabalho concorrentes. No entanto, quando as sessões mantêm locks por períodos prolongados, uma fila de solicitações em espera se acumula, o que degrada o desempenho da aplicação ou causa timeouts.

Descrição do problema

Ocorrem bloqueios de solicitações na instância do ApsaraDB RDS for SQL Server, e as consultas da aplicação apresentam timeout ou execução lenta.

Causa

As transações competem por recursos com locks. Quando uma sessão mantém um lock e outra precisa de um lock conflitante no mesmo recurso, o sistema bloqueia a segunda sessão até a liberação do lock.

Solução

Siga estas três etapas para identificar e encerrar a sessão causadora do bloqueio.

Etapa 1: Identificar as sessões de bloqueio e em espera

Execute a seguinte instrução para obter informações sobre os locks atuais e as solicitações bloqueadas. Essa consulta combina várias Dynamic Management Views (DMVs) do SQL Server para exibir as sessões bloqueadas, as sessões causadoras do bloqueio e as instruções SQL em execução em cada sessão.

SELECT dtl.request_session_id AS waitSID,
       der.blocking_session_id AS blockSID,
       dowt.resource_description,
       der.wait_type,
       dowt.wait_duration_ms,
       DB_NAME(dtl.resource_database_id) AS DB,
       dtl.resource_associated_entity_id AS waitingAssociatedEntity,
       dtl.resource_type AS waitResType,
       dtl.request_type AS waitReqType,
       dest.[text] AS waitSQL,
       dtl1.request_type AS blockReqType,
       dest1.[text] AS blockingSQL
FROM sys.dm_tran_locks dtl
JOIN sys.dm_os_waiting_tasks dowt ON dowt.resource_address=dtl.lock_owner_address
JOIN sys.dm_exec_requests der ON der.session_id=dtl.request_session_id CROSS apply sys.dm_exec_sql_text(der.sql_handle) dest
LEFT JOIN sys.dm_exec_requests der1 ON der.session_id=dowt.blocking_session_id OUTER apply sys.dm_exec_sql_text(der1.sql_handle) dest1
LEFT JOIN sys.dm_tran_locks dtl1 ON dtl1.request_session_id=der1.session_id

A tabela a seguir descreve as principais colunas de saída.

Coluna	Descrição
`waitSID`	ID da sessão em espera (bloqueada).
`blockSID`	ID da sessão bloqueadora que mantém o lock. Use este valor na Etapa 3.
`wait_type`	Tipo de espera enfrentado pela sessão bloqueada (por exemplo, `LCK_M_S` para espera de lock compartilhado, `LCK_M_X` para espera de lock exclusivo).
`wait_duration_ms`	Tempo de espera da sessão bloqueada, em milissegundos.
`DB`	Nome do banco de dados onde o bloqueio ocorre.
`waitingAssociatedEntity`	Identificador do recurso necessário à sessão em espera. Use este valor na Etapa 2.
`waitResType`	Tipo de recurso com lock (por exemplo, `KEY`, `PAGE`, `OBJECT`).
`waitSQL`	Instrução SQL que a sessão bloqueada tenta executar.
`blockingSQL`	Instrução SQL que a sessão bloqueadora está executando no momento. Retorna NULL se a sessão bloqueadora estiver ociosa.

Etapa 2: Identificar o recurso com lock

Execute a seguinte instrução para determinar a tabela e o índice envolvidos no bloqueio. Substitua [$Waiting_Associate_Entity] pelo valor de waitingAssociatedEntity obtido nos resultados da Etapa 1.

SELECT OBJECT_NAME(i.object_id) obj,
       i.name
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id=p.object_id
AND i.index_id=p.index_id
WHERE p.partition_id=[$Waiting_Associate_Entity]

[$Waiting_Associate_Entity] especifica o recurso aguardado pelas transações. Esse valor corresponde à coluna waitingAssociatedEntity retornada pela consulta da Etapa 1 e representa o hobt_id (ID do heap ou B-tree) da tabela ou índice onde ocorre a contenção de lock.

Essa consulta retorna o nome do objeto (tabela) e o nome do índice, o que ajuda a compreender quais dados as transações concorrentes tentam acessar.

Etapa 3: Encerrar a sessão bloqueadora

Após identificar a sessão bloqueadora na coluna blockSID da Etapa 1, execute a instrução KILL para encerrá-la:

KILL <session_id>

Substitua <session_id> pelo valor de blockSID. Por exemplo, se o blockSID for 58, execute KILL 58.

Importante

Antes de encerrar uma sessão, analise a saída blockingSQL da Etapa 1 para entender a operação em andamento na sessão bloqueadora. Encerrar uma sessão reverte a transação ativa, o que pode afetar a integridade dos dados ou o comportamento da aplicação. Use KILL apenas quando o bloqueio causar um impacto significativo e não for possível aguardar a conclusão natural da transação.

Próximos passos

Encerrar uma sessão bloqueadora é uma medida de emergência que resolve o problema imediato, mas não evita a recorrência. Para tratar a causa raiz, considere as seguintes ações:

Analise a consulta de bloqueio: Revise as saídas blockingSQL e waitSQL da Etapa 1 e procure por índices ausentes, table scans extensos ou transações de longa duração.
Otimize os índices: A falta de índices é uma das causas mais comuns de bloqueios prolongados. Certifique-se de que as colunas consultadas com frequência tenham índices adequados.
Mantenha as transações curtas: Projete as transações para serem concluídas o mais rápido possível. Evite manter transações abertas enquanto aguarda a entrada do usuário ou operações externas.
Revise os níveis de isolamento: Considere usar o Read Committed Snapshot Isolation (RCSI) para reduzir a contenção de locks. Esse nível permite que as operações de leitura acessem um snapshot dos dados sem serem bloqueadas por operações de escrita.

Para obter um guia abrangente sobre como diagnosticar e prevenir bloqueios, consulte How do I resolve the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance?