Entender e resolver problemas de bloqueio do SQL Server

Aplica-se a: SQL Server (todas as versões com suporte), Instância Gerenciada de SQL do Azure

Número original do KB: 224453

Objetivo

O artigo descreve o bloqueio no SQL Server e demonstra como solucionar problemas e resolver o bloqueio.

Neste artigo, o termo conexão refere-se a uma única sessão conectada do banco de dados. Cada conexão aparece como uma ID de sessão (SPID) ou session_id em muitas DMVs. Cada um desses SPIDs geralmente é chamado de processo, embora não seja um contexto de processo separado no sentido usual. Em vez disso, cada SPID consiste nos recursos do servidor e nas estruturas de dados necessárias para fazer o serviço das solicitações de uma única conexão de um determinado cliente. Um único aplicativo cliente pode ter uma ou mais conexões. Da perspectiva do SQL Server, não há diferença entre várias conexões de um único aplicativo cliente em um único computador cliente e várias conexões de vários aplicativos cliente ou vários computadores cliente; elas são atômicas. Uma conexão pode bloquear outra conexão, independentemente do cliente de origem.

Observação

Este artigo se concentra em instâncias do SQL Server, incluindo Instâncias Gerenciadas de SQL do Azure. Para obter informações específicas sobre como solucionar o bloqueio no Banco de dados SQL do Azure, consulte Entender e resolver problemas de bloqueio do Banco de dados SQL do Azure.

O que é bloqueio?

O bloqueio é uma característica inevitável e por design de qualquer RDBMS (sistema de gerenciamento de banco de dados relacional) com simultaneidade baseada em bloqueio. Conforme mencionado anteriormente, no SQL Server, o bloqueio ocorre quando uma sessão mantém um bloqueio em um recurso específico e um segundo SPID tenta adquirir um tipo de bloqueio conflitantes no mesmo recurso. Normalmente, o período para o qual o primeiro SPID bloqueia o recurso é pequeno. Quando a sessão de propriedade libera o bloqueio, a segunda conexão fica livre para adquirir seu próprio bloqueio no recurso e continuar o processamento. O bloqueio, conforme descrito aqui, é um comportamento normal e pode acontecer muitas vezes ao longo de um dia sem nenhum efeito perceptível no desempenho do sistema.

A duração e o contexto de transação de uma consulta determinam por quanto tempo seus bloqueios são mantidos e, assim, seu efeito em outras consultas. Se a consulta não for executada em uma transação (e nenhuma dica de bloqueio for usada), os bloqueios para instruções SELECT só serão mantidos em um recurso no momento em que ela estiver realmente sendo lida, não durante a consulta. Para instruções INSERT, UPDATE e DELETE, os bloqueios são mantidos durante a consulta, tanto para consistência de dados quanto para permitir que a consulta seja revertida, se necessário.

Para consultas executadas em uma transação, a duração para a qual os bloqueios são mantidos é determinada pelo tipo de consulta, pelo nível de isolamento da transação e se as dicas de bloqueio são usadas na consulta. Para obter uma descrição de bloqueio, dicas de bloqueio e níveis de isolamento de transação, consulte os seguintes artigos:

Quando o travamento e o bloqueio persistem até o ponto em que há um efeito prejudicial no desempenho do sistema, isso ocorre devido a um dos seguintes motivos:

  • Um SPID mantém bloqueios em um conjunto de recursos por um longo período de tempo antes de liberá-los. Esse tipo de bloqueio se resolve ao longo do tempo, mas pode causar degradação do desempenho.

  • Um SPID mantém bloqueios em um conjunto de recursos e nunca os libera. Esse tipo de bloqueio não se resolve e impede o acesso aos recursos afetados indefinidamente.

No primeiro cenário, a situação pode ser muito fluida, pois diferentes SPIDs causam bloqueio em diferentes recursos ao longo do tempo, criando um destino móvel. Essas situações são difíceis de solucionar usando o SQL Server Management Studio para restringir o problema a consultas individuais. Por outro lado, a segunda situação resulta em um estado consistente que pode ser mais fácil de diagnosticar.

Aplicativos e bloqueio

Pode haver uma tendência de se concentrar no ajuste do lado do servidor e nos problemas de plataforma ao enfrentar um problema de bloqueio. No entanto, a atenção prestada somente ao banco de dados pode não levar a uma resolução e pode absorver o tempo e a energia melhor direcionados para examinar o aplicativo cliente e as consultas que ele envia. Independentemente do nível de visibilidade que o aplicativo expõe em relação às chamadas de banco de dados que estão sendo feitas, um problema de bloqueio requer frequentemente a inspeção das instruções SQL exatas enviadas pelo aplicativo e o comportamento exato do aplicativo em relação ao cancelamento de consulta, ao gerenciamento de conexões, à busca de todas as linhas de resultado e assim por diante. Se a ferramenta de desenvolvimento não permitir controle explícito sobre o gerenciamento de conexões, cancelamento de consulta, tempo limite de consulta, busca de resultados e assim por diante, os problemas de bloqueio podem não ser resolvidos. Esse potencial deve ser examinado de perto antes de selecionar uma ferramenta de desenvolvimento de aplicativos para o SQL Server, especialmente para ambientes OLTP sensíveis ao desempenho.

