Compartilhar via

Entender e resolver problemas de bloqueios

Aplica-se a:Banco de Dados SQL do AzureBanco de Dados SQL no Fabric

O artigo descreve o bloqueio no Banco de Dados SQL do Azure e no Banco de Dados SQL do Fabric, e demonstra como solucionar problemas e resolver o bloqueio.

Objetivo

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 ou session_id em muitos DMVs. Cada uma dessas IDs de sessão geralmente é conhecida como um processo, embora não seja um contexto de processo separado no sentido usual. Em vez disso, cada ID de sessão consiste nos recursos do servidor e nas estruturas de dados 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 banco de dados SQL do Azure, não há nenhuma diferença entre várias conexões de um aplicativo cliente único em um computador cliente único 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.

Para obter informações sobre como solucionar problemas de deadlocks, consulte Analisar e impedir deadlocks no Banco de Dados SQL do Azure e no Banco de Dados SQL do Fabric.

Observação

Este conteúdo está concentrado no banco de dados SQL do Azure. O banco de dados SQL do Azure é baseado na versão estável mais recente do mecanismo de banco de dados do Microsoft SQL Server, portanto, grande parte do conteúdo é semelhante, embora as opções e as ferramentas de solução de problemas possam ser diferentes. Para obter mais informações sobre o bloqueio no SQL Server, consulte Entender e resolver problemas de bloqueio do SQL Server. O Banco de Dados SQL do Fabric compartilha muitos recursos com o Banco de Dados SQL do Azure. Para obter mais informações sobre monitoramento de desempenho, consulte Monitorar banco de dados SQL no Microsoft Fabric.

Entender 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. O bloqueio em um banco de dados SQL do Azure ocorre quando uma sessão mantém um bloqueio em um recurso específico e uma segunda ID de sessão tenta obter um tipo de bloqueio conflitante no mesmo recurso. Em geral, o intervalo de tempo em que a primeira ID de sessão 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. Esse comportamento é normal e pode acontecer muitas vezes ao longo de um dia sem nenhum efeito perceptível no desempenho do sistema.

Cada novo banco de dados no Banco de Dados SQL do Azure tem a configuração RCSI (instantâneo de leitura confirmada) do banco de dados habilitada por padrão. O bloqueio entre sessões que leem dados e sessões que gravam dados é minimizado com o RCSI, que usa o controle de versão de linha para aumentar a simultaneidade. No entanto, bloqueio e deadlocks ainda podem ocorrer em bancos de dados no Banco de Dados SQL do Azure porque:

  • Consultas que modificam dados podem bloquear umas às outras.
  • Consultas podem ser executadas em níveis de isolamento que aumentam o bloqueio. Os níveis de isolamento podem ser especificados em cadeias de conexão de aplicativos, dicas de consulta ou instruções SET em Transact-SQL.
  • O RCSI pode estar desabilitado, fazendo com que o banco de dados use bloqueios compartilhados (S) para proteger as instruções SELECT executadas no nível de isolamento de leitura confirmada. Isso pode aumentar bloqueios e deadlocks.

O nível de isolamento de instantâneo também é habilitado por padrão em novos bancos de dados no Banco de Dados SQL do Azure. O isolamento de instantâneo é um nível de isolamento adicional baseado em linha que fornece consistência no nível de transação para os dados e que usa versões de linha para selecionar linhas para atualizar. Para usar o isolamento de instantâneo, as consultas ou conexões devem definir explicitamente o nível de isolamento da transação como SNAPSHOT. Isso só pode ser feito quando o isolamento de instantâneo está habilitado no banco de dados.

Você pode identificar se o isolamento de instantâneo e/ou RCSI está habilitado com o Transact-SQL. Conecte-se ao banco de dados no Banco de Dados SQL do Azure e execute a seguinte consulta:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Se o RCSI estiver habilitado, a coluna is_read_committed_snapshot_on retornará o valor 1. Se o isolamento de instantâneo estiver habilitado, a coluna snapshot_isolation_state_desc retornará o valor ATIVADO.

