Compreender e resolver problemas de bloqueio

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 de Malha e demonstra como solucionar problemas e resolver bloqueios.

Objetivo

Neste artigo, o termo conexão refere-se a uma única sessão conectada do banco de dados. Cada ligação aparece como um ID de sessão, ou session_id em muitos DMVs. Cada um destes IDs de sessão é frequentemente referido como um processo, embora não seja um contexto de processo separado no sentido habitual. Em vez disso, cada ID de sessão consiste nos recursos do servidor e nas estruturas de dados necessárias para servir os pedidos de uma única ligação de um dado cliente. Um único aplicativo cliente pode ter uma ou mais conexões. Da perspetiva do Banco de Dados SQL do Azure, não há diferença entre várias conexões de um único aplicativo cliente em um único computador cliente e várias conexões de vários aplicativos cliente ou vários computadores cliente; eles são atômicos. Uma conexão pode bloquear outra conexão, independentemente do cliente de origem.

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

Entenda o bloqueio

O bloqueio é uma característica inevitável e por design de qualquer sistema de gerenciamento de banco de dados relacional (RDBMS) com simultaneidade baseada em bloqueio. O bloqueio ocorre numa base de dados na Base de Dados SQL do Azure quando uma sessão bloqueia um recurso específico e um ID de segunda sessão tenta adquirir um tipo de bloqueio em conflito no mesmo recurso. Normalmente, o período de tempo durante o qual o ID da primeira sessão bloqueia o recurso é pequeno. Quando a sessão proprietária liberta o bloqueio, a segunda conexão fica livre para adquirir o seu próprio bloqueio no recurso e continuar o processamento. Esse comportamento é normal e pode acontecer muitas vezes ao longo de um dia sem efeito percetível no desempenho do sistema.

Cada novo banco de dados no Banco de Dados SQL do Azure tem a configuração de banco de dados RCSI (de instantâneo confirmado de leitura) habilitada por padrão. A minimização do bloqueio entre sessões que leem dados e sessões que escrevem dados é alcançada no RCSI, que utiliza a versão de linha para aumentar a simultaneidade. No entanto, bloqueios e interrupções ainda podem ocorrer em bases de dados no Banco de Dados SQL do Azure porque:

• As consultas que modificam dados podem bloquear umas às outras.
• As consultas podem ser executadas em níveis de isolamento que aumentam o bloqueio. Os níveis de isolamento podem ser especificados em cadeias de ligação da aplicação, sugestões de consultaou instruções SET no Transact-SQL.
• RCSI pode ser desativado, o que faz com que o banco de dados utilize bloqueios compartilhados (S) para proteger instruções SELECT executadas sob o nível de isolamento de leitura confirmada. Isso pode aumentar os bloqueios e os interbloqueios.

de nível de isolamento de instantâneo também é habilitado por padrão para 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 de nível de transação para dados e que usa versões de linha para selecionar linhas a serem atualizadas. Para usar o isolamento de "snapshot", as consultas ou conexões devem definir explicitamente o nível de isolamento de transação para SNAPSHOT. Isso só pode ser feito quando o isolamento por instantâneo está ativado para a base de dados.

É possível identificar se o RCSI e/ou o isolamento de snapshot estão habilitados com o Transact-SQL. Conecte-se ao seu 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 ativado, a coluna is_read_committed_snapshot_on devolve o valor 1. Se o isolamento de instantâneo for ativado, a coluna snapshot_isolation_state_desc retornará o valor ON.

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. As instruções SELECT são executadas sob RCSI , não adquirem bloqueios compartilhados (S) nos dados lidose, portanto, não bloqueiam transações que estão a modificar dados. Para instruções INSERT, UPDATE e DELETE, os bloqueios são mantidos durante a consulta, tanto para consistência de dados quanto para permitir que a consulta seja revertida, se necessário.

Para consultas executadas dentro de uma transação explícita, o tipo de bloqueio e a duração pela qual os bloqueios são mantidos são determinados 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 de transação, consulte os seguintes artigos:

• Bloqueio no Mecanismo de Banco de Dados
• Personalização de bloqueios e versionamento de linhas
• modos de bloqueio
• Compatibilidade de Bloqueio
• Transações

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