Preste atenção ao desempenho do banco de dados durante a fase de design e construção do banco de dados e do aplicativo. Em particular, o consumo de recursos, o nível de isolamento e o comprimento do caminho da transação devem ser avaliados para cada consulta. Cada consulta e transação deve ser o mais leve possível. Uma boa disciplina de gerenciamento de conexões deve ser executada, sem ela, o aplicativo pode parecer ter um desempenho aceitável em um número baixo de usuários, mas o desempenho pode ser degradado significativamente à medida que o número de usuários aumenta.

Com o design adequado de aplicativo e consulta, o SQL Server é capaz de dar suporte a muitos milhares de usuários simultâneos em um único servidor, com pouco bloqueio.

Solucionar problemas de bloqueio

Independentemente de qual situação de bloqueio estamos, a metodologia para solucionar problemas de bloqueio é a mesma. Essas separações lógicas são o que ditará o restante da composição deste artigo. O conceito é localizar o bloqueador de cabeçalho e identificar o que essa consulta está fazendo e por que ela está bloqueando. Depois que a consulta problemática é identificada (ou seja, o que está mantendo bloqueios por um período prolongado), a próxima etapa é analisar e determinar por que o bloqueio está acontecendo. Depois de entendermos o motivo, podemos fazer alterações reformulando a consulta e a transação.

Etapas na solução de problemas:

  1. Identificar a sessão de bloqueio principal (bloqueador de cabeçalho)

  2. Localizar a consulta e a transação que está causando o bloqueio (o que está mantendo bloqueios por um período prolongado)

  3. Analisar/entender por que ocorre o bloqueio prolongado

  4. Resolver o problema de bloqueio recriando a consulta e a transação

Agora vamos nos aprofundar para discutir como identificar a sessão de bloqueio principal com uma captura de dados apropriada.

Coletar informações de bloqueio

Para combater a dificuldade de solucionar problemas de bloqueio, um administrador de banco de dados pode usar scripts SQL que monitoram constantemente o estado de travamento e bloqueio em SQL Server. Para coletar esses dados, há dois métodos complementares.

A primeira é consultar DMOs (objetos de gerenciamento dinâmico) e armazenar os resultados para comparação ao longo do tempo. Alguns objetos referenciados neste artigo são DMVs (exibições de gerenciamento dinâmico) e alguns são DMFs (funções de gerenciamento dinâmico).

A segunda é usar XEvents (Eventos Estendidos) ou Rastreamentos do SQL Profiler para capturar o que está em execução. Como o Rastreamento SQL o SQL Server Profiler foram preteridos, este guia de solução de problemas se concentrará em XEvents.

Coletar informações de DMVs

Referenciar DMVs para solucionar problemas de bloqueio tem o objetivo de identificar o SPID (ID de sessão) no cabeçalho da cadeia de bloqueio e na Instrução SQL. Procure por SPIDs das vítimas que estão sendo bloqueadas. Se algum SPID estiver sendo bloqueado por outro SPID, investigue o SPID que possui o recurso (o SPID de bloqueio). Esse SPID do proprietário também está sendo bloqueado? Você pode percorrer a cadeia para encontrar o bloqueador de cabeçalho e investigar por que ele está mantendo o bloqueio.

Para fazer isso, use um dos seguintes métodos:

  • No Pesquisador de Objetos SSMS (SQL Server Management Studio), clique com o botão direito do mouse no objeto de servidor de nível superior, expanda Relatórios, expanda Relatórios Padrão e, em seguida, selecione Atividades – Todas as Transações de Bloqueio. Este relatório mostra as transações atuais no cabeçalho de uma cadeia de bloqueio. Se você expandir a transação, o relatório mostrará as transações bloqueadas pela transação principal. Esse relatório também mostrará o Bloqueio da Instrução SQL e a Instrução SQL Bloqueada.

  • Abra o Monitor de Atividade no SSMS e consulte a coluna Bloqueado Por. Encontre mais informações sobre o Monitor de Atividades aqui.

Métodos mais detalhados baseados em consulta também estão disponíveis usando DMVs:

  • Os comandos sp_who e sp_who2 são comandos mais antigos para mostrar todas as sessões atuais. A DMV sys.dm_exec_sessions retorna mais dados em um conjunto de resultados mais fácil de consultar e filtrar. Você encontrará sys.dm_exec_sessions no núcleo de outras consultas.

  • Se você já tiver uma sessão específica identificada, poderá usar DBCC INPUTBUFFER(<session_id>) para localizar a última instrução que foi enviada por uma sessão. Resultados semelhantes podem ser retornados com a DMF (função de gerenciamento dinâmico) sys.dm_exec_input_buffer, em um conjunto de resultados mais fácil de consultar e filtrar, fornecendo o session_id e o request_id. Por exemplo, para retornar a consulta mais recente enviada pelo session_id 66 e request_id 0:

