Compartilhar via

Entender e resolver problemas de bloqueio do SQL Server

  • Artigo

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 muitos DMVs. Cada um desses SPIDs é frequentemente referido como um processo, embora não seja um contexto de processo separado no sentido usual. Em vez disso, cada SPID consiste nos recursos e estruturas de dados do servidor necessárias para atender às solicitações de uma única conexão de um determinado cliente. Um aplicativo cliente único 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; eles são atômicos. 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 problemas de bloqueio no Banco de Dados SQL do Azure, consulte Entender e resolver problemas de bloqueio do Banco de Dados SQL do Azure.

O que é o bloqueio

O bloqueio é uma característica inevitável e intencional de qualquer RDBMS (sistema de gerenciamento de banco de dados relacional) com simultaneidade baseada em bloqueio. Conforme mencionado, no SQL Server, o bloqueio ocorre quando uma sessão mantém um bloqueio em um recurso específico e um segundo SPID tenta obter um tipo de bloqueio conflitante no mesmo recurso. Normalmente, o intervalo de tempo para o qual o primeiro SPID bloqueia o recurso é pequeno. Quando a sessão proprietária libera o bloqueio, a segunda conexão é liberada 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.

O contexto de duração e de transação de uma consulta determina por quanto tempo seus bloqueios são mantidos e seus efeitos 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 ele estiver realmente sendo lido, não durante a consulta. Para instruções INSERT, UPDATE e DELETE, os bloqueios são mantidos durante a consulta, tanto para a 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, o 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 da transação, consulte os seguintes artigos:

Quando o bloqueio persiste 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 sozinho 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 sozinho e impede o acesso aos recursos afetados indefinidamente.

No primeiro cenário, a situação pode ser muito fluida, pois os SPIDs diferentes causam o bloqueio em recursos diferentes 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 em problemas de plataforma e de ajuste no lado do servidor ao enfrentar um problema de bloqueio. No entanto, dar atenção apenas 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 feitas, um problema de bloqueio, no entanto, frequentemente exige 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ão, à 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 SQL Server, especialmente para ambientes OLTP sensíveis ao desempenho.

Fique atento ao desempenho do banco de dados durante a fase de design e construção do banco de dados e do aplicativo. Especialmente, 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 leves possíveis. Uma boa disciplina de gerenciamento de conexão deve ser exercida, sem isso, o aplicativo pode parecer ter um desempenho aceitável com um número baixo de usuários, mas o desempenho pode diminuir significativamente à medida que o número de usuários aumenta.

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

Solucionar problemas de bloqueio

Independente 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 determinará o restante da composição deste artigo. O conceito é localizar o bloqueador de cabeçalho e identificar o que a consulta está fazendo e por que está bloqueando. Depois que a consulta problemática é identificada (ou seja, o que está mantendo os bloqueios por um período prolongado), a próxima etapa é analisar e determinar por que o bloqueio está acontecendo. Depois de entendermos o porquê, podemos fazer alterações redesenhando 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 os bloqueios por um longo período)

  3. Analisar/entender por que o bloqueio prolongado ocorre

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

Agora, vamos discutir melhor sobre como identificar a principal sessão de bloqueio com uma captura de dados apropriada.

Coletar informações de bloqueio

Para neutralizar a dificuldade de solucionar problemas de bloqueio, um administrador de banco de dados pode usar scripts SQL que monitoram constantemente o estado de bloqueio e bloqueio no SQL Server. Para coletar esses dados, existem dois métodos complementares.

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

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

Coletar informações das DMVs

Referenciar DMVs para solucionar problemas de bloqueio tem o objetivo de identificar a SPID (ID de sessão) no início da cadeia de bloqueios e na instrução do SQL. Procure as SPIDs de vítima que estão sendo bloqueadas. Se uma SPID estiver sendo bloqueada por outra SPID, investigue a SPID que possui o recurso (a SPID de bloqueio). O proprietário da SPID também está sendo bloqueado? Você pode percorrer a cadeia para localizar o bloqueador de cabeçalho e investigar por que ele está mantendo seu bloqueio.