• Um ID de sessão mantém bloqueios num conjunto de recursos durante um período prolongado antes de os libertar. Este tipo de bloqueio resolve-se ao longo do tempo, mas pode causar degradação do desempenho.

• Um ID de sessão mantém bloqueios sobre um conjunto de recursos e nunca os liberta. 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 diferentes IDs de sessão causam bloqueios em diferentes recursos ao longo do tempo, criando um alvo móvel. Essas situações são difíceis de solucionar usando SQL Server Management Studio para restringir o problema a consultas individuais. Em contraste, 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 de bloqueio e o número de bloqueios simultaneamente necessários para gravações. O bloqueio otimizado usa dois componentes principais: bloqueio de ID de transação (TID) (também usado em outros recursos de controlo de versões de linha) e bloqueio após qualificação (LAQ). Não requer nenhuma configuração extra.

Este artigo atualmente se aplica 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, consulte Bloqueio otimizado.

Aplicações e bloqueio

Pode haver uma tendência de se concentrar no ajuste do lado do servidor e em problemas de plataforma 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 tempo e energia que seriam melhor utilizados para examinar a aplicação cliente e as consultas que esta envia. Não importa o nível de visibilidade que o aplicativo exponha 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 consultas, gerenciamento de conexões, busca de todas as linhas de resultados e assim por diante. Se a ferramenta de desenvolvimento não permitir controle explícito sobre o gerenciamento de conexão, cancelamento de consultas, tempo limite de consulta, busca de resultados e assim por diante, os problemas de bloqueio podem não ser solucionáveis. Esse potencial deve ser examinado de perto 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.

Preste atenção ao desempenho do banco de dados durante a fase de projeto e construção do banco de dados e do aplicativo. Em particular, o consumo de recursos, o nível de isolamento e o comprimento do caminho de transação devem ser avaliados para cada consulta. Cada consulta e transação deve ser o mais leve possível. Uma boa disciplina de gestão 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 se degradar significativamente à medida que o número de usuários aumenta para cima.

Com o design adequado de aplicativos e consultas, o Banco de Dados SQL do Azure é capaz de suportar muitos milhares de usuários simultâneos em um único servidor, com pouco bloqueio.

Solucionar problemas de bloqueio

Independentemente da situação de bloqueio em que nos encontramos, a metodologia para solucionar problemas de bloqueio é a mesma. Estas separações lógicas são o que dita o resto da composição deste artigo. O conceito é encontrar o bloqueador principal e identificar o que essa consulta está fazendo e por que está bloqueando. Uma vez identificada a consulta problemática (ou seja, o que está mantendo bloqueios pelo período prolongado), o próximo passo é analisar e determinar a causa do 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 principal de bloqueio (principal bloqueador)

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

3. Analisar/entender por que ocorre o bloqueio prolongado

4. Resolva o problema de bloqueio redesenhando a consulta e a transação

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

Reunir informações de bloqueio

Para neutralizar a dificuldade de solucionar problemas de bloqueio, um administrador de banco de dados pode usar scripts SQL que monitoram constantemente o estado de bloqueio e bloqueio no banco de dados no Banco de Dados SQL do Azure. Para recolher estes dados, existem essencialmente dois métodos.

A primeira é consultar objetos de gerenciamento dinâmico (DMOs) e armazenar os resultados para comparação ao longo do tempo. Alguns objetos mencionados neste artigo são visões de gestão dinâmica (DMVs) e alguns são funções de gestão dinâmica (DMFs). O segundo método é usar XEvents para capturar o que está sendo executado.

Reunir informações dos Departamentos de Veículos Motorizados (DMVs)

Referenciar DMVs para resolver problemas de bloqueio tem como objetivo identificar o ID da sessão no início da cadeia de bloqueio e a Instrução SQL. Procure os IDs de sessão das vítimas que estão a ser bloqueados. Se algum ID de sessão estiver a ser bloqueado por outro ID de sessão, então investigue o ID de sessão que detém o recurso (o ID de sessão bloqueante). Esse ID de sessão do proprietário também está a ser bloqueado? Você pode percorrer a cadeia para encontrar o bloqueador principal e, em seguida, investigar por que ele está mantendo o seu bloqueio.