SELECT * FROM sys.dm_exec_input_buffer (66,0);
  • Consulte sys.dm_exec_requests e referencie a coluna blocking_session_id. Quando blocking_session_id = 0, uma sessão não está sendo bloqueada. Embora sys.dm_exec_requests liste apenas as solicitações em execução no momento, qualquer conexão (ativa ou não) será listada em sys.dm_exec_sessions. Crie essa junção comum entre sys.dm_exec_requests e sys.dm_exec_sessions na próxima consulta. Lembre-se de que para ser retornada por sys.dm_exec_requests, a consulta deve estar sendo executada ativamente com SQL Server.

  • Execute esta consulta de exemplo para localizar as consultas que executam ativamente e o texto atual do lote sql ou o texto do buffer de entrada, usando as DMVs sys.dm_exec_sql_text ou sys.dm_exec_input_buffer. Se os dados retornados pela coluna text de sys.dm_exec_sql_text for NULL, a consulta não estará em execução no momento. Nesse caso, a coluna event_info de sys.dm_exec_input_buffer conterá a última cadeia de caracteres de comando passada para o mecanismo SQL. Essa consulta também pode ser usada para identificar sessões bloqueando outras sessões, incluindo uma lista de session_ids bloqueados por session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Execute esta consulta de exemplo mais elaborada, fornecida pelo Suporte da Microsoft, para identificar o cabeçalho de uma cadeia de bloqueio de várias sessões, incluindo o texto da consulta das sessões envolvidas em uma cadeia de bloqueio.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;
SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • Referência sys.dm_os_waiting_tasks que está na camada de thread/tarefa do SQL Server. Isso retorna informações sobre qual SQL wait_type a solicitação está enfrentando no momento. Como sys.dm_exec_requests, somente solicitações ativas são retornadas por sys.dm_os_waiting_tasks.

Observação

Para obter muito mais sobre tipos de espera, incluindo estatísticas de espera agregadas ao longo do tempo, consulte a DMV sys.dm_db_wait_stats.

  • Use a DMV sys.dm_tran_locks para obter informações mais granulares sobre quais bloqueios foram colocados por consultas. Essa DMV pode retornar grandes quantidades de dados em uma instância de SQL Server de produção e é útil para diagnosticar quais bloqueios são mantidos no momento.

Devido à INNER JOIN em sys.dm_os_waiting_tasks, a consulta a seguir restringe a saída de sys.dm_tran_locks apenas a solicitações bloqueadas no momento, seu status de espera e seus bloqueios:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

Com as DMVs, armazenar os resultados da consulta ao longo do tempo fornecerá pontos de dados que permitirão que você examine o bloqueio em um intervalo de tempo especificado para identificar o bloqueio persistente ou as tendências. A ferramenta de acesso para CSS para solucionar esses problemas é usar o coletor de dados PSSDiag. Essa ferramenta usa as "Estatísticas de Desempenho do SQL Server" para coletar conjuntos de resultados das DMVs referenciadas acima, ao longo do tempo. Como essa ferramenta está em constante evolução, examine a versão pública mais recente do DiagManager no GitHub.

Coletar informações de eventos estendidos

Além das informações acima, geralmente é necessário capturar um rastreamento das atividades no servidor para investigar completamente um problema de bloqueio no SQL Server. Por exemplo, se uma sessão executar várias instruções dentro de uma transação, somente a última instrução enviada será representada. No entanto, uma das instruções anteriores pode ser o motivo pelo qual os bloqueios ainda estão sendo mantidos. Um rastreamento permitirá que você veja todos os comandos executados por uma sessão dentro da transação atual.

Há duas maneiras de capturar rastreamentos no SQL Server; Eventos Estendidos (XEvents) e Rastreamentos do Criador de Perfil. No entanto, os rastreamentos SQL usando o Criador de Perfil do SQL foram preteridos. O XEvents é a plataforma de rastreamento mais recente e superior que permite mais versatilidade e menos impacto para o sistema observado, e sua interface é integrada ao SSMS.

Há sessões de Evento Estendido pré-criadas prontas para iniciar no SSMS, listadas Pesquisador de Objetos no menu do XEvent Profiler. Para obter mais informações, consulte XEvent Profiler. Você também pode criar suas próprias sessões de Evento Estendido personalizadas no SSMS, consulte Assistente de Nova Sessão de Eventos Estendidos. Para solucionar problemas de bloqueio, normalmente capturaremos:

  • Erros de Categoria:
    • Atenção
    • Blocked_process_report**
    • Error_reported (Administrador do Canal)
    • Exchange_spill
    • Execution_warning

**Para configurar o limite e a frequência em que os relatórios de processo bloqueados são gerados, use o comando sp_configure para configurar a opção de limite de processo bloqueado, que pode ser definida em segundos. Por padrão, nenhum relatório de processo bloqueado é produzido.

  • Avisos de Categoria:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning
  • Execução de Categoria:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting
  • Bloqueio de Categoria

    • Lock_deadlock
  • Sessão de Categoria

    • Existing_connection
    • Logon
    • Fazer Logoff

Identificar e resolver cenários comuns de bloqueio

Ao examinar as informações acima, você pode determinar a causa da maioria dos problemas de bloqueio. O restante deste artigo é uma discussão sobre como usar essas informações para identificar e resolver alguns cenários comuns de bloqueio. Esta discussão pressupõe que você tenha usado os scripts de bloqueio (referenciados anteriormente) para capturar informações sobre os SPIDs de bloqueio e ter capturado a atividade do aplicativo usando uma sessão XEvent.