Para fazer isso, você pode usar um dos seguintes métodos:

  • No Pesquisador de Objetos do 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 selecione Atividade – Todas as Transações de Bloqueio. Este relatório mostra as transações atuais no início de uma cadeia de bloqueio. Se você expandir a transação, o relatório mostrará as transações bloqueadas pela transação principal. Este relatório também mostrará a Instrução SQL de Bloqueio 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 Atividade aqui.

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

  • Os sp_who comandos and 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 que é mais fácil de ser consultado e filtrado. Você encontrará sys.dm_exec_sessions no centro de outras consultas.

  • Se já tiver uma sessão específica identificada, você 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 que é mais fácil de ser consultado e filtrado, fornecendo a session_id e a request_id. Por exemplo, para retornar a consulta mais recente enviada por session_id 66 e request_id 0:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • Consulte a sys.dm_exec_requests coluna e faça referência à blocking_session_id coluna. Quando blocking_session_id = 0, uma sessão não está sendo bloqueada. Embora sys.dm_exec_requests apenas as solicitações em execução no momento, toda conexão (ativa ou não) será listada em sys.dm_exec_sessions. Estenda esta 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 o SQL Server.

  • Execute esta consulta de exemplo para localizar as consultas em execução ativamente e o texto do lote SQL atual 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 text pela coluna de sys.dm_exec_sql_text for NULL, a consulta não estará em execução no momento. Nesse caso, a event_info coluna de conterá a última string de sys.dm_exec_input_buffer comando passada para o mecanismo SQL. Essa consulta também pode ser usada para identificar sessões que bloqueiam outras sessões, incluindo uma lista de session_ids bloqueadas 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 elaborada, fornecida pelo Suporte da Microsoft, para identificar o cabeçalho de uma cadeia de bloqueio de sessão múltipla, incluindo o texto de 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 o SQL wait_type que a solicitação está enfrentando no momento. Assim como sys.dm_exec_requests, apenas as solicitações ativas são retornadas por sys.dm_os_waiting_tasks.

Observação

Para obter muito mais sobre os 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 produção do SQL Server e é útil para diagnosticar quais bloqueios são mantidos no momento.

Devido à INNER JOIN em sys.dm_os_waiting_tasks, a seguinte consulta restringe a saída de sys.dm_tran_locks somente para as solicitações atualmente bloqueadas, o status de espera e os bloqueios delas:

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ê revise o bloqueio em um intervalo de tempo especificado para identificar as tendências ou o bloqueio persistente. A ferramenta de 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 de DMVs mencionados acima, ao longo do tempo. Como essa ferramenta está em constante evolução, revise 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 minuciosamente um problema de bloqueio no SQL Server. Por exemplo, se uma sessão executar várias instruções em uma transação, somente a última instrução que foi 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 SQL Server Profiler foram preteridos. O XEvents é a plataforma de rastreamento mais nova e superior que permite mais versatilidade e menos impacto ao sistema observado, e sua interface é integrada ao SSMS.

Há sessões de Eventos Estendidos pré-criadas prontas para serem iniciadas no SSMS, listadas no 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 personalizadas de Eventos Estendidos 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 de canal)
    • Exchange_spill
    • Execution_warning

**Para configurar o limite e a frequência com que os relatórios de processo bloqueado 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, não são produzidos relatórios de processo bloqueado.

  • Avisos de Categoria:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning

  • Execução da Categoria:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Bloqueio de categoria

    • Lock_deadlock

  • Sessão de categoria

    • Existing_connection
    • Logon
    • Logout

Identificar e resolver cenários de bloqueio comuns