Lembre-se de executar cada um desses scripts no banco de dados de destino no Banco de Dados SQL do Azure.

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

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

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

• Consulte 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 liste apenas solicitações em execução no momento, qualquer conexão (ativa ou não) está listada em sys.dm_exec_sessions. Baseie-se nesta junção comum entre sys.dm_exec_requests e sys.dm_exec_sessions na próxima consulta.

• Execute esta consulta SQL para encontrar as consultas que estão a ser executadas ativamente e o seu texto atual do lote SQL ou o texto do buffer de entrada, utilizando os 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 no momento. Nesse caso, o campo event_info de sys.dm_exec_input_buffer contém a última cadeia de caracteres de comando passada para o mecanismo SQL. Esta consulta também pode ser usada para identificar sessões que bloqueiam outras sessões, incluindo uma lista de IDs de sessão bloqueados por ID de sessão.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

• Execute esta consulta de exemplo mais elaborada, fornecida pelo Suporte da Microsoft, para identificar o chefe de uma cadeia de bloqueio de várias sessões, incluindo o texto da consulta das sessões envolvidas em uma cadeia de bloqueio.

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

• Para capturar transações de longa duração ou não confirmadas, use outro conjunto de DMVs para exibir transações abertas atuais, incluindo sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connectionse sys.dm_exec_sql_text. Há várias DMVs associadas ao rastreamento de transações; consulte a seção sobre vistas e funções de gerenciamento dinâmico relacionadas a transações para mais informações.

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. Isso retorna informações sobre o tipo de espera SQL que a solicitação está enfrentando no momento. Como sys.dm_exec_requests, somente solicitações ativas são retornadas por sys.dm_os_waiting_tasks.

• Use o sys.dm_tran_locks Detran para obter informações mais granulares sobre quais bloqueios foram colocados por consultas. Esse Detran pode retornar grandes quantidades de dados em um banco de dados de produção e é útil para diagnosticar quais bloqueios são mantidos atualmente.

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

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

• Com os DMVs, o armazenamento dos resultados da consulta ao longo do tempo fornece pontos de dados que permitem que você revise o bloqueio em um intervalo de tempo especificado para identificar bloqueios persistentes ou tendências.

Reúna informações de eventos estendidos

Além das informações anteriores, muitas vezes é 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 executa várias instruções dentro de uma transação, somente a última instrução enviada é representada. No entanto, uma das declarações anteriores pode ser a razão pela 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, SQL Server Profiler é uma tecnologia de rastreamento preterida que não é suportada para o Banco de Dados SQL do Azure. Extended Events é a mais recente tecnologia de rastreamento que permite mais versatilidade e menos efeito no sistema observado, e sua interface é integrada ao SQL Server Management Studio (SSMS).

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

• Erros de categoria:

  • Atenção
  • Erro_relato
  • Aviso de Execução

• Avisos de Categoria:

  • Missing_join_predicate

• Execução da categoria:

  • Rpc_concluído
  • Rpc_starting
  • Sql_lote_concluído
  • Lote_de_Sql_iniciando

• Categoria deadlock_monitor

  • relatório_deadlock_xml_base_de_dados

• Sessão de categoria

  • Ligação_existente
  • Iniciar sessão
  • Sair

Identificar e resolver cenários de bloqueio comuns

Ao examinar as informações anteriores, você pode determinar a causa da maioria dos problemas de bloqueio. O restante deste artigo é uma discussão sobre como usar essas informações para identificar e resolver alguns cenários de bloqueio comuns. Esta discussão parte do princípio de que utilizou os scripts de bloqueio (referidos anteriormente) para captar informação sobre os IDs das sessões de bloqueio e capturou a atividade da aplicação usando uma sessão XEvent.

Analise os dados de bloqueio

• Examinar a saída dos DMVs sys.dm_exec_requests e sys.dm_exec_sessions para determinar as cabeças das cadeias de bloqueio, usando blocking_these e session_id. Isso identifica mais claramente quais solicitações estão bloqueadas e quais estão bloqueando. Analise ainda mais as sessões que estão bloqueadas e bloqueadas. Existe uma raiz comum à cadeia de blocos? Eles provavelmente compartilham uma tabela comum, e uma ou mais das sessões envolvidas numa cadeia de bloqueio estão a executar uma operação de gravação.

