Entender e resolver problemas de bloqueio do banco de dados SQL do Azure

Aplica-se a: Banco de Dados SQL do Azure

O artigo descreve o bloqueio em bancos de dados SQL do Azure 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 (SPID) ou session_id em muitos DMVs. Cada uma dessas SPIDs geralmente é conhecida como um processo, embora não seja um contexto de processo separado de fato. 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 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, confira Analisar e evitar deadlocks no Banco de Dados SQL do Azure.

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 um segundo SPID tenta obter um tipo de bloqueio conflitante no mesmo recurso. Normalmente, o intervalo de tempo para o qual o primeiro SPID bloqueia o recurso é pequeno. Quando a sessão proprietária libera o bloqueio, a segunda conexão é liberada para adquirir seu próprio bloqueio no recurso e continuar o processamento. Esse comportamento é normal e pode ocorrer muitas vezes durante o curso de um dia sem 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.

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. 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:

• Bloqueio no Mecanismo de Banco de Dados
• Personalizando bloqueio e controle de versão de linha
• Modos de Bloqueio
• Compatibilidade de Bloqueios
• Transações

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 SPID mantém os bloqueios em um conjunto de recursos por um período de tempo longo antes de liberá-los. Esse tipo de bloqueio se resolve sozinho ao longo do tempo, mas pode causar degradação do desempenho.

• Uma SPID mantém os 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.

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). Não requer nenhuma configuração adicional.

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. 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 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ão deve ser exercida, sem isso, o aplicativo pode parecer ter um desempenho aceitável com um número baixo de usuários, mas o desempenho pode diminuir significativamente à medida que o número de usuários aumenta.

Com o design adequado de aplicativos e consultas, o banco de dados SQL do Azure é capaz de dar suporte a 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. 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 o motivo, podemos fazer as alterações remodelando 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

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.

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ê 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);

• 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 NULOS, a consulta não estará em execução no momento. 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;

• Para capturar transações de execução longa ou não confirmadas, use outro conjunto de DMVs para exibir as transações abertas atuais, incluindo sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections e sys.dm_exec_sql_text. Existem várias DMVs associadas à transações de rastreamento, revise mais DMVs em transações aqui.

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.

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

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 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 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. O Eventos Estendidos é a tecnologia de rastreamento mais recente que permite mais versatilidade e menos impacto 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

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. Esta discussão considera que você já usou os scripts de bloqueio (referenciados anteriormente) para capturar informações sobre as SPIDs de bloqueio e capturou a atividade do aplicativo usando uma sessão do 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 os campos a seguir:

  • 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
    Este campo 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.

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

  • 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	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 do catálogo sys.partitions para associar a hobt_id a uma index_id e uma object_id específicas. 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
  , 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 determinar se o problema ocorre apenas em determinados clientes (nome do host), em determinadas bibliotecas de rede (net_library), quando o último lote enviado por uma SPID era last_request_start_time em sys.dm_exec_sessions, por quanto tempo uma solicitação foi executada usando start_time em sys.dm_exec_requests etc.

Cenários comuns de bloqueio

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

As colunas Waittype, Open_Tran e Status referem-se à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á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. Revise também a seção de Desempenho Inteligente do portal do Azure para o banco de dados SQL do Azure, incluindo 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 uma SPID suspensa que tem uma transação não confirmada

   Em geral, esse tipo de bloqueio pode ser identificado por uma SPID que está suspensa ou aguardando um comando, mas cujo nível de aninhamento da transação (@@TRANCOUNT, open_transaction_count de sys.dm_exec_requests) é superior a 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 SPID recebe um tempo limite de consulta ou um cancelamento, ele encerra a consulta e o lote atuais, mas não reverte ou confirma a transação automaticamente. 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 da consulta ou o cancelamento aparecerá como um evento de sinal ATTENTION para a 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;
   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. 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 um pequeno número de consultas antes de liberar a conexão de volta ao 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 manipular 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, 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 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

   SET XACT_ABORT ON A seguir, instruções T-SQL seguindo uma instrução que causa um erro não serão executadas. Isso pode afetar o fluxo pretendido de código existente.

3. Bloqueio causado por uma SPID cujo aplicativo cliente correspondente não buscou todas as linhas de resultado para 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 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 é 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. 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 ao recurso Recuperação de Banco de Dados Acelerada introduzido em 2019, as reversões demoradas devem ser raras.

   Resolução: aguarde até que a SPID termine de reverter as alterações 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 a limpeza adequada de seus recursos, você pode encerrar a SPID usando o comando KILL. O comando KILL usa o valor da SPID como entrada. Por exemplo, para eliminar a SPID 99, emita o seguinte comando:

   KILL 99
   

Conteúdo relacionado

• Analisar e evitar deadlocks no Banco de Dados SQL do Azure
• Monitoramento e ajuste de desempenho no Banco de Dados SQL do Azure e da Instância Gerenciada de SQL do Azure
• Monitorando o desempenho usando o Repositório de Consultas
• Guia de Controle de Versão de Linha e Bloqueio de Transações
• SET TRANSACTION ISOLATION LEVEL
• Início Rápido: Eventos estendidos no SQL Server
• Banco de Dados SQL do Azure: melhorando o ajuste de desempenho com ajuste automático
• Fornecer desempenho consistente com o SQL Azure
• Solucionando problemas de conectividade e outros erros com o Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure
• Transient Fault Handling
• Configurar o MAXDOP (grau máximo de paralelismo) no Banco de Dados SQL do Azure
• Diagnosticar e solucionar problemas de alta CPU em Banco de Dados SQL do Azure