Partilhar via

Compreender e resolver problemas de bloqueio da Base de Dados SQL do Azure

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Objetivo

O artigo descreve o bloqueio nos bancos de dados SQL do Azure e demonstra como solucionar problemas e resolver o bloqueio.

Neste artigo, o termo conexão refere-se a uma única sessão conectada do banco de dados. Cada conexão aparece como um ID de sessão (SPID) ou session_id em muitos DMVs. Cada um destes SPIDs é muitas vezes referido como um processo, embora não seja um contexto de processo separado no sentido usual. Em vez disso, cada SPID consiste nos recursos do servidor e estruturas de dados necessários para atender às solicitações de uma única conexão de um determinado 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; são atómicas. 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.

Nota

Este conteúdo é focado 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 ferramentas de solução de problemas possam diferir. Para obter mais informações sobre bloqueio no SQL Server, consulte Compreender e resolver problemas de bloqueio do SQL Server.

Entenda o bloqueio

O bloqueio é uma característica inevitável e propositada de qualquer sistema de gestão de bases de dados relacionais (RDBMS) com simultaneidade baseada em bloqueio. O bloqueio em um banco de dados no 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 adquirir um tipo de bloqueio conflitante no mesmo recurso. Normalmente, o período 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 fica livre para adquirir seu próprio bloqueio no recurso e continuar o processamento. Este é um 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 (instantâneo confirmado de leitura) habilitada por padrão. O bloqueio entre dados de leitura de sessões e dados de gravação de sessões é minimizado no RCSI, que usa o controle de versão de linha para aumentar a simultaneidade. No entanto, bloqueios e bloqueios ainda podem ocorrer em bancos 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 sob níveis de isolamento que aumentam o bloqueio. Os níveis de isolamento podem ser especificados em cadeias de conexão de aplicativo, dicas de consulta ou instruções SET no Transact-SQL.
  • O RCSI pode ser desativado, fazendo com que o banco de dados use bloqueios compartilhados (S) para proteger instruções SELECT executadas sob o nível de isolamento confirmado de leitura. Isso pode aumentar o bloqueio e os bloqueios.

O 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 instantâneo, as consultas ou conexões devem definir explicitamente seu nível de isolamento de transação como SNAPSHOT. Isso só pode ser feito quando o isolamento de instantâneo estiver habilitado para o banco 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 retornará o is_read_committed_snapshot_on valor 1. Se o isolamento de instantâneo estiver habilitado, a coluna retornará o snapshot_isolation_state_desc valor ON.

A duração e o contexto da transação de uma consulta determinam por quanto tempo seus bloqueios são mantidos e, portanto, seu efeito em outras consultas. As instruções SELECT executadas em RCSI não adquirem bloqueios compartilhados (S) nos dados que estão sendo lidos e, portanto, não bloqueiam transações que estejam modificando 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 bloqueios e a duração para os quais 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:

Quando o bloqueio persiste até o ponto em que há um efeito prejudicial no desempenho do sistema, é devido a uma das seguintes razões:

  • Um SPID mantém bloqueios em um conjunto de recursos por um longo período de tempo antes de liberá-los. Esse tipo de bloqueio se resolve com o tempo, mas pode causar degradação do desempenho.

  • Um SPID mantém bloqueios em um conjunto de recursos e nunca os libera. Este tipo de bloqueio não se resolve por si só e impede o acesso aos recursos afetados indefinidamente.

No primeiro cenário, a situação pode ser muito fluida, pois diferentes SPIDs causam bloqueio em diferentes recursos ao longo do tempo, criando um alvo em movimento. Essas situações são difíceis de solucionar usando o 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 controle de versão de linha) e bloqueio após qualificação (LAQ). Não requer nenhuma configuração adicional.

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, a atenção dada apenas ao banco de dados pode não levar a uma resolução, e pode absorver tempo e 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 exponha em relação às chamadas de banco de dados que estão sendo feitas, um problema de bloqueio, no entanto, 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 o controle explícito sobre o gerenciamento de conexão, cancelamento de consulta, 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 gerenciamento de conexões deve ser exercida, sem ela, 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 milhares de usuários simultâneos em um único servidor, com pouco bloqueio.

Nota

Para obter mais orientações de desenvolvimento de aplicativos, consulte Solução de problemas de conectividade e outros erros com o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure e Tratamento de Falhas Transitórias.

Resolver problemas de bloqueio

Independentemente da situação de bloqueio em que nos encontramos, a metodologia para solucionar problemas de bloqueio é a mesma. Essas separações lógicas são o que ditará o resto da composição deste artigo. O conceito é encontrar o bloqueador de cabeça e identificar o que essa consulta está fazendo e por que ela está bloqueando. Uma vez identificada a consulta problemática (ou seja, o que está segurando bloqueios pelo período prolongado), o próximo passo é analisar e determinar por que o bloqueio está acontecendo. Depois de entendermos o porquê, podemos fazer alterações redesenhando a consulta e a transação.