Analisar dados de bloqueio

  • Examine a saída das DMVs sys.dm_exec_requests e sys.dm_exec_sessions para determina os cabeçalhos das cadeias de bloqueio, usando blocking_these e session_id. Isso identificará com mais clareza quais solicitações estão bloqueadas e quais estão bloqueando. Examine ainda mais as sessões que estão bloqueadas e bloqueando. Há uma raiz ou algo comum para a cadeia de bloqueio? Eles provavelmente compartilham uma tabela comum, e uma ou mais das sessões envolvidas em uma cadeia de bloqueio está executando uma operação de gravação.

  • Examine a saída das DMVs sys.dm_exec_requests e sys.dm_exec_sessions para obter informações sobre os SPIDs no cabeçalho da cadeia de bloqueio. Procure as seguintes colunas:

    • sys.dm_exec_requests.status
      Esta coluna mostra o status de uma solicitação específica. Normalmente, um status de suspensão indica que o SPID concluiu a execução e está aguardando o aplicativo enviar outra consulta ou lote. Um status executável ou em execução indica que o SPID está processando uma consulta no momento. A tabela a seguir fornece breves explicações dos vários valores de status.

      Status Significado
      Histórico O SPID está executando uma tarefa em segundo plano, como detecção de deadlock, gravador de log ou ponto de verificação.
      Inativo O SPID não está em execução no momento. Isso geralmente indica que o SPID está aguardando um comando do aplicativo.
      Em execução No momento, o SPID está em execução em um agendador.
      Executável O SPID está na fila executável de um agendador e aguardando para obter o tempo do agendador.
      Suspenso O SPID está aguardando um recurso, como um bloqueio ou uma trava.
    • sys.dm_exec_sessions.open_transaction_count
      Esta coluna informa o número de transações abertas nesta sessão. Se esse valor for maior que 0, o SPID estará dentro de uma transação aberta e poderá estar mantendo bloqueios adquiridos por qualquer instrução dentro da transação.

    • sys.dm_exec_requests.open_transaction_count
      Da mesma forma, essa coluna informa o número de transações abertas nesta solicitação. Se esse valor for maior que 0, o SPID estará dentro de uma transação aberta e poderá estar mantendo bloqueios adquiridos por qualquer instrução dentro da transação.

    • sys.dm_exec_requests.wait_type, wait_timee last_wait_type
      Se sys.dm_exec_requests.wait_type for NULL, a solicitação não estará aguardando nada no momento e o valor last_wait_type indicará o último wait_type que a solicitação encontrou. Para obter mais informações sobre sys.dm_os_wait_stats e uma descrição dos tipos de espera mais comuns, consulte sys.dm_os_wait_stats. O valor wait_time pode ser usado para determinar se a solicitação está progredindo. Quando uma consulta em relação à tabela sys.dm_exec_requests retorna um valor na coluna wait_time que é menor que o valor wait_time de uma consulta anterior de sys.dm_exec_requests, isso indica que o bloqueio anterior foi adquirido e liberado e agora está aguardando um novo bloqueio (supondo wait_time que não seja zero). Isso pode ser verificado comparando a saída wait_resource entre sys.dm_exec_requests, que exibe o recurso para o qual a solicitação está aguardando.

    • sys.dm_exec_requests.wait_resource Essa coluna indica o recurso no qual uma solicitação bloqueada está aguardando. A tabela a seguir lista formatos wait_resource comuns e seu significado:

      Resource Formatar Exemplo Explicação
      Table DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 Nesse caso, a ID do banco de dados 5 é o banco de dados de exemplo pubs, object_id 261575970 é a tabela de títulos e 1 é o índice clusterizado.
      Page DatabaseID:FileID:PageID PÁGINA: 5:1:104 Nesse caso, a ID do banco de dados 5 é pubs, a ID do arquivo 1 é o arquivo de dados primário e a página 104 é uma página que pertence à tabela de títulos. Para identificar a object_id à qual a página pertence, use a função de gerenciamento dinâmico sys.dm_db_page_info, passando no DatabaseID, FileId, PageId do wait_resource.
      Chave DatabaseID:Hobt_id (Valor de hash para chave de índice) CHAVE: 5:72057594044284928 (3300a4f361aa) Nesse caso, a ID do banco de dados 5 é Pubs, Hobt_ID 72057594044284928 corresponde a index_id 2 para object_id 261575970 (tabela de títulos). Use a exibição de catálogo sys.partitions para associar o hobt_id a um determinado index_id e object_id. Não é possível cancelar o hash do hash da chave de índice para um valor de chave específico.
      Linha DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Nesse caso, a ID do banco de dados 5 é pubs, a ID do arquivo 1 é o arquivo de dados primário, a página 104 é uma página que pertence à tabela de títulos e o slot 3 indica a posição da linha na página.
      Compilar DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Nesse caso, a ID do banco de dados 5 é pubs, a ID do arquivo 1 é o arquivo de dados primário, a página 104 é uma página que pertence à tabela de títulos e o slot 3 indica a posição da linha na página.
    • sys.dm_tran_active_transactions A DMV sys.dm_tran_active_transactions contém dados sobre transações abertas que podem ser unidas a outras DMVs para obter uma imagem completa das transações que aguardam confirmação ou reversão. Use a consulta a seguir para retornar informações sobre transações abertas, ingressadas em outras DMVs, incluindo sys.dm_tran_session_transactions. Considere o estado atual de uma transação, transaction_begin_time e outros dados de situação para avaliar se ela pode ser uma fonte de bloqueio.

      SELECT tst.session_id, [database_name] = db_name(s.database_id)
      , tat.transaction_begin_time
      , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
      , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                      WHEN 2 THEN 'Read-only transaction'
                                                      WHEN 3 THEN 'System transaction'
                                                      WHEN 4 THEN 'Distributed transaction' END
      , input_buffer = ib.event_info, tat.transaction_uow     
      , transaction_state  = CASE tat.transaction_state    
                  WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                  WHEN 1 THEN 'The transaction has been initialized but has not started.'
                  WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                  WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                  WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
                  WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                  WHEN 6 THEN 'The transaction has been committed.'
                  WHEN 7 THEN 'The transaction is being rolled back.'
                  WHEN 8 THEN 'The transaction has been rolled back.' END 
      , transaction_name = tat.name, request_status = r.status
      , tst.is_user_transaction, tst.is_local
      , session_open_transaction_count = tst.open_transaction_count  
      , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
      FROM sys.dm_tran_active_transactions tat 
      INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
      INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
      LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
      CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;
      
    • Outras colunas

      As colunas restantes em sys.dm_exec_sessions e sys.dm_exec_request também podem fornecer informações sobre a raiz de um problema. Sua utilidade varia dependendo das circunstâncias do problema. Por exemplo, você pode determinar se o problema ocorre somente com determinados clientes (hostname), em determinadas bibliotecas de rede (client_interface_name), quando o último lote enviado por um SPID era last_request_start_time em sys.dm_exec_sessions, por quanto tempo uma solicitação estava em execução usado start_time em sys.dm_exec_requests e assim por diante.

