Partilhar 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 geralmente é chamado de 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; 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 é 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 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 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 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 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, 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 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 o 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 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

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 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 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 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 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 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 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, 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 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 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 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 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 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 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 qual SQL wait_type 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 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 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 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 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 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, 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 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 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 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 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 pubs, 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 catálogo sys.partitions para associar o hobt_id a um determinado 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 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 a 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 "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 ou cancelamento de consulta.
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 manter bloqueios enquanto o nível de isolamento da transação for padrão (LER CONFIRMADOS), essa é 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 um tempo limite de consulta ou cancelamento ocorreu ou simplesmente uma instrução de reversão foi emitida.
6 NULO >0 hibernando Enfim. Quando o Windows NT determina que a sessão não está mais ativa, a conexão é 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 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.

Resoluçã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, confira 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 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:

  • 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 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, 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 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 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 de 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 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 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 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 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.

Resoluçã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 a vítima do 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.

Resoluçã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 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 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, 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 comando KILL. Para referência, consulte ENCERRAR (Transact-SQL).

O comando KILL usa o valor da 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