• Examine a saída dos DMVs sys.dm_exec_requests e sys.dm_exec_sessions para obter informações sobre os IDs de sessão no início da cadeia de bloqueio. Procure os seguintes campos:

  • sys.dm_exec_requests.status
    Esta coluna mostra o status de uma solicitação específica. Normalmente, um estado de inatividade indica que o ID da sessão concluiu a execução e está à espera que a aplicação submeta outra consulta ou lote. Um estado executável ou em execução indica que o ID da sessão está atualmente a processar uma consulta. A tabela a seguir fornece breves explicações sobre os vários valores de status.

  Situação	Significado
  Contexto geral	O ID da sessão está a executar uma tarefa em segundo plano, como deteção de deadlocks, escrita de logs ou checkpoint.
  Dormindo	O ID da sessão não está a ser executado neste momento. Isto normalmente indica que o ID da sessão está à espera de um comando da aplicação.
  Correr	O ID da sessão está atualmente a correr num agendador.
  Executável	O ID da sessão está na fila executável de um agendador e à espera de ser agendado.
  Suspenso	O ID da sessão está à espera de um recurso, como uma fechadura ou uma trava.

  • sys.dm_exec_sessions.open_transaction_count
    Este campo informa o número de transações abertas nesta sessão. Se este valor for superior a 0, o ID da sessão encontra-se numa transação aberta e pode conter 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 este valor for superior a 0, o ID da sessão encontra-se numa transação aberta e pode conter bloqueios adquiridos por qualquer instrução dentro da transação.

  • sys.dm_exec_requests.wait_type, wait_timee last_wait_type
    Se o sys.dm_exec_requests.wait_type for NULL, a solicitação não está aguardando nada no momento e o valor last_wait_type indica o último wait_type que a solicitação encontrou. Para obter mais informações sobre sys.dm_os_wait_stats e uma descrição dos tipos de espera mais comuns, consulte sys.dm_os_wait_stats. O valor wait_time pode ser usado para determinar se a solicitação está progredindo. Quando uma consulta na tabela sys.dm_exec_requests retorna 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 obtido e libertado e está agora à espera de um novo bloqueio, assumindo que wait_timeé diferente de zero. Isso pode ser verificado comparando o wait_resource com a saída de sys.dm_exec_requests, que exibe o recurso pelo qual a solicitação está esperando.

  • sys.dm_exec_requests.wait_resource Este campo indica o recurso em que uma solicitação bloqueada está aguardando. A tabela a seguir lista formatos de wait_resource comuns e seu significado:

  Recurso	Formato	Exemplo	Explicação
  Tabela	DatabaseID:ObjectID:IndexID	TAB: 05:261575970:1	Nesse caso, o ID do banco de dados 5 é o banco de dados de exemplo pubs e o ID do objeto 261575970 é a tabela de títulos e 1 é o índice clusterizado.
  Página	DatabaseID:FileID:PageID	PÁGINA: 5:1:104	Neste caso, o ID de banco de dados 5 is pubs, o ID de arquivo 1 é o arquivo de dados primário e a página 104 é uma página pertencente à tabela de títulos. Para identificar a que object_id a página pertence, use a função de gerenciamento dinâmico sys.dm_db_page_info, passando o DatabaseID, FileId, e PageId do wait_resource.
  Chave	DatabaseID:Hobt_id (valor de hash para chave de índice)	CHAVE: 5:72057594044284928 (3300a4f361aa)	Neste caso, o ID de banco de dados 5 é pubse Hobt_ID 72057594044284928 corresponde ao 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 reverter o hash da chave de índice para obter um valor de chave específico.
  Linha	DatabaseID:FileID:PageID:Slot(linha)	RID: 5:1:104:3	Nesse caso, o ID de banco de dados 5 é pubs, o ID de arquivo 1 é o arquivo de dados primário, a página 104 é uma página pertencente à tabela de títulos e o slot 3 indica a posição da linha na página.
  Compilar	DatabaseID:FileID:PageID:Slot(linha)	RID: 5:1:104:3	Nesse caso, o ID de banco de dados 5 é pubs, o ID de arquivo 1 é o arquivo de dados primário, a página 104 é uma página pertencente à tabela de títulos e o slot 3 indica a posição da linha na página.

  • sys.dm_tran_active_transactions O sys.dm_tran_ative_transactions DMV contém dados sobre transações abertas que podem ser unidas a outros DMVs para obter uma visão completa das transações que aguardam confirmação ou reversão. Use a consulta a seguir para retornar informações sobre transações abertas, unidas a outros DMVs, incluindo sys.dm_tran_session_transactions. Considere o estado atual de uma transação, transaction_begin_timee outros dados situacionais 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. A sua utilidade varia consoante as circunstâncias do problema. Por exemplo, pode determinar se o problema ocorre apenas em certos clientes (nome do host), em certas bibliotecas de rede (net_library), quando o último lote submetido por um ID de sessão estava last_request_start_time em sys.dm_exec_sessions, há quanto tempo um pedido estava a correr usando start_time em sys.dm_exec_requests, e assim sucessivamente.