Ao examinar as informações acima, você pode determinar a causa da maioria dos problemas de bloqueio. O restante deste artigo é uma discussão de 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 tenha 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 determinar os cabeçalhos das cadeias de bloqueio usando blocking_these e session_id. Isso identificará de forma mais clara quais solicitações são bloqueadas e quais estão bloqueando. Entenda mais sobre as sessões bloqueadas e bloqueando. Existe uma raiz ou comum para a cadeia de bloqueio? Elas provavelmente compartilham uma tabela comum e uma ou mais 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 as 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 a 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 a SPID está processando uma consulta no momento. A tabela a seguir fornece breves explicações dos vários valores de status.

      Status Significado
      Segundo plano A SPID está executando uma tarefa em segundo plano, como detecção de deadlock, gravador de log ou ponto de verificação.
      Hibernando A SPID não está em execução no momento. Isso geralmente indica que a SPID está aguardando um comando do aplicativo.
      Executando A SPID está sendo executado no momento em um agendador.
      Executável A SPID está na fila executável de um agendador e aguardando para obter o tempo do agendador.
      Suspenso A 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 do que 0, a SPID estará dentro de uma transação aberta e poderá estar mantendo os bloqueios adquiridos por uma instrução dentro da transação. A transação aberta pode ter sido criada por uma instrução ativa no momento ou por uma solicitação de declaração que foi executada no passado e não está mais ativa.

    • sys.dm_exec_requests.open_transaction_count

      Da mesma forma, essa coluna informa o número de transações abertas nessa 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 ativa dentro da transação. Ao contrário sys.dm_exec_sessions.open_transaction_countde , se não houver uma solicitação ativa, esta coluna mostrará 0.

    • sys.dm_exec_requests.wait_type, wait_time e last_wait_type

      Se o sys.dm_exec_requests.wait_type for NULL, a solicitação não está aguardando nada e o valor last_wait_type indica o último wait_type que a solicitação encontrou. Para obter mais informações sobre osys.dm_os_wait_stats e uma descrição dos tipos de espera mais comuns, consulte sys.dm_os_wait_stats. O valorwait_time pode ser usado para determinar se a solicitação está progredindo. Quando uma consulta na tabelasys.dm_exec_requestsdevolve um valor na colunawait_time que é menor do que o valor wait_time de uma consulta anterior desys.dm_exec_requests, isso indica que o bloqueio anterior foi adquirido e liberado e agora está aguardando um novo bloqueio (supondo que o wait_time seja diferente de zero). Isso pode ser verificado por meio da comparação de wait_resource entre a saídasys.dm_exec_requests, que exibe o recurso que a solicitação está aguardando.

    • sys.dm_exec_requests.wait_resource

      Essa coluna indica o recurso que uma solicitação bloqueada está aguardando. A tabela a seguir lista formatos comunswait_resource e seus significados:

      Recurso Formato Exemplo Explicação
      Tabela DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 Nesse caso, a ID do banco de dados 5 é o banco de dados de exemplo de pubs e object_id 261575970 é a tabela de títulos e 1 é o índice clusterizado.
      Página DatabaseID:FileID:PageID PAGE: 5:1:104 Nesse caso, a ID do banco de dados 5 é pubs, a ID do arquivo de dados primário 1 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 o DatabaseID, o FileID, o PageId do wait_resource.
      Chave DatabaseID: Hobt_id (valor de hash para chave de índice) KEY: 5:72057594044284928 (3300a4f361aa) Nesse caso, a ID de 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 sys.partitions catálogo para associar o hobt_id a um arquivo específico index_id e object_id. Não é possível remover o hash do hash de 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 de banco de dados 5 é pubs, a ID do arquivo de dados primário 1, 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 de banco de dados 5 é pubs, a ID do arquivo de dados primário 1, 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.

    • A sys.dm_tran_active_transactionsDMV 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 estão aguardando confirmação ou reversão. Use a consulta a seguir para devolver informações sobre transações abertas, unidas a 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 apenas de determinados clientes (hostname), em determinadas bibliotecas de rede (client_interface_name), quando o último lote enviado por um SPID estava last_request_start_time em sys.dm_exec_sessions, há quanto tempo uma solicitação estava em execução usando start_time em sys.dm_exec_requestse assim por diante.

Cenários comuns de bloqueio

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

As colunas wait_type, open_transaction_count e status referem-se às informações devolvidas por sys.dm_exec_request, outras colunas podem ser devolvidas por sys.dm_exec_sessions. A coluna "Isso resolve?" indica se o bloqueio será resolvido ou não resolvido sozinho ou se a sessão deve ser eliminada por meio do comando KILL. Para obter mais informações, confira KILL (Transact-SQL).

