Identificar e resolver bloqueios e deadlocks
O bloqueio é normal em um banco de dados que usa bloqueio. Uma transação contém um bloqueio. Outra transação aguarda. É esperado um breve bloqueio que dura alguns milissegundos. Ele se torna um problema quando dura tempo suficiente para afetar os usuários. Deadlocks são a forma mais grave: duas transações bloqueiam permanentemente uma à outra e o mecanismo de banco de dados precisa terminar uma para quebrar o ciclo.
Bloqueio
O bloqueio ocorre quando uma sessão mantém um bloqueio em um recurso e outra sessão solicita um bloqueio conflitante no mesmo recurso. A sessão de solicitação aguarda até que a primeira sessão libere o bloqueio.
Os recursos podem ser linhas, páginas ou até mesmo tabelas inteiras. Os bloqueios podem ser compartilhados (para leitura) ou exclusivos (para escrita). Quando uma sessão solicita um bloqueio que entra em conflito com um bloqueio existente, ela fica bloqueada até que a primeira sessão confirme ou reverta sua transação e libere seus bloqueios.
Identificar cadeias de bloqueio
No Banco de Dados SQL do Azure, o Isolamento de Instantâneo de Leitura Confirmada (RCSI) está habilitado por padrão, de modo que as operações de leitura utilizam versionamento de linha em vez de bloqueios compartilhados. Essa configuração reduz significativamente o bloqueio entre leitores e escritores. No entanto, o bloqueio entre dois escritores, ou o bloqueio causado por transações explícitas com níveis de isolamento mais altos, ainda ocorre.
A sessão na parte superior de uma cadeia de bloqueio é chamada de bloqueador principal. Todas as outras sessões bloqueadas estão aguardando, direta ou indiretamente, que o bloqueador principal libere seus bloqueios. Para localizar o bloqueador de cabeçalho, consulte sys.dm_exec_requests e procure sessões em que blocking_session_id não seja zero:
SELECT
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
t.text AS query_text,
r.status,
r.command
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;
Para encontrar o bloqueador de cabeçalho nesses resultados, procure a ID da sessão que bloqueia outros, mas não está bloqueada em si. Por exemplo, suponha que a consulta retorne estas linhas:
| identificador_da_sessão | ID da sessão bloqueadora |
|---|---|
| 55 | 52 |
| 60 | 52 |
| 52 | 0 |
As sessões 55 e 60 são bloqueadas pela sessão 52, e a sessão 52 possui um blocking_session_id de 0, o que significa que nada a está bloqueando. A sessão 52 é o bloqueador principal. Depois de identificá-la, consulte sys.dm_exec_sessions e sys.dm_exec_requests filtrados para esse ID de sessão para ver o que está sendo executado e por que está mantendo bloqueios.
Tenha em mente que o RCSI elimina o bloqueio entre leitores e escritores, mas não entre dois escritores. Considere um cenário em que a sessão 52 executa uma atualização em lote dentro de uma transação explícita:
-- Session 52
BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Processing' WHERE Region = 'West';
-- Transaction stays open while application does other work
Essa atualização adquire bloqueios exclusivos em todas as linhas correspondentes. Agora, a sessão 55 tenta atualizar uma dessas mesmas linhas:
-- Session 55
UPDATE Orders SET Priority = 1 WHERE OrderId = 4820;
A sessão 55 aguarda porque a sessão 52 já contém um bloqueio exclusivo nessa linha e não foi confirmada. Uma consulta SELECT em relação a essas linhas ainda teria êxito no RCSI porque lê uma versão de linha em vez de solicitar um bloqueio compartilhado. Com o RCSI removendo o bloqueio de leitor-gravador por padrão, o bloqueio que você encontra no Banco de Dados SQL do Azure normalmente envolve duas sessões que precisam gravar nas mesmas linhas.
Reconhecer cenários comuns de bloqueio
Entender por que o bloqueio acontece ajuda você a impedi-lo. O mecanismo do Banco de Dados SQL do Azure gerencia automaticamente bloqueios, mas determinados padrões levam a um bloqueio mais longo:
Uma consulta de longo prazo que mantém bloqueios por um período estendido. A consulta está executando e fazendo progresso ativamente, mas mantém bloqueios o tempo todo. Outras sessões que precisam de bloqueios conflitantes nos mesmos recursos esperam até que a consulta seja concluída. Para resolver esse problema, procure maneiras de otimizar a consulta, como adicionar índices ou reescrevê-la para tocar menos linhas.
Uma sessão adormecida com uma transação inativa. Uma sessão executa uma instrução dentro de uma transação explícita e, em seguida, interrompe a execução, mas nunca confirma ou reverte. Os bloqueios da transação permanecem mantidos indefinidamente. Esse problema geralmente ocorre quando um aplicativo experimenta um tempo limite ou cancelamento de consulta, mas não emite um ROLLBACK correspondente. Use SET XACT_ABORT ON para que os erros de runtime revertam automaticamente a transação.
Uma sessão que não buscou todas as linhas de resultado. Um aplicativo envia uma consulta, mas não recupera todas as linhas do conjunto de resultados. Os bloqueios podem permanecer mantidos em linhas que ainda não foram buscadas. Verifique se o aplicativo busca todas as linhas de resultados até a conclusão.
Uma sessão em um estado de reversão. Uma consulta que foi encerrada (com KILL ou por um deadlock) está revertendo suas alterações. A reversão pode levar um tempo significativo para grandes modificações, e a sessão mantém bloqueios durante esse processo. Aguarde a conclusão da reversão e evite grandes modificações em lotes durante períodos de alta atividade.
Uma conexão órfã. Um aplicativo cliente falha ou uma estação de trabalho é reiniciada sem fechar a conexão de banco de dados de forma limpa. O servidor não detecta imediatamente a desconexão, portanto, os bloqueios dessa sessão permanecem mantidos. Encerrar a sessão órfã com KILL <session_id>;.
Observação
Dois desses cenários são mitigados no Banco de Dados SQL do Azure por padrão. O RCSI reduz o impacto de linhas de resultados não buscadas porque as consultas SELECT não adquirem bloqueios compartilhados no controle de versão de linha, portanto, linhas não buscadas não bloqueiam os gravadores. A ADR (Recuperação acelerada de banco de dados) torna raras as reversões longas porque pode desfazer alterações quase instantaneamente, independentemente do tamanho da transação. Os três cenários restantes (consultas de longa execução, sessões inativas com transações não confirmadas e conexões órfãs) permanecem totalmente relevantes porque envolvem bloqueios de gravação exclusivos que o RCSI e o ADR não podem liberar antecipadamente.
Resolver bloqueio ativo
Quando você encontra o bloqueio ativo:
- Identifique o bloqueador principal usando a consulta DMV mostrada anteriormente.
- Determine se a transação da sessão de bloqueio pode ser concluída automaticamente ou se está aguardando uma entrada externa.
- Se a sessão de bloqueio for uma conexão órfã ou abandonada, encerre-a com
KILL <session_id>; - Examine o plano de execução da consulta de bloqueio para oportunidades de otimização, como índices ausentes.
Para evitar que o bloqueio seja recorrente, mantenha as transações curtas. Execute apenas as instruções mínimas necessárias em uma transação e confirme imediatamente. Use SET XACT_ABORT ON no código do aplicativo para que qualquer erro de runtime reverta automaticamente toda a transação, o que impede que transações semi-concluídas mantenham os bloqueios indefinidamente. Mova toda a lógica voltada para o usuário para fora dos limites de transação.
Deadlocks
Um deadlock ocorre quando duas ou mais transações formam uma dependência circular. Cada transação contém um bloqueio que o outro precisa e nenhum deles pode prosseguir. Aqui está um exemplo concreto:
- A transação A atualiza a linha 1 e adquire um bloqueio exclusivo.
- A transação B atualiza a linha 2 e adquire um bloqueio exclusivo.
- A transação A tenta atualizar a linha 2 e é bloqueada pela Transação B.
- A transação B tenta atualizar a linha 1 e é bloqueada pela Transação A.
Nenhuma das duas transações pode ser concluída. O monitor de impasse do mecanismo de banco de dados realiza verificações periódicas desses ciclos, com um intervalo padrão de cinco segundos, que diminui para até 100 milissegundos quando os impasses são frequentes. Quando detecta um ciclo, ele escolhe a transação que é menos cara para reverter como vítima, reverte-a e retorna o erro 1205 para o aplicativo. Essa reversão permite que a outra transação seja concluída.
Capturar informações de deadlock
No SQL Server e na Instância Gerenciada de SQL do Azure, a system_health sessão de Eventos Estendidos captura eventos de deadlock por padrão. Você pode consultar o relatório de deadlock do seu buffer de anel usando sys.dm_xe_session_targets e sys.dm_xe_sessions.
No Banco de Dados SQL do Azure, a abordagem é diferente. Você cria uma sessão personalizada de Eventos Estendidos que captura o evento sqlserver.database_xml_deadlock_report e o consulta usando as DMVs com escopo de banco de dados sys.dm_xe_database_sessions e sys.dm_xe_database_session_targets. O exemplo a seguir cria uma sessão de captura de deadlock e consulta seu buffer de anel:
-- Create and start the session
CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE = ON, MAX_MEMORY = 4 MB);
GO
ALTER EVENT SESSION [deadlocks] ON DATABASE STATE = START;
GO
-- Query deadlock events from the ring buffer
DECLARE @tracename sysname = N'deadlocks';
SELECT
d.value('(/event/@timestamp)[1]', 'datetime2') AS deadlock_time,
d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml
FROM (
SELECT CAST(target_data AS XML) AS rb
FROM sys.dm_xe_database_sessions AS s
INNER JOIN sys.dm_xe_database_session_targets AS t
ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
WHERE s.name = @tracename
AND t.target_name = N'ring_buffer'
) AS ring_buffer
CROSS APPLY rb.nodes(
'/RingBufferTarget/event[@name=''database_xml_deadlock_report'']'
) AS xevent(d)
ORDER BY deadlock_time DESC;
O grafo de deadlock inclui três seções. A lista de vítimas identifica qual transação foi encerrada. A lista de processos mostra cada processo envolvido, incluindo o texto da consulta, o nível de isolamento e o modo de bloqueio. A lista de recursos mostra os recursos bloqueados e qual processo possui e aguarda em cada um deles.
No Banco de Dados SQL do Azure, você também pode configurar alertas de deadlock por meio do portal do Azure para receber notificações quando ocorrerem deadlocks.
Prevenir impasses
Você não pode eliminar todos os deadlocks, mas pode reduzir significativamente a frequência com que eles ocorrem:
- Acessar objetos em uma ordem consistente: se todas as transações modificarem a Tabela A antes da Tabela B, as dependências circulares não poderão ser formadas. Padronizar padrões de acesso por meio de procedimentos armazenados.
- Mantenha as transações curtas: transações mais curtas mantêm bloqueios por menos tempo, o que reduz a possibilidade de dependências circulares.
- Usar níveis de isolamento com controle de versão de linha: o RCSI elimina os bloqueios compartilhados durante operações de leitura, removendo assim uma fonte comum de ciclos de deadlock. O bloqueio otimizado no Banco de Dados SQL do Azure reduz ainda mais a probabilidade de deadlock.
- Adicionar índices apropriados: Quando as consultas varrem muitas linhas, elas adquirem bloqueios em uma ampla gama de dados. Adicionar índices que restringem a verificação a menos linhas reduz conflitos de bloqueio.
- Usar forçamento de plano com o Repositório de Consultas: se uma alteração de plano fez uma consulta verificar mais linhas e adquirir mais bloqueios, forçar o plano anterior poderá reduzir deadlocks enquanto você investiga.
Resolver deadlocks no código da aplicação
Os aplicativos sempre devem incluir lógica de repetição para erros de deadlock. Quando uma transação é escolhida como vítima de deadlock, o mecanismo de banco de dados a reverte e retorna o erro 1205. Seu aplicativo deve capturar esse erro, pausar brevemente e reenviar a transação.
BEGIN TRY
BEGIN TRANSACTION;
-- Your data modification statements
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205
BEGIN
ROLLBACK TRANSACTION;
WAITFOR DELAY '00:00:01'; -- Brief pause before retry
-- Retry logic here
END
ELSE
BEGIN
ROLLBACK TRANSACTION;
THROW;
END
END CATCH;
Dica
Randomize o atraso de repetição entre tentativas para impedir que as mesmas duas transações entrem em um deadlock de novo de imediato. Um padrão comum é aguardar entre um e três segundos com um componente aleatório.
Principais conclusões
O bloqueio é normal, mas o bloqueio estendido afeta os usuários, por isso você usa sys.dm_exec_requests para encontrar o bloqueador principal e entender o que ele está fazendo. Cenários comuns incluem consultas de execução prolongada, sessões suspensas com transações não confirmadas e conexões órfãs, todas as quais você aborda mantendo as transações curtas, usando SET XACT_ABORT ON e garantindo que as aplicações gerenciem corretamente as conexões e os conjuntos de resultados. Os deadlocks se formam quando as transações criam dependências de bloqueio circular e o mecanismo de banco de dados as resolve automaticamente encerrando a transação mais barata e retornando o erro 1205. Você reduz a frequência de deadlock acessando objetos em uma ordem consistente, mantendo as transações curtas, usando níveis de isolamento de versionamento de linha e adicionando índices apropriados. O código do aplicativo sempre deve incluir lógica de reintento para o erro 1205, para que ele possa se recuperar automaticamente quando for selecionado como vítima de um deadlock.