Etapas na solução de problemas:

  1. Identificar a sessão de bloqueio principal (bloqueador de cabeça)

  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 exibições de gerenciamento dinâmico (DMVs) e alguns são funções de gerenciamento dinâmico (DMFs). O segundo método é usar XEvents para capturar o que está sendo executado.

Reunir informações de DMVs

Referenciar DMVs para solucionar problemas de bloqueio tem o objetivo de identificar o SPID (ID de sessão) na cabeça da cadeia de bloqueio e a instrução SQL. Procure SPIDs de vítimas que estão sendo bloqueados. Se algum SPID estiver sendo bloqueado por outro SPID, investigue o SPID proprietário do recurso (o SPID de bloqueio). Esse SPID proprietário também está sendo bloqueado? Você pode andar na corrente para encontrar o bloqueador de cabeça e, em seguida, investigar por que ele está mantendo 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 Detran sys.dm_exec_sessions retorna mais dados em um conjunto de resultados mais fácil de consultar e filtrar. Você encontrará sys.dm_exec_sessions no centro de outras consultas.

  • Se você já tiver uma sessão específica identificada, você pode usar DBCC INPUTBUFFER(<session_id>) para encontrar a última declaração que foi enviada por uma sessão. Resultados semelhantes podem ser retornados com a sys.dm_exec_input_buffer função de gerenciamento dinâmico (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 blocking_session_id coluna 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 atualmente em execução, qualquer conexão (ativa ou não) será listada em sys.dm_exec_sessions. Desenvolva essa 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 ativa e seu texto de lote SQL atual ou texto de buffer de entrada, usando o sys.dm_exec_sql_text ou sys.dm_exec_input_buffer DMVs. Se os dados retornados pelo text campo de sys.dm_exec_sql_text é NULL, a consulta não está sendo executada no momento. Nesse caso, o campo de conterá a última cadeia de caracteres de sys.dm_exec_input_buffer comando passada para o event_info mecanismo SQL. Essa consulta também pode ser usada para identificar sessões que bloqueiam outras sessões, incluindo uma lista de session_ids bloqueados 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 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;

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, apenas as solicitações ativas são retornadas pelo sys.dm_os_waiting_tasks.

Nota

Para saber mais sobre os tipos de espera, incluindo estatísticas de espera agregadas ao longo do tempo, consulte o sys.dm_db_wait_stats do Detran. Este DMV retorna estatísticas de espera agregadas apenas para o banco de dados atual.

  • 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 on sys.dm_os_waiting_tasks, a consulta a seguir restringe a saída de apenas para solicitações bloqueadas no momento, seu status de sys.dm_tran_locks 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, 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 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 completamente um problema de bloqueio no Banco de Dados SQL do Azure. Por exemplo, se uma sessão executar várias instruções dentro de uma transação, somente a última instrução enviada será representada. No entanto, uma das declarações anteriores pode ser a razão pela qual os bloqueios ainda estão sendo mantidos. Um rastreamento permitirá que você veja todos os comandos executados por uma sessão dentro da transação atual.

Há duas maneiras de capturar rastreamentos no SQL Server; Eventos estendidos (XEvents) e rastreamentos do Profiler. No entanto, o SQL Server Profiler é uma tecnologia de rastreamento preterida sem suporte para o Banco de Dados SQL do Azure. Os Eventos Estendidos são a mais recente tecnologia de rastreamento que permite mais versatilidade e menos impacto 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. Use um assistente de sessão de Eventos Estendidos para capturar estes eventos úteis:

  • Erros de categoria:

    • Atenção
    • Error_reported
    • Execution_warning

  • Advertências da 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
    • Iniciar Sessão
    • Fim de Sessão

Nota

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

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 pressupõe que você tenha usado os scripts de bloqueio (referenciados anteriormente) para capturar informações sobre os SPIDs de bloqueio e tenha capturado a atividade do aplicativo usando uma sessão XEvent.

Analise os dados de bloqueio

  • Examinar a saída dos DMVs e determinar as cabeças sys.dm_exec_requests das cadeias de bloqueio, usando blocking_these e sys.dm_exec_sessions session_id. Isso identificará 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 ou comum à cadeia de bloqueio? Eles provavelmente compartilham uma tabela comum, e uma ou mais das sessões envolvidas em uma cadeia de bloqueio está executando uma operação de gravação.

  • Examine a saída dos DMVs sys.dm_exec_requests e sys.dm_exec_sessions obtenha informações sobre os SPIDs na cabeça 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 status de suspensão indica que o SPID concluiu a execução e está aguardando que o aplicativo envie outra consulta ou lote. Um status executável ou em execução indica que o SPID está processando uma consulta no momento. A tabela a seguir fornece breves explicações sobre os vários valores de status.
    Status Significado
    Fundo O SPID está executando uma tarefa em segundo plano, como deteção de deadlock, gravador de log ou ponto de verificação.
    Dormindo O SPID não está em execução no momento. Isso geralmente indica que o SPID está aguardando um comando do aplicativo.
    Em execução O SPID está atualmente em execução em um agendador.
    Executável O SPID está na fila executável de um agendador e aguardando para obter o tempo do agendador.
    Suspensa O SPID está aguardando um recurso, como um cadeado 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, o SPID está dentro de uma transação aberta e pode estar mantendo bloqueios adquiridos por qualquer extrato 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, o SPID está dentro de uma transação aberta e pode estar mantendo bloqueios adquiridos por qualquer extrato dentro da transação.

    • sys.dm_exec_requests.wait_type, , wait_timee last_wait_type
      Se o for NULL, a solicitação não está atualmente aguardando nada e o valor indica o sys.dm_exec_requests.wait_type last_wait_type último wait_type que a solicitação encontrou. Para obter mais informações e sys.dm_os_wait_stats uma descrição dos tipos de espera mais comuns, consulte sys.dm_os_wait_stats. O wait_time valor pode ser usado para determinar se a solicitação está progredindo. Quando uma consulta na tabela retorna um valor na sys.dm_exec_requests wait_time coluna que é menor do que o valor de uma consulta anterior de , isso indica que o wait_time bloqueio anterior foi adquirido e liberado e agora está aguardando um novo bloqueio (assumindo diferente de sys.dm_exec_requestszero wait_time). Isso pode ser verificado comparando a saída entresys.dm_exec_requests, que exibe o recurso para o qual a wait_resource solicitação está aguardando.

    • 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 comuns wait_resource e seu significado:

    recurso Formato Exemplo Explicação
    Table DatabaseID:ObjectID:IndexID TAB: 05:261575970:1 Nesse caso, o ID de 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 Nesse caso, o ID de banco de dados 5 é 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 object_id 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.
    Key DatabaseID:Hobt_id (valor de hash para chave de índice) CHAVE: 5:72057594044284928 (3300a4f361aa) Neste caso, o ID de banco de dados 5 é Pubs, Hobt_ID 72057594044284928 corresponde ao index_id 2 para object_id 261575970 (tabela de títulos). Use o modo de exibição de catálogo para associar o sys.partitions hobt_id a um determinado index_id e object_id. Não há como deshash o hash da chave de índice para um valor de chave específico.
    Linha DatabaseID:FileID:PageID:Slot(linha) MONTAGEM: 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) MONTAGEM: 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 transaction_begin_timeatual de uma transação e 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 em função das circunstâncias do problema. Por exemplo, você pode determinar se o problema acontece apenas a partir de determinados clientes (nome do host), em determinadas bibliotecas de rede (net_library), quando o último lote enviado por um SPID estava last_request_start_time dentro sys.dm_exec_sessions, há quanto tempo uma solicitação estava sendo executada usando start_time o , sys.dm_exec_requestse assim por diante.