Cenário Wait_type Open_Tran Status Resolvido? Outros sintomas
1 NOT NULL >= 0 executável Sim, quando a consulta é concluída. Em sys.dm_exec_sessions, as colunas reads, cpu_time e/ou memory_usage aumentarão com o tempo. A duração da consulta será alta quando for concluída.
2 NULO >0 hibernando Não, mas a SPID pode ser eliminada. Um sinal de atenção pode ser visto na sessão de Evento Estendido para esse SPID, indicando que ocorreu um tempo limite de consulta ou cancelamento.
3 NULO >= 0 executável Não. Não será resolvido até que o cliente busque todas as linhas ou feche a conexão. A SPID pode ser eliminada, mas isso pode levar até 30 segundos. Se open_transaction_count = 0 e o SPID mantiver bloqueios enquanto o nível de isolamento da transação for padrão (READ COMMITTED), essa será uma causa provável.
4 Varia >= 0 executável Não. Não será resolvido até que o cliente cancele consultas ou feche conexões. As SPIDs podem ser eliminadas, mas pode levar até 30 segundos. A coluna hostname em sys.dm_exec_sessions para a SPID no cabeçalho de uma cadeia de bloqueio será a mesma que uma das SPIDs que ela está bloqueando.
5 NULO >0 reversão Sim. Um sinal de atenção pode ser visto na sessão de Eventos Estendidos para esse SPID, indicando que ocorreu um tempo limite ou cancelamento de consulta ou simplesmente uma instrução de reversão foi emitida.
6 NULO >0 hibernando Enfim. Quando o Windows NT determinar que a sessão não está mais ativa, a conexão será interrompida. O valor de 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 em execução normal com um tempo de execução longo

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

Resolução:

A solução para esse problema de bloqueio é procurar maneiras de otimizar a consulta. Essa classe de problema de bloqueio pode ser um problema de desempenho e exige que você a trate 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, confira Monitorar e ajustar o desempenho.

Os relatórios internos do 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 caras e os planos de execução abaixo do ideal.

Se você tiver uma consulta de longa duração bloqueando outros usuários e ela não puder 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 consultas, um cenário em que bloqueios de linha ou página são escalonados para bloqueios de tabela. O Microsoft SQL Server determina dinamicamente quando executar o escalonamento de bloqueios. A maneira mais simples e segura de evitar o escalonamento de bloqueios é manter as transações curtas e reduzir o volume de bloqueios de consultas caras para que os limites de escalonamento de bloqueios 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á dormindo ou aguardando um comando com um 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 ROLLBACK e/ou COMMIT. Quando um SPID recebe um tempo limite de consulta ou um cancelamento, ele encerra a consulta e o lote atuais, mas não reverte ou confirma automaticamente a transação. O aplicativo é responsável por isso, pois o SQL Server não pode presumir que uma transação inteira deve ser revertida devido ao cancelamento de uma única consulta. O tempo limite ou cancelamento da consulta aparecerá como um evento de sinal ATTENTION 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 a contagem de transações é um. Todos os bloqueios adquiridos na transação ainda são mantidos até que a transação seja confirmada ou revertida. Se os aplicativos abrirem e confirmarem explicitamente as transações, uma comunicação ou outro erro poderá deixar a sessão e sua transação em um estado aberto.

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

Resoluções:

  • Essa classe de problema de bloqueio também pode ser um problema de desempenho e exige que você a trate como tal. Se o tempo de execução da consulta puder ser diminuído, o tempo limite ou o cancelamento da consulta poderão não ocorrer. É importante que o aplicativo possa lidar com os cenários de tempo limite ou cancelamento caso eles surjam, mas você também pode se beneficiar do exame 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 erros do aplicativo cliente, execute IF @@TRANCOUNT > 0 ROLLBACK TRAN após um erro, mesmo que o aplicativo cliente não acredite que uma transação está aberta. A verificação de transações abertas é necessária porque 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, liberar a conexão causará uma desconexão física da conexão do SQL Server, resultando na reversão do servidor de todas 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 de tempo de execução, essa configuração anulará todas as transações abertas e devolverá o controle para o cliente. Para obter mais informações, revise 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 abra uma transação e, em seguida, libere a conexão com o pool de conexões, mas ela pode não ser reutilizada por vários segundos, durante os quais a transação permaneceria aberta. Se a conexão não for reutilizada, a transação será anulada quando a conexão atingir o tempo limite 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

Em seguida 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 de código existente.

