Escalonamento de bloqueios: encontre a causa

Um trabalho de manutenção modifica alguns milhares de linhas e outras solicitações começam a esperar. O escalonamento de bloqueios é uma explicação possível, mas enxergar um bloqueio de tabela não comprova essa hipótese. Primeiro identifique o bloqueio incompatível que impede o avanço e sua transação proprietária.

Interpretar os sinais

Um bloqueio de intenção exclusiva sobre um objeto é normal quando suas linhas são modificadas. Ele informa a existência de bloqueios inferiores; não exclui todos os leitores automaticamente. Um bloqueio compartilhado ou exclusivo de tabela tem outras regras de compatibilidade. Confundir IX com X pode direcionar a investigação para uma correção inadequada.

A consulta mostra bloqueios de objeto concedidos e pendentes no banco atual. O escopo é limitado: bloqueios de chave, página, esquema ou aplicação ainda podem explicar a espera. As permissões de diagnóstico dependem da versão e devem ser concedidas pelo papel de monitoramento estabelecido.

SELECT request_session_id, resource_type, request_mode,
       request_status, resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
  AND resource_type = 'OBJECT';

Associe as sessões à cadeia de bloqueio e às instruções em execução. Capture o evento estendido lock_escalation quando houver suspeita. Um bloqueio X observado agora não informa se surgiu por escalonamento, hint explícito ou outra operação. O evento fornece a informação histórica que uma fotografia não possui.

Não considere uma quantidade fixa de linhas uma fronteira garantida. Linhas e bloqueios não são equivalentes. Índices, caminhos de acesso, isolamento e pressão de memória influenciam o consumo. ROWLOCK não impede necessariamente o escalonamento. Desabilitar esse mecanismo pode trocar contenção por esgotamento de recursos.

Estabelecer transações realmente separadas

O objetivo é reduzir recursos mantidos simultaneamente. Uma instrução pequena dentro de uma transação que envolve todo o loop continua acumulando bloqueios. Commits independentes criam os pontos de liberação. Verifique se o cliente, o chamador da procedure ou o agendador introduz uma transação externa.

O exercício cria dados temporários e os modifica por instruções confirmadas independentemente. Ele exige ausência de transação aberta e IMPLICIT_TRANSACTIONS desabilitado. O tamanho escolhido demonstra o mecanismo; não é uma recomendação universal para produção.

IF @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2
    THROW 50000, 'Use autocommit with no open transaction.', 1;
CREATE TABLE #Work (Id int PRIMARY KEY, Done bit NOT NULL);
INSERT #Work
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
DECLARE @changed int = 1;
WHILE @changed > 0
BEGIN
    ;WITH batch AS
    (SELECT TOP (500) Id, Done FROM #Work
     WHERE Done = 0 ORDER BY Id)
    UPDATE batch SET Done = 1;
    SET @changed = @@ROWCOUNT;
END;
SELECT COUNT(*) AS Remaining FROM #Work WHERE Done = 0;

A seleção ordenada pela chave torna o progresso compreensível. Em uma tabela real, o predicado precisa de um acesso eficiente. Um lote pode alterar 500 linhas e ler milhões para encontrá-las. Analise linhas lidas, leituras lógicas, esperas e duração da transação, além de @@ROWCOUNT.

Validar concorrência e recuperação

Execute o trabalho junto com leitores e escritores representativos. Compare as solicitações mais lentas, os eventos de escalonamento e a geração de log antes e depois. Uma manutenção um pouco mais demorada pode ser preferível quando mantém as solicitações dentro do prazo. Por outro lado, milhares de commits minúsculos também introduzem custo.

Commits independentes alteram o resultado de uma interrupção. O cancelamento deixa uma operação parcialmente concluída. O critério de seleção precisa permitir retomada segura, e qualquer marcador de progresso deve ser confirmado com as alterações correspondentes. Se o negócio exige atomicidade do conjunto inteiro, o uso de lotes modifica esse contrato.

Examine ainda triggers, cascatas, replicação e transporte de log para grupos de disponibilidade. Esses efeitos podem multiplicar o trabalho de uma pequena atualização. Inclua um cancelamento proposital no teste e confirme que a retomada não pula linhas nem repete efeitos externos já concluídos. Uma correção convincente identifica o bloqueador original, reduz o consumo relevante e preserva a semântica transacional exigida. Apenas fazer desaparecer um evento de escalonamento não comprova que o comportamento geral melhorou.

Referências técnicas: Microsoft Learn: Lock escalation · Microsoft Learn: Lock diagnostics.