Cenários de bloqueio comuns

A tabela abaixo mapeia os sintomas comuns para 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 cancelada por meio do comando KILL. Para obter mais informações, consulte KILL.

Cenários de bloqueio detalhados

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

   Resolução: A solução para este 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 exigir que você o busque como tal. Para obter informações sobre como solucionar problemas de uma consulta de execução lenta específica, consulte Como solucionar problemas de consultas de execução lenta no SQL Server. Para obter mais informações, consulte Monitor e ajuste para desempenho.

   Os relatórios do Query Store no SSMS também são uma ferramenta altamente recomendada e valiosa para identificar as consultas mais dispendiosas e os planos de execução subótimos. Consulte também Query Performance Insight.

   Se a consulta executar apenas operações SELECT, considere executar a instrução em isolamento de instantâneo se ela estiver habilitada em seu banco de dados, especialmente se o RCSI tiver sido desativado. Tal como quando o RCSI está ativado, as consultas de leitura de dados não requerem bloqueios partilhados (S) no nível de isolamento por instantâneo. Além disso, o isolamento de instantâneo fornece consistência ao nível da transação para todas as declarações numa transação múltipla explícita. O isolamento de instantâneo pode já estar habilitado em seu banco de dados. O isolamento de instantâneo também pode ser usado com consultas que executam modificações, mas você deve lidar com conflitos de atualização .

   Se tiveres uma consulta de execução prolongada que está a bloquear outros utilizadores e não pode ser optimizada, considera movê-la de um ambiente OLTP para um sistema de relatórios dedicado, uma réplica síncrona apenas de leitura da base de dados.

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

   Este tipo de bloqueio pode frequentemente ser identificado por um ID de sessão que está em suspensão ou à espera de um comando, mas cujo nível de aninhamento de transações (@@TRANCOUNT, open_transaction_count de sys.dm_exec_requests) é maior que zero. Isso pode ocorrer se o aplicativo tiver um tempo limite de consulta ou emitir um cancelamento sem também emitir o número necessário de instruções ROLLBACK e/ou COMMIT. Quando um ID de sessão recebe um tempo limite da consulta ou um cancelamento, termina a consulta e o lote atuais, mas não reverte automaticamente nem confirma a transação. O aplicativo é responsável por isso, pois o Banco de Dados SQL do Azure não pode assumir que uma transação inteira deve ser revertida devido ao cancelamento de uma única consulta. O time-out ou cancelamento da consulta aparece como um evento de sinal de ATENÇÃO para o 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 esta consulta na mesma janela:

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

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

   Utilize o script mencionado anteriormente neste artigo, com base em sys.dm_tran_active_transactions, para identificar as transações não confirmadas atualmente na instância.

   Resoluções:

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

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

     • No manipulador de erros do aplicativo cliente, execute IF @@TRANCOUNT > 0 ROLLBACK TRAN após qualquer erro, mesmo que o aplicativo cliente não acredite que uma transação esteja aberta. A verificação de transações abertas é necessária, porque um procedimento armazenado chamado durante o lote pode ter iniciado uma transação sem o conhecimento do aplicativo cliente. Certas 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 abortar a transação, esse código de reversão não é executado nesses casos.
     • Se o pool de conexões estiver sendo usado em um aplicativo que abre a conexão e executa algumas consultas antes de liberar a conexão de volta para o pool, como um aplicativo baseado na Web, desabilitar temporariamente o pool de conexões pode ajudar a aliviar o problema até que o aplicativo cliente seja modificado para lidar com os erros adequadamente. Ao desabilitar o pool de conexões, liberar a conexão causa uma desconexão física da conexão do Banco de Dados SQL do Azure, resultando na reversão de quaisquer transações abertas pelo servidor.
     • Use SET XACT_ABORT ON para a conexão ou em quaisquer procedimentos armazenados que iniciem transações e não sejam limpos após um erro. No caso de um erro em tempo de execução, essa configuração anula todas as transações abertas e retorna o controle para o cliente. Para obter mais informações, consulte 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, em seguida, liberar a conexão para o pool de conexões, mas ela não pode ser reutilizada por vários segundos, durante o qual a transação permanecerá 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. Assim, é ideal para o aplicativo cliente abortar transações em seu manipulador de erros ou usar SET XACT_ABORT ON para evitar esse atraso potencial.

   Atenção

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