A duração e o contexto de transação de uma consulta determinam por quanto tempo seus bloqueios são mantidos e seu efeito em outras consultas. Instruções SELECT executadas no RCSI não adquirem bloqueios compartilhados (S) nos dados que estão sendo lidos, portanto, não bloqueiam transações que estão modificando dados. 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 explícita, o tipo dos bloqueios e a duração na 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:

  • Uma ID de sessão 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.

  • Uma ID de sessão 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 dinâmica, pois diferentes IDs de sessão causam bloqueio em diferentes recursos ao longo do tempo, criando um alvo 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.

Bloqueio otimizado

O bloqueio otimizado é um novo recurso do Mecanismo de Banco de Dados que reduz drasticamente a memória e o número de bloqueios necessários simultaneamente para gravações. O bloqueio otimizado usa dois componentes primários: bloqueio de TID (ID de transação) (também usado em outros recursos de controle de versão de linha) e LAQ (bloqueio após qualificação). Ele não requer nenhuma configuração extra.

Este artigo se aplica atualmente ao comportamento do Mecanismo de Banco de Dados sem bloqueio otimizado.

Para obter mais informações e saber onde o bloqueio otimizado está disponível, confira Bloqueio otimizado.

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, prestar 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. Não importa o nível de visibilidade que o aplicativo expõe em relação às chamadas de banco de dados que estão sendo feitas, um problema de bloqueio frequentemente requer a inspeção das instruções SQL exatas enviadas pelo aplicativo e o comportamento exato do aplicativo em relação ao cancelamento de consulta, ao gerenciamento de conexões, à busca de todas as linhas de resultados e assim por diante. Se a ferramenta de desenvolvimento não permitir o controle explícito sobre o gerenciamento de conexão, o cancelamento de consulta, o tempo limite de consulta, a busca de resultados e assim por diante, os problemas de bloqueio podem não ser resolvidos. Esse potencial deve ser examinado de forma minuciosa antes de selecionar uma ferramenta de desenvolvimento de aplicativos para o banco de dados SQL do Azure, 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ões deve ser exercida. Sem ele, o aplicativo pode parecer ter um desempenho aceitável em 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 banco de dados SQL do Azure é capaz de dar suporte a milhares de usuários simultâneos em um único servidor, com pouco bloqueio.

Observação

Para obter mais diretrizes de desenvolvimento de aplicativos, consulte Solucionar problemas de conectividade e outros erros e Tratamento de Falhas Transitórias.

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 determina 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. Após identificar a consulta problemática (ou seja, o que está mantendo bloqueios pelo período prolongado), a próxima etapa será analisar e determinar por que está ocorrendo o bloqueio. Depois de entendermos os motivos, 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 anular a dificuldade de solucionar problemas de bloqueio, um administrador de banco de dados pode usar scripts SQL que monitoram constantemente o estado de bloqueio no banco de dados do Banco de Dados SQL do Azure. Para coletar esses dados, existem basicamente dois métodos.

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). O segundo método é usar XEvents para capturar o que está em execução.

Coletar informações das DMVs

Fazer referência a DMVs para solucionar problemas de bloqueio tem a meta de identificar a ID da sessão à frente da cadeia de bloqueio e da Instrução SQL. Procure as IDs de sessão da vítima que estão sendo bloqueadas. Se alguma ID de sessão estiver sendo bloqueada por outra ID de sessão, investigue a ID da sessão que possui o recurso (a ID da sessão de bloqueio). Esse ID de sessão do proprietário 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.

Não deixe de executar cada um desses scripts no banco de dados do Banco de Dados SQL do Azure de destino.

  • 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ê pode encontrar sys.dm_exec_sessions no núcleo 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);

  • Veja a coluna blocking_session_id em sys.dm_exec_requests. 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) é 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.

  • 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 pelo campo text de sys.dm_exec_sql_text forem NULL, a consulta não está sendo executada. Nesse caso, o campo event_info de sys.dm_exec_input_buffer contém a última cadeia de caracteres de comando transmitida para o mecanismo do 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];
  • sys.dm_os_waiting_tasks de referência que está na camada de thread/tarefa do SQL. Isso devolve informações sobre qual tipo de espera SQL 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. Essa DMV devolve estatísticas de espera de agregada somente dos banco de dados atual.

  • Use a DMV sys.dm_tran_locks para obter informações mais granulares sobre quais bloqueios foram colocados por consultas. Essa DMV pode devolver grandes volumes de dados em um banco de dados de produção e é útil para diagnosticar quais bloqueios estão sendo 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 fornece pontos de dados que permitem que você examine o bloqueio em um intervalo de tempo especificado para identificar o bloqueio persistente ou as tendências.