Cenários de bloqueio comuns

A tabela abaixo mapeia os sintomas comuns para suas causas prováveis.

As colunas Waittype, Open_Tran e Status referem-se a 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 será resolvido por conta própria ou se a sessão deve ser morta por meio do KILL comando. Para obter mais informações, consulte KILL (Transact-SQL).

Cenário Tipo de espera Open_Tran Status Resolve? Outros sintomas
1 NÃO NULO >= 0 executável Sim, quando a consulta terminar. Em sys.dm_exec_sessions, , cpu_timereadse/ou memory_usage colunas aumentarão com o tempo. A duração da consulta será alta quando concluída.
2 NULO >0 dormir Não, mas o SPID pode ser morto. Um sinal de atenção pode ser visto na sessão de Evento Estendido para este SPID, indicando que ocorreu um tempo limite de consulta ou cancelamento.
3 NULO >= 0 executável Não Não será resolvido até que o cliente busque todas as linhas ou feche a conexão. SPID pode ser morto, mas pode levar até 30 segundos. Se open_transaction_count = 0 e o SPID mantiver bloqueios enquanto o nível de isolamento da transação for padrão (READ COMMMITTED), essa é uma causa provável.
4 Varia >= 0 executável Não Não será resolvido até que o cliente cancele consultas ou feche conexões. Os SPIDs podem ser mortos, mas podem levar até 30 segundos. A hostname coluna para sys.dm_exec_sessions o SPID na cabeça de uma cadeia de bloqueio será a mesma que uma do SPID que está bloqueando.
5 NULO >0 reversão Sim. Um sinal de atenção pode ser visto na sessão Eventos estendidos para este SPID, indicando que ocorreu um tempo limite de consulta ou cancelamento, ou simplesmente uma instrução de reversão foi emitida.
6 NULO >0 dormir Eventualmente. Quando o Windows NT determinar que a sessão não está mais ativa, a conexão do Banco de dados SQL do Azure será interrompida. O last_request_start_time valor em sys.dm_exec_sessions é muito anterior ao tempo atual.

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 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 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 Monitorar e ajustar o desempenho.

    Os relatórios do Repositório de Consultas 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. Analise também a seção Desempenho Inteligente do portal do Azure para o banco de dados SQL do Azure, incluindo o Query Performance Insight.

    Se a consulta executar apenas operações SELECT, considere executar a instrução sob 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) ao nível de isolamento de instantâneos. Além disso, o isolamento de instantâneo fornece consistência de nível de transação para todas as instruções em uma transação explícita de várias instruções. 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 você tiver uma consulta de longa execução 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 síncrona somente leitura do banco de dados.

  2. Bloqueio causado por um SPID em suspensão que tem uma transação não confirmada

    Esse tipo de bloqueio geralmente pode ser identificado por um SPID que está em suspensão ou aguardando um comando, mas cujo nível de aninhamento de transação (@@TRANCOUNT, open_transaction_count de sys.dm_exec_requests) é maior que zero. 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 SPID recebe um tempo limite de consulta ou um cancelamento, ele encerra a consulta e o lote atuais, mas não reverte ou confirma automaticamente a transação. O aplicativo é responsável por isso, pois o 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 tempo limite de consulta ou cancelamento aparecerá como um evento de sinal ATTENTION para o SPID na sessão de Evento Estendido.

    Para demonstrar uma transação explícita não confirmada, emita a seguinte consulta:

    CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
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 indica que o nível de aninhamento 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.

    Use o script anteriormente neste artigo com base em sys.dm_tran_active_transactions para identificar transações atualmente não confirmadas 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 busque 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 da análise do desempenho da consulta.

    • Os aplicativos devem gerenciar adequadamente os níveis de aninhamento de transações, ou eles 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 seguindo 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 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 para o pool, como um aplicativo baseado na Web, desabilitar temporariamente o pool de conexões pode ajudar a aliviar o problema até que o aplicativo cliente seja modificado para lidar com os erros adequadamente. Ao desabilitar o pool de conexões, liberar a conexão causará uma desconexão física da conexão do Banco de Dados SQL do Azure, resultando na reversão de todas as transações abertas pelo servidor.
      • Use SET XACT_ABORT ON para a conexão ou em quaisquer procedimentos armazenados que iniciam transações e não são limpos após um erro. No caso de um erro em tempo de execução, essa configuração anulará todas as transações abertas e retornará o controle para o cliente. Para obter mais informações, consulte SET XACT_ABORT (Transact-SQL).

    Nota

    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 serão executadas. Isso pode afetar o fluxo pretendido do código existente.

  3. Bloqueio causado por um SPID cujo aplicativo cliente correspondente não buscou todas as linhas de resultados até a conclusão

    Depois de enviar uma consulta para o servidor, todas as aplicações têm de concluir imediatamente todas as linhas de resultados. Se um aplicativo não buscar todas as linhas de resultados, bloqueios podem ser deixados nas tabelas, bloqueando outros usuários. 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 não o fizer (e se não puder ser configurado para o fazer), poderá não conseguir resolver o problema de bloqueio. Para evitar o problema, pode restringir as aplicações com desempenho fraco a uma base de dados de relatórios ou de suporte a decisões, separada da base de dados OLTP principal.

    O impacto deste cenário é reduzido quando o instantâneo consolidado de leitura está ativado na base de dados, que é a configuração predefinida na Base de Dados SQL do Azure. Saiba mais na seção Entender o bloqueio deste artigo.

    Nota

    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 até a conclusão. Isso não exclui o uso de OFFSET e FETCH na cláusula ORDER BY de uma consulta para executar a paginação do lado do servidor.

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

    Uma consulta de modificação de dados que é KILLed, 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 vítima de deadlock. Isso geralmente pode ser identificado observando a saída do sys.dm_exec_requests, que pode indicar o comando ROLLBACK, e a coluna pode mostrar o percent_complete progresso.

    Graças ao recurso de recuperação acelerada de banco de dados introduzido em 2019, reversões longas devem ser raras.

    Resolução: aguarde até que o SPID 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 imediatamente cancelada em algumas condições. Do ponto de vista da Base de Dados SQL do Azure, o cliente ainda parece estar presente e quaisquer bloqueios adquiridos podem manter-se. 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 o SPID usando o KILL comando. O KILL comando usa o valor SPID como entrada. Por exemplo, para matar SPID 99, emita o seguinte comando:

    KILL 99

Consulte também

Próximos passos