Cenário 3: Bloqueio causado por um SPID cujo aplicativo cliente correspondente não buscou 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 para 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 de forma transparente instruções SQL para o servidor, o aplicativo deve buscar todas as linhas de resultado. Se isso não acontecer (e se não puder ser configurado para isso), talvez você não consiga resolver o problema de bloqueio. Para evitar o problema, você pode restringir o mau desempenho de aplicativos a um relatório ou a um banco de dados de suporte a decisões, separado do banco de dados OLTP principal.

Resolução:

O aplicativo deve ser reescrito para buscar todas as linhas do resultado até a conclusão. Isso não exclui 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 cliente/servidor distribuído

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

Exemplo A: Deadlock distribuído 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 seguinte deadlock distribuído poderá ocorrer. Observe que o termo dbproc usado aqui se refere à 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 em dbproc1. Isso significa que ele não espera a chamada retornar antes de continuar. Em seguida, o aplicativo envia outra operação SQL em dbproc2 e aguarda os resultados para iniciar o processamento dos dados retornados. Quando os dados começam a voltar (o que o dbproc responde primeiro - suponha que seja dbproc1), ele processa até a conclusão 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 um bloqueio, mas tenta enviar dados para seu cliente, dbproc2. No entanto, dbproc2 é efetivamente bloqueado em dbproc1 na camada de aplicativo, pois o thread único de execução para o aplicativo está em uso por dbproc1. 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.

Exemplo B: Deadlock distribuído 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 pode 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 SELECT instrução 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 INSERTinstrução , UPDATE, or DELETE na mesma tabela. Eventualmente, o SPID1 (executando o INSERT, , ou DELETE) fica bloqueado em um bloqueio mantido pelo SPID2 (executando o SELECTUPDATE). O SPID2 grava uma linha de resultado no cliente dbproc2. Em seguida, o dbproc2 tenta passar a linha em um buffer para dbproc1, mas descobre que dbproc1 está ocupado (está bloqueado aguardando o SPID1 concluir o atual INSERT, que está bloqueado no SPID2). Neste ponto, dbproc2 é bloqueado na camada de aplicativo por dbproc1 cujo SPID (SPID1) é 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 exemplos A e B são questões fundamentais das quais os desenvolvedores de aplicativos devem estar cientes. Eles devem codificar aplicativos para lidar com esses casos adequadamente.

Resolução:

Quando um tempo limite de consulta for fornecido, se ocorrer o deadlock distribuído, 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 é eliminada 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 a vítima do deadlock. Isso geralmente pode ser identificado observando a saída de sys.dm_exec_requests, que pode indicar o ROLLBACK command, e a percent_complete coluna pode mostrar progresso.

Uma consulta de modificação de dados que é eliminada 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 de sua sessão de rede. Da mesma forma, uma consulta selecionada como 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 foram aplicadas inicialmente. Por exemplo, se uma DELETEinstrução , INSERT, ou UPDATE estiver em execução por uma hora, pode 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 dourado ou de reversão (o que significa que ele não pode ser morto 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 ROLLBACK. A status coluna de sys.dm_exec_sessions indicará um status ROLLBACK.

Observação

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

Resolução:

Você deve aguardar a conclusão da reversão das alterações feitas pela sessão.

Se a instância for desligada no meio dessa operação, o banco de dados estará no modo de recuperação ao reiniciar 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 Acelerada de Banco de Dados habilitada, isso não deve ocorrer.

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

Cenário 6: Bloqueio causado por uma transação órfã

Este é 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 for reiniciada ou houver um erro de anulação em lote, tudo isso poderá deixar uma transação aberta. Essa situação pode ocorrer se o aplicativo não reverter a transação nos blocos ou FINALLY do aplicativo CATCH ou se não lidar com essa situação.

Nesse cenário, enquanto a execução de um lote SQL foi 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 a contagem de transações é um. Todos os bloqueios adquiridos na transação ainda sã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 é melhorando o tratamento de erros/exceções do aplicativo, especialmente para encerramentos inesperados. Certifique-se de usar um Try-Catch-Finally bloco 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 de tempo de execução que anula o lote, essa configuração reverterá automaticamente todas as transações abertas e retornará o controle ao cliente. Para obter mais informações, revise 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 KILL comando. Para referência, consulte KILL (Transact-SQL).

O comando KILL usa o valor da SPID como entrada. Por exemplo, para matar o SPID 9, execute o seguinte comando:

KILL 99

Observação

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

Confira também