Cenários comuns de bloqueio

A tabela a seguir mapeia sintomas comuns para suas causas prováveis.

As colunas wait_type, open_transaction_count e status se referem às informações retornadas por sys.dm_exec_request, outras colunas podem ser retornadas por sys.dm_exec_sessions. A coluna "Resolva?" indica se o bloqueio será resolvido por conta própria ou se a sessão deve ser encerrada por meio do comando KILL. Para saber mais, confira ENCERRAR (Transact-SQL).

Cenário Wait_type Open_Tran Status Resolve? Outros Sintomas
1 NÃO NULO >= 0 executável Sim, quando a consulta for concluída. Em sys.dm_exec_sessions, reads, cpu_time e/ou nas colunas memory_usage aumentarão ao longo do tempo. A duração da consulta será alta quando concluída.
2 NULL >0 em suspensão Não, mas o SPID pode ser encerrado. Um sinal de atenção pode ser visto na sessão de Evento Estendido para esse SPID, indicando que ocorreu um tempo limite ou cancelamento de consulta.
3 NULL >= 0 executável Não. Não será resolvido até que o cliente busque todas as linhas ou feche a conexão. O SPID pode ser encerrado, mas pode levar até 30 segundos. Se open_transaction_count = 0 e o SPID manter bloqueios enquanto o nível de isolamento da transação for padrão (LER CONFIRMADOS), essa é uma causa provável.
4 Várias >= 0 executável Não. Não será resolvido até que o cliente cancele consultas ou feche conexões. SPIDs podem ser encerrados, mas isso pode levar até 30 segundos. A coluna hostname em sys.dm_exec_sessions para o SPID no cabeçalho de uma cadeia de bloqueio será a mesma que um dos SPID que está bloqueando.
5 NULL >0 reversão Sim. Um sinal de atenção pode ser visto na sessão de Eventos Estendidos para esse SPID, indicando que um tempo limite de consulta ou cancelamento ocorreu ou simplesmente uma instrução de reversão foi emitida.
6 NULL >0 em suspensão Eventualmente. Quando o Windows NT determina que a sessão não está mais ativa, a conexão é interrompida. O valor last_request_start_time em sys.dm_exec_sessions é muito anterior à hora atual.

Cenários de bloqueio detalhados

Cenário 1: bloqueio causado por uma consulta normalmente em execução com um longo tempo de execução

Nesse cenário, uma consulta em execução ativamente adquiriu bloqueios e os bloqueios não são liberados (ela é afetada pelo nível de isolamento da transação). Portanto, outras sessões aguardarão os bloqueios até que sejam liberados.

Solução:

A solução para esse tipo de problema de bloqueio é procurar maneiras de otimizar a consulta. Essa classe de problema de bloqueio pode ser um problema de desempenho e exigir que você o busque como tal. Para obter informações sobre como solucionar problemas de uma consulta de execução lenta específica, consulte Como solucionar problemas de consultas de execução lenta no SQL Server. Para obter mais informações, consulte Monitorar e Ajustar o Desempenho.