Coletar informações de Eventos Estendidos

Além das informações anteriores, geralmente é necessário capturar um rastreamento das atividades no servidor para investigar minuciosamente um problema de bloqueio no banco de dados SQL do Azure. Por exemplo, se uma sessão executar várias instruções em uma transação, somente a última instrução enviada será representada. No entanto, uma das instruções anteriores pode ser o motivo pelo qual os bloqueios ainda estão sendo mantidos. Um rastreamento permite 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 Profiler. No entanto, o SQL Server Profiler é uma tecnologia de rastreamento preterida sem suporte no banco de dados SQL do Azure. Eventos Estendidos é a tecnologia de rastreamento mais recente que permite mais versatilidade e menos efeito no sistema observado e sua interface é integrada ao SSMS (SQL Server Management Studio).

Consulte o documento que explica como usar o Assistente de Nova Sessão de Eventos Estendidos no SSMS. No entanto, para os bancos de dados SQL do Azure, o SSMS fornece uma subpasta Eventos Estendidos em cada banco de dados no Pesquisador de Objetos. Use um assistente de sessão de Eventos Estendidos para capturar esses eventos úteis:

  • Erros de categoria:

    • Attention
    • Error_reported
    • Execution_warning

  • Avisos de Categoria:

    • Missing_join_predicate

  • Execução da Categoria:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Categoria deadlock_monitor

    • database_xml_deadlock_report

  • Sessão de categoria

    • Existing_connection
    • Logon
    • Logout

Observação

Para obter informações detalhadas sobre deadlocks, confira Analisar e evitar deadlocks no Banco de Dados SQL do Azure e banco de dados SQL do Fabric.

Identificar e resolver cenários de bloqueio comuns