3. Bloqueio causado por um ID de sessão em que a aplicação cliente correspondente não obteve todas as linhas de resultados completamente.

   Depois de enviar uma consulta para o servidor, todos os aplicativos devem buscar imediatamente todas as linhas de resultados até a conclusão. Se uma aplicação não obtiver todas as linhas de resultado, podem ficar bloqueios nas tabelas, impedindo outros utilizadores. Se você estiver usando um aplicativo que envia instruções SQL de forma transparente para o servidor, o aplicativo deve buscar todas as linhas de resultado. Se isso não acontecer (e se não puder ser configurado para isso), talvez você não consiga resolver o problema de bloqueio. Para evitar o problema, você pode restringir aplicativos mal comportados a um relatório ou um banco de dados de suporte a decisões, separado do banco de dados OLTP principal.

   O impacto deste cenário é reduzido quando o snapshot de leitura confirmada é ativado 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 orientações sobre a lógica de repetição para aplicações que se conectam ao Banco de Dados SQL do Azure.

   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 no servidor.

4. Bloqueio causado por uma sessão em 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 vítima de deadlock. Isso geralmente pode ser identificado observando a saída de sys.dm_exec_requests, que pode indicar o comando ROLLBACK, e a coluna percent_complete pode mostrar progresso.

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

   Resolução: Esperar que o ID da sessão termine de reverter as alterações feitas.

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

5. Bloqueio causado por uma ligação órfã

   Se o aplicativo cliente intercetar erros ou a estação de trabalho cliente for reiniciada, a sessão de rede para o servidor pode não ser cancelada imediatamente em algumas condições. Da perspetiva do Banco de Dados SQL do Azure, o cliente ainda parece estar presente e quaisquer bloqueios adquiridos ainda podem ser mantidos. Para obter mais informações, consulte Como solucionar problemas de conexões órfãs no SQL Server.

   Resolução: Se a aplicação cliente se desligou sem limpar adequadamente os seus recursos, pode terminar o ID da sessão usando o KILL comando. O comando KILL utiliza o valor do ID de sessão como entrada. Por exemplo, para eliminar o ID de sessão 99, emita o seguinte comando:

   KILL 99
   

Conteúdo relacionado

• Analisar e evitar impasses no Banco de Dados SQL do Azure e no Banco de Dados SQL do Fabric
• Monitorização e otimização de desempenho na Base de Dados SQL do Azure e na Instância Gerida do Azure SQL
• Monitorar o desempenho usando o Repositório de Consultas
• Guia de Bloqueio de Transações e Versionamento de Linhas
• DEFINIR O NÍVEL DE ISOLAMENTO DA TRANSAÇÃO (Transact-SQL)
• Guia de Início Rápido: Eventos Estendidos
• Banco de Dados SQL do Azure: melhorando o ajuste de desempenho com de ajuste automático
• Forneça um desempenho consistente com o SQL do Azure
• Solucionar problemas de conectividade e outros erros
• Tratamento de falhas transitórias
• Configurar o grau máximo de paralelismo (MAXDOP) no Banco de Dados SQL do Azure
• Diagnosticar e solucionar problemas de alta CPU na Base de Dados SQL do Azure e na Base de Dados SQL do Microsoft Fabric