Os relatórios internos para SSMS do Repositório de Consultas (introduzidos no SQL Server 2016) também são uma ferramenta altamente recomendada e valiosa para identificar as consultas mais dispendidas e planos de execução abaixo do ideal.

Se você tiver uma consulta de execução longa que está bloqueando outros usuários e não pode ser otimizada, considere movê-la de um ambiente OLTP para um sistema de relatórios dedicado. Você também pode usar grupos de disponibilidade Always On para sincronizar uma réplica somente leitura do banco de dados.

Observação

O bloqueio durante a execução da consulta pode ser causado pelo escalonamento de consulta, um cenário em que os bloqueios de linha ou página são escalonados para bloqueios de tabela. O Microsoft SQL Server determina dinamicamente quando executar o escalonamento de bloqueio. A maneira mais simples e segura de evitar o escalonamento de bloqueios é manter as transações curtas e reduzir o volume de bloqueio de consultas caras para que os limites de escalonamento de bloqueio não sejam excedidos. Para obter mais informações sobre como detectar e evitar o escalonamento excessivo de bloqueios, consulte Resolver o problema de bloqueio causado pelo escalonamento de bloqueios.

Cenário 2: bloqueio causado por um SPID em suspensão que tem uma transação não confirmada

Esse tipo de bloqueio geralmente pode ser identificado por um SPID que está em suspensão ou aguardando um comando, mas cujo nível de aninhamento de transação (@@TRANCOUNT, open_transaction_count de sys.dm_exec_requests) é maior que zero. Essa situação pode ocorrer se o aplicativo tiver um tempo limite de consulta ou emitir um cancelamento sem emitir o número necessário de instruções REVERTER e/ou CONFIRMAR. Quando um SPID receber um tempo limite de consulta ou um cancelamento, ele encerrará a consulta atual e o lote, mas não reverterá ou confirmará automaticamente a transação. O aplicativo é responsável por isso, pois O SQL Server não pode supor que uma transação inteira deve ser revertida devido a uma única consulta ser cancelada. O tempo limite ou cancelamento da consulta será exibido como um evento de sinal de ATENÇÃO para o SPID na sessão de Evento Estendido.

Para demonstrar uma transação explícita não confirmada, emita a seguinte consulta:

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

Em seguida, execute essa consulta na mesma janela:

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

A saída da segunda consulta indica que o nível de aninhamento de transação é um. Todos os bloqueios adquiridos na transação ainda serão mantidos até que a transação seja confirmada ou revertida. Se os aplicativos abrirem e confirmarem transações explicitamente, uma comunicação ou outro erro poderá deixar a sessão e sua transação em um estado aberto.

Use o script anteriormente neste artigo com base em sys.dm_tran_active_transactions para identificar transações não confirmadas no momento em toda a instância.

Resoluções:

  • Além disso, essa classe de problema de bloqueio pode ser um problema de desempenho e exigir que você o busque como tal. Se o tempo de execução da consulta puder ser reduzido, o tempo limite ou o cancelamento da consulta pode não ocorrer. É importante que o aplicativo possa lidar com o tempo limite ou cancelar cenários caso eles surjam, mas você também pode se beneficiar da análise do desempenho da consulta.

  • Os aplicativos devem gerenciar corretamente os níveis de aninhamento de transações ou podem causar um problema de bloqueio após o cancelamento da consulta dessa maneira. Considere o seguinte:

    • No manipulador de erro do aplicativo cliente, execute IF @@TRANCOUNT > 0 ROLLBACK TRAN após qualquer erro, mesmo que o aplicativo cliente não acredite que uma transação esteja aberta. A verificação de transações abertas é necessária, pois um procedimento armazenado chamado durante o lote pode ter iniciado uma transação sem o conhecimento do aplicativo cliente. Determinadas condições, como cancelar a consulta, impedem que o procedimento seja executado após a instrução atual, portanto, mesmo que o procedimento tenha lógica para verificar IF @@ERROR <> 0 e anular a transação, esse código de reversão não será executado nesses casos.

    • Se o pool de conexões estiver sendo usado em um aplicativo que abre a conexão e executa algumas consultas antes de liberar a conexão de volta para o pool, como um aplicativo baseado na Web, desabilitar temporariamente o pool de conexões pode ajudar a aliviar o problema até que o aplicativo cliente seja modificado para lidar com os erros adequadamente. Ao desabilitar o pool de conexões, a liberação da conexão causará uma desconexão física da conexão SQL Server, fazendo com que o servidor reverta as transações abertas.

    • Use SET XACT_ABORT ON para a conexão ou em qualquer procedimento armazenado que inicie transações e não esteja limpando após um erro. No caso de um erro em tempo de execução, essa configuração anulará todas as transações abertas e retornará o controle ao cliente. Para obter mais informações, examine SET XACT_ABORT (Transact-SQL).

Observação

A conexão não é redefinida até que seja reutilizada do pool de conexões, portanto, é possível que um usuário possa abrir uma transação e liberar a conexão com o pool de conexões, mas ela pode não ser reutilizada por vários segundos, durante o qual a transação permaneceria aberta. Se a conexão não for reutilizada, a transação será anulada quando a conexão expirar e for removida do pool de conexões. Portanto, é ideal que o aplicativo cliente anule transações em seu manipulador de erros ou use SET XACT_ABORT ON para evitar esse possível atraso.