Ao revisar as informações anteriores, 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. Essa discussão pressupõe que você usou os scripts de bloqueio (referenciados anteriormente) para capturar informações sobre as IDs de sessão de bloqueio e capturou 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 identifica com mais clareza 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.

  • Verifique a saída dos DMVs sys.dm_exec_requests e sys.dm_exec_sessions para obter informações sobre as IDs de sessão à cabeça da cadeia de bloqueio. Procure os campos a seguir:

    • sys.dm_exec_requests.status
      Esta coluna mostra o status de uma solicitação específica. Geralmente, um status de espera indica que o ID da sessão concluiu a execução e está aguardando o aplicativo enviar outra consulta ou lote. Um status pronto para execução ou em execução indica que a ID de sessão 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 ID da sessão está executando uma tarefa em segundo plano, como detecção de deadlock, gravador de log ou ponto de verificação.
    Hibernando A ID da sessão não está sendo executada no momento. Isso geralmente indica que a ID da sessão está aguardando um comando do aplicativo.
    Executando Atualmente, a ID da sessão está em execução em um agendador.
    Executável A ID da sessão está na fila executável de um agendador e aguardando para obter o tempo do agendador.
    Suspenso A ID da sessão está aguardando um recurso, como um bloqueio ou uma trava.

    • sys.dm_exec_sessions.open_transaction_count
      Este campo informa o número de transações abertas nesta sessão. Se esse valor for maior que 0, a ID de sessão faz parte de uma transação aberta e poderá manter bloqueios adquiridos por qualquer instrução dentro da transação.

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

    • sys.dm_exec_requests.wait_type, wait_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 tabela sys.dm_exec_requests devolve um valor na coluna wait_time que é menor do que o valor wait_time de uma consulta anterior de sys.dm_exec_requests, isso indica que o bloqueio anterior foi adquirido e liberado e agora está aguardando um novo bloqueio (supondo 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 Esse campo indica o recurso em 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 e a ID do objeto 261575970 é a tabela de títulos e 1 é o índice clusterizado.
    ? DatabaseID:FileID:PageID PÁGINA: 5:1:104 Nesse caso, a ID do banco de dados 5 is pubs, a ID do arquivo 1 é o arquivo de dados primário e a página 104 é uma página que pertence à tabela de títulos. Para identificar o object_id à qual a página pertence, use a função de gerenciamento dinâmico sys.dm_db_page_info, passando o DatabaseID, FileId, PageId do wait_resource.
    Chave DatabaseID: Hobt_id (valor de hash para chave de índice) CHAVE: 5:72057594044284928 (3300a4f361aa) Nesse caso, a ID 5 do banco de dados é pubse Hobt_ID 72057594044284928 corresponde a index_id 2 para object_id 261575970 (tabela de títulos). Use a exibição do catálogo sys.partitions para associar o hobt_id a um index_id e object_idespecíficos. 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 do banco de dados 5 é pubs, a ID do arquivo 1 é o arquivo de dados primário, a página 104 é uma página que pertence à tabela de títulos e o slot 3 indica a posição da linha na página.
    Compilar DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 Nesse caso, a ID do banco de dados 5 é pubs, a ID do arquivo 1 é o arquivo de dados primário, a página 104 é uma página que pertence à tabela de títulos e o slot 3 indica a posição da linha na página.
    • 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
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, 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 identificar se o problema ocorre apenas de determinados clientes (nome do host), em determinadas bibliotecas de rede (net_library), quando o último lote enviado por uma ID de sessão estava last_request_start_time em sys.dm_exec_sessions, por quanto tempo uma solicitação estava sendo executada usando 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 Waittype, Open_Trane Status referem-se às informações retornadas por sys.dm_exec_request. Outras colunas podem ser retornadas por sys.dm_exec_sessions. A coluna "Resolve?" indica se o bloqueio é resolvido por conta própria ou se a sessão deve ser encerrada por meio do comando KILL. Para obter mais informações, consulte KILL.

Cenário Waittype Open_Tran Status Resolvido? Outros sintomas
1 NÃO NULO >= 0 executável Sim, quando a consulta é concluída. Em sys.dm_exec_sessions, as colunas reads, cpu_timee/ou memory_usage aumentam ao longo do tempo. A duração da consulta é alta quando concluída.
2 NULO >0 hibernando Não, mas a ID da sessão pode ser morta. Um sinal de atenção pode ser visto na sessão de Eventos Estendidos para este ID da sessão, indicando que ocorreu um tempo limite de consulta ou cancelamento.
3 NULO >= 0 executável Não. Não é resolvido até que o cliente busque todas as linhas ou feche a conexão. A ID da sessão pode ser morta, mas pode levar até 30 segundos. Se open_transaction_count = 0 e a ID da sessão 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 é resolvido até que o cliente cancele consultas ou feche conexões. IDs de sessão podem ser encerrados, mas isso pode levar até 30 segundos. A hostname coluna na sys.dm_exec_sessions ID da sessão à frente de uma cadeia de bloqueio é a mesma da ID da sessão que está bloqueando.
5 NULO >0 reversão Sim. Um sinal de atenção pode ser visto na sessão de Eventos Estendidos para este ID de sessão, indicando que ocorreu um tempo limite ou um cancelamento de consulta, ou que uma instrução de reversão foi simplesmente emitida.
6 NULO >0 hibernando Enfim. Quando o Windows determina que a sessão não está mais ativa, a conexão do Banco de Dados SQL do Azure é interrompida. O valor de last_request_start_time em sys.dm_exec_sessions é muito anterior à hora atual.

Cenários de bloqueio detalhados

  1. Bloqueio causado por uma consulta em execução normalmente com um tempo de execução prolongado

    Resolução: a solução para esse tipo de problema de bloqueio é procurar maneiras de otimizar a consulta. Na verdade, essa classe de problema de bloqueio pode ser apenas um problema de desempenho e exige que você o busque dessa forma. 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 do Repositório de Consultas no SSMS também são uma ferramenta valiosa e altamente recomendada para identificar as consultas mais caras e planos de execução de qualidade inferior. Analise também Análise de Desempenho de Consultas.

    Se a consulta executar apenas operações SELECT, considere executar a instrução em isolamento de instantâneo se isso estiver habilitado em seu banco de dados, especialmente se o RCSI tiver sido desabilitado. Como ocorre quando o RCSI está habilitado, as consultas que leem dados não exigem bloqueios compartilhados (S) no nível de isolamento do instantâneo. Além disso, o isolamento de instantâneo fornece consistência no nível da transação para todas as instruções em uma transação explícita de várias instruções. O isolamento de instantâneo poderia já estar habilitado em seu banco de dados. O isolamento de instantâneo também poderia ser usado com consultas que executam modificações, mas você deve lidar com conflitos de atualização.

    Se você tem uma consulta de execução prolongada 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, uma réplica somente leitura síncrona do banco de dados.

  2. Bloqueio causado por um ID de sessão suspenso que tem uma transação não confirmada

    Esse tipo de bloqueio geralmente pode ser identificado por uma ID da sessão que está inativa 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. Isso pode ocorrer se o aplicativo apresentar um tempo limite de consulta ou emitir um cancelamento sem emitir também o número necessário de instruções ROLLBACK e/ou COMMIT. Quando uma ID de sessão recebe um tempo limite de consulta ou um cancelamento, ela 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 Banco de Dados SQL do Azure não pode supor que uma transação inteira precisa ser revertida porque apenas uma consulta está sendo cancelada. O tempo limite ou o cancelamento da consulta é exibido como um evento de sinal ATTENTION para a ID da sessão 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;
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 da 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 também pode ser um problema de desempenho e exige que você o busque dessa forma. Se o tempo de execução da consulta puder ser diminuído, o tempo limite da consulta ou cancelamento não ocorrerá. É importante que o aplicativo seja capaz de lidar com os cenários de tempo limite ou cancelamento caso eles ocorram, mas você também pode se beneficiar com a 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. Considerar:

      • 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 execute 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 do Banco de Dados SQL do Azure, fazendo com que o servidor reverta todas as transações abertas.
      • Use SET XACT_ABORT ON para a conexão ou nos procedimentos armazenados que iniciam transações e não estão 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, analise SET XACT_ABORT.

    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 para 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 as 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, instruções T-SQL após uma instrução que causa um erro não são executadas. Isso pode afetar o fluxo pretendido de código existente.

  3. Bloqueio causado por um ID de sessão cujo aplicativo cliente correspondente não buscou todas as linhas de resultado até o final

    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 não estiver (e se 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.

    O impacto desse cenário é reduzido quando o instantâneo de leitura confirmada é habilitado no banco de dados, que é a configuração padrão no Banco de Dados SQL do Azure. Saiba mais na seção Entender o bloqueio deste artigo.

    Observação

    Consulte as diretrizes para lógica de repetição para aplicativos que se conectam ao Banco de Dados SQL do Azure.

    Resolução: o aplicativo deve ser reescrito para buscar todas as linhas do resultado para 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.

  4. Bloqueio causado por uma sessão em um estado de reversão

    Uma consulta de modificação de dados que é terminada ou cancelada fora de uma transação definida pelo usuário é 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. Geralmente, isso pode ser identificado observando a saída de sys.dm_exec_requests, o que pode indicar o comando ROLLBACK, e a coluna percent_complete pode mostrar o progresso.

    Graças à Recuperação acelerada de banco de dados introduzida em 2019, reversões longas devem ser raras.

    Resolução: aguarde até que a ID da sessão termine de reverter as alterações que foram feitas.

    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.

  5. Bloqueio causado por uma conexão órfã

    Se o aplicativo cliente interceptar erros ou a estação de trabalho cliente for reiniciada, a sessão de rede para o servidor poderá não ser cancelada imediatamente em algumas condições. Do ponto de vista do Banco de Dados SQL do Azure, o cliente ainda parece estar presente e todos os bloqueios adquiridos ainda podem ser retidos. Para obter mais informações, consulte Como solucionar problemas de conexões órfãs no SQL Server.

    Resolução: se o aplicativo cliente tiver se desconectado sem limpar adequadamente seus recursos, você poderá encerrar a ID da sessão usando o KILL comando. O comando KILL usa o valor da ID da sessão como entrada. Por exemplo, para eliminar a ID da sessão 99, emita o seguinte comando:

    KILL 99

Conteúdo relacionado

  • Last updated on