Cuidado

Após SET XACT_ABORT ON, as instruções T-SQL após uma instrução que causa um erro não serão executadas. Isso pode afetar o fluxo pretendido do código existente.

Cenário 3: bloqueio causado por um SPID cujo aplicativo cliente correspondente não busca todas as linhas de resultado até a conclusão

Depois de enviar uma consulta para o servidor, todos os aplicativos devem buscar imediatamente todas as linhas de resultado até a conclusão. Se um aplicativo não buscar todas as linhas de resultado, os bloqueios poderão ser deixados nas tabelas, bloqueando outros usuários. Se você estiver usando um aplicativo que envia instruções SQL de forma transparente para o servidor, o aplicativo deverá buscar todas as linhas de resultado. Se isso não ocorrer (e se ele não puder ser configurado para fazer isso), talvez você não consiga resolver o problema de bloqueio. Para evitar o problema, você pode restringir aplicativos mal-comportados a um relatório ou a um banco de dados de suporte a decisões, separado do banco de dados OLTP principal.

Solução:

O aplicativo deve ser reescrito para buscar todas as linhas do resultado até a conclusão. Isso não permite o uso de OFFSET e FETCH na cláusula ORDER BY de uma consulta para executar a paginação do lado do servidor.

Cenário 4: bloqueio causado por um deadlock de cliente/servidor distribuído

Ao contrário de um deadlock convencional, um deadlock distribuído não é detectável usando o gerenciador de bloqueioS RDBMS. Isso ocorre porque apenas um dos recursos envolvidos no deadlock é um bloqueio de SQL Server. O outro lado do deadlock está no nível do aplicativo cliente, sobre o qual SQL Server não tem controle. As duas seções a seguir mostram exemplos exemplos de como isso pode acontecer e possíveis maneiras pelas quais o aplicativo pode evitá-lo.

Exemplo A: deadlock distribuído de cliente/Servidor com um único thread de cliente

Se o cliente tiver várias conexões abertas e um único thread de execução, o deadlock distribuído a seguir poderá ocorrer. Observação, o termo dbproc usado aqui refere-se à estrutura de conexão do cliente.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

No caso mostrado acima, um único thread de aplicativo cliente tem duas conexões abertas. Ele envia de forma assíncrona uma operação SQL no dbproc1. Isso significa que ele não aguarda o retorno da chamada antes de continuar. Em seguida, o aplicativo envia outra operação SQL no dbproc2 e aguarda os resultados para iniciar o processamento dos dados retornados. Quando os dados começam a voltar (o que o dbproc responde pela primeira vez– suponha que seja dbproc1), eles processam para concluir todos os dados retornados nesse dbproc. Ele busca resultados de dbproc1 até que o SPID1 seja bloqueado em um bloqueio mantido pelo SPID2 (porque as duas consultas estão sendo executadas de forma assíncrona no servidor). Neste ponto, dbproc1 aguardará indefinidamente por mais dados. O SPID2 não está bloqueado em uma trava, mas tenta enviar dados para seu cliente, dbproc2. No entanto, dbproc2 é efetivamente bloqueado no dbproc1 na camada do aplicativo, pois o único thread de execução para o aplicativo está em uso pelo dbproc1. Isso resulta em um deadlock que o SQL Server não pode detectar ou resolver porque apenas um dos recursos envolvidos é um recurso SQL Server.

Exemplo B: deadlock distribuído de cliente/servidor com um thread por conexão

Mesmo que exista um thread separado para cada conexão no cliente, uma variação desse deadlock distribuído ainda poderá ocorrer, conforme mostrado a seguir.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Esse caso é semelhante ao Exemplo A, exceto que dbproc2 e SPID2 estão executando uma instrução SELECT com a intenção de executar o processamento de linha por vez e entregar cada linha por meio de um buffer para dbproc1 para uma instrução INSERT, UPDATE ou DELETE na mesma tabela. Eventualmente, o SPID1 (executando o INSERT, UPDATE ou DELETE) torna-se bloqueado em uma trava mantida pelo SPID2 (executando o SELECT). O SPID2 grava uma linha de resultado no dbproc2 do cliente. Dbproc2 tenta passar a linha em um buffer para dbproc1, mas encontra dbproc1 ocupado (está bloqueado aguardando o SPID1 concluir o INSERT atual, que está bloqueado no SPID2). Neste ponto, dbproc2 é bloqueado na camada de aplicativo por dbproc1 cujo SPID (SPID1) está bloqueado no nível do banco de dados pelo SPID2. Novamente, isso resulta em um deadlock que o SQL Server não pode detectar ou resolver porque apenas um dos recursos envolvidos é um recurso do SQL Server.

Os dois exemplos A e B são problemas fundamentais que os desenvolvedores de aplicativos devem estar cientes. Eles devem codificar aplicativos para lidar com esses casos adequadamente.

Solução:

Quando um tempo limite de consulta tiver sido fornecido, se o deadlock distribuído ocorrer, ele será interrompido quando o tempo limite ocorrer. Consulte a documentação do provedor de conexão para obter mais informações sobre como usar um tempo limite de consulta.

Cenário 5: bloqueio causado por uma sessão em um estado de reversão

Uma consulta de modificação de dados que é encerrada ou cancelada fora de uma transação definida pelo usuário será revertida. Isso também pode ocorrer como um efeito colateral da desconexão da sessão de rede do cliente ou quando uma solicitação é selecionada como vítima de deadlock. Isso geralmente pode ser identificado observando a saída de sys.dm_exec_requests, que pode indicar REVERSÃO command, e a coluna percent_complete pode mostrar o progresso.

Uma consulta de modificação de dados que é encerrada ou cancelada fora de uma transação definida pelo usuário será revertida. Isso também pode ocorrer como um efeito colateral da reinicialização do computador cliente e da desconexão da sessão de rede. Da mesma forma, uma consulta selecionada como a vítima de deadlock será revertida. Uma consulta de modificação de dados geralmente não pode ser revertida mais rapidamente do que as alterações aplicadas inicialmente. Por exemplo, se uma instrução DELETE, INSERTou UPDATE tivesse sido executada por uma hora, poderia levar pelo menos uma hora para ser revertida. Esse é o comportamento esperado, pois as alterações feitas devem ser revertidas ou a integridade transacional e física no banco de dados seria comprometida. Como isso deve acontecer, o SQL Server marca o SPID em um estado de ouro ou reversão (o que significa que ele não pode ser encerrado ou selecionado como uma vítima de deadlock). Isso geralmente pode ser identificado observando a saída de sp_who, que pode indicar o comando REVERSÃO. A coluna status de sys.dm_exec_sessions indicará um status REVERSÃO.

Observação

Reversões longas são raras quando o recurso Recuperação Acelerada de Banco de Dados está habilitado. Esse recurso foi introduzido no SQL Server 2019.

Solução:

Você deve aguardar até que a sessão termine de reverter as alterações feitas.

Se a instância for desligada no meio dessa operação, o banco de dados estará no modo de recuperação após a reinicialização e ficará inacessível até que todas as transações abertas sejam processadas. A recuperação de inicialização leva essencialmente a mesma quantidade de tempo por transação que a recuperação em tempo de execução, e o banco de dados fica inacessível durante esse período. Portanto, forçar o servidor a corrigir um SPID em um estado de reversão geralmente será contraproducente. No SQL Server 2019 com a Recuperação de Banco de Dados Acelerada habilitada, isso não deve ocorrer.

Para evitar essa situação, não execute operações de gravação em lotes grandes ou operações de criação ou manutenção de índice durante o horário de trabalho em sistemas OLTP. Se possível, execute essas operações durante períodos de baixa atividade.

Cenário 6: bloqueio causado por uma conexão órfã

Esse é um cenário de problema comum e se sobrepõe parcialmente ao Cenário 2. Se o aplicativo cliente parar, a estação de trabalho do cliente será reiniciada ou se houver um erro de anulação em lote, todos eles poderão deixar uma transação aberta. Essa situação poderá ocorrer se o aplicativo não reverter a transação nos blocos CATCH ou FINALLY no aplicativo ou se ele não lidar com essa situação de outra forma.

Nesse cenário, embora a execução de um lote SQL tenha sido cancelada, o aplicativo deixa a transação SQL aberta. Da perspectiva da instância do SQL Server, o cliente ainda parece estar presente e todos os bloqueios adquiridos são mantidos.

Para demonstrar uma transação órfã, execute a seguinte consulta, que simula um erro de anulação em lote inserindo dados em uma tabela inexistente:

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

Em seguida, execute essa consulta na mesma janela:

SELECT @@TRANCOUNT;

A saída da segunda consulta indica que o nível de aninhamento de transação é um. Todos os bloqueios adquiridos na transação ainda serão mantidos até que a transação seja confirmada ou revertida. Como o lote já foi anulado pela consulta, o aplicativo que o executa pode continuar executando outras consultas na mesma sessão sem limpar a transação que ainda está aberta. O bloqueio será mantido até que a sessão seja encerrada ou a instância do SQL Server seja reiniciada.

Resoluções:

  • A melhor maneira de evitar essa condição é melhorar o tratamento de erros/exceções do aplicativo, especialmente para encerramentos inesperados. Certifique-se de usar um bloco Try-Catch-Finally no código do aplicativo e reverter a transação no caso de uma exceção.
  • Considere usar SET XACT_ABORT ON para a sessão ou em qualquer procedimento armazenado que inicie transações e não esteja limpando após um erro. No caso de um erro em tempo de execução que aborte o lote, essa configuração anulará todas as transações abertas e retornará o controle ao cliente. Para obter mais informações, examine SET XACT_ABORT (Transact-SQL).
  • Para resolver uma conexão órfã de um aplicativo cliente que foi desconectado sem limpar adequadamente seus recursos, você pode encerrar o SPID usando o comando KILL. Para referência, consulte ENCERRAR (Transact-SQL).

O comando KILL usa o valor SPID como entrada. Por exemplo, para encerrar o SPID 9, emita o seguinte comando:

KILL 99

Observação

O comando KILL pode levar até 30 segundos para ser concluído, devido ao intervalo entre as verificações do comando KILL.

Confira também