Partilhar via

Analise e previna bloqueios no Banco de Dados SQL do Azure e no banco de dados SQL Fabric

  • Artigo

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

Este artigo ensina como identificar deadlocks, usar gráficos de deadlock e Query Store para identificar as consultas no deadlock e planejar e testar alterações para evitar que deadlocks voltem a ocorrer. Este artigo aplica-se à Base de Dados SQL do Azure e à Base de Dados SQL Fabric, que partilha muitas funcionalidades da Base de Dados SQL do Azure.

Este artigo se concentra em identificar e analisar impasses devido à contenção de bloqueio. Saiba mais sobre outros tipos de impasses em recursos que podem causar bloqueios em.

Como ocorrem os impasses

Cada novo banco de dados no Banco de Dados SQL do Azure tem a configuração de banco de dados RCSI (de isolamento de instantâneo confirmado de leitura) habilitada por padrão. O bloqueio de entre sessões de leitura de dados e sessões de gravação de dados é minimizado no RCSI, que usa o versionamento de linhas para aumentar a simultaneidade. No entanto, bloqueios e deadlocks 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 por meio de métodos de biblioteca de cliente, dicas de consultaou SET TRANSACTION ISOLATION LEVEL no Transact-SQL.

  • RCSI pode estar desativado, fazendo com que o banco de dados use bloqueios compartilhados (S) para proteger SELECT instruções executadas sob o nível de isolamento confirmado de leitura. Isso poderia aumentar os bloqueios e os impasses.

Um exemplo de impasse

Um impasse ocorre quando duas ou mais tarefas bloqueiam indefinidamente umas às outras porque cada tarefa tem um bloqueio sobre um recurso que as outras estão tentando bloquear. Um impasse também é chamado de dependência cíclica: no caso de um impasse de duas tarefas, a transação A tem uma dependência da transação B, e a transação B fecha o círculo por ter uma dependência da transação A.

Por exemplo:

  1. Sessão A inicia uma transação explícita e executa uma instrução de atualização que adquire um bloqueio de atualização (U) numa linha da tabela SalesLT.Product que é convertida num bloqueio exclusivo (X).

  2. Sessão B executa uma instrução update que modifica a tabela SalesLT.ProductDescription. A instrução update se une à tabela SalesLT.Product para encontrar as linhas corretas a serem atualizadas.

    • A sessão B adquire um bloqueio de atualização (U) em 72 linhas da tabela SalesLT.ProductDescription.

    • Sessão B precisa de um bloqueio compartilhado em linhas na tabela SalesLT.Product, incluindo a linha que está bloqueada por Sessão A. Sessão B está bloqueada no SalesLT.Product.

  3. Sessão A continua sua transação e agora executa uma atualização na tabela SalesLT.ProductDescription. Sessão A é bloqueada pela Sessão B em SalesLT.ProductDescription.

Diagrama mostrando duas sessões em um deadlock. Cada sessão possui um recurso que o outro processo precisa para continuar.

Todas as transações em um impasse aguardam indefinidamente, a menos que uma das transações participantes seja revertida, por exemplo, porque sua sessão foi encerrada.

O monitor de interbloqueio do mecanismo de banco de dados verifica periodicamente as tarefas que estão num interbloqueio. Se o monitor de deadlock detetar uma dependência cíclica, ele escolhe uma das tarefas como vítima e encerra sua transação com o erro 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. Quebrar o impasse dessa forma permite que a outra tarefa ou tarefas no deadlock concluam suas transações.

Observação

Saiba mais sobre os critérios para escolher uma vítima de deadlock na seção Deadlock process list deste artigo.

Diagrama de um impasse entre duas sessões. Uma sessão é escolhida como a vítima do impasse.

O aplicativo com a transação escolhida como vítima do deadlock deve repetir a transação, que geralmente é concluída após a conclusão da outra transação ou transações envolvidas no deadlock.

É uma prática recomendada introduzir um atraso curto e aleatório antes de tentar novamente para evitar encontrar o mesmo impasse novamente. Saiba mais sobre como criar lógica de repetição para erros transitórios.

Nível de isolamento padrão no Banco de Dados SQL do Azure

As novas bases de dados no Azure SQL Database ativam por padrão o Read Committed Snapshot (RCSI). O RCSI altera o comportamento do ler o nível de isolamento comprometido para usar de controle de versão de linha para fornecer consistência no nível da instrução sem o uso de bloqueios compartilhados (S) para instruções SELECT.

Com o RCSI ativado:

  • As instruções que leem dados não bloqueiam as instruções que modificam dados.
  • As instruções que modificam dados não bloqueiam a leitura de dados.

O nível de isolamento de instantâneo também está ativado por padrão para novos bancos de dados no Azure SQL Database. O isolamento instantâneo é um nível de isolamento adicional baseada em linhas que garante consistência a nível de transações 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. Apenas é possível quando o isolamento por instantâneo está 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 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 ON.

Se RCSI estiver desabilitado para um banco de dados no Banco de Dados SQL do Azure, investigue por que o RCSI foi desabilitado antes de reativá-lo. O código da aplicação pode esperar que as consultas que lêem dados sejam bloqueadas por consultas que gravam dados, resultando em resultados incorretos das condições de corrida quando o RCSI está ativado.

Interpretar eventos de deadlock

Um evento de deadlock é emitido depois que o gerenciador de deadlock no Banco de Dados SQL do Azure deteta um deadlock e seleciona uma transação como vítima. Em outras palavras, se configurares alertas para bloqueios, a notificação será acionada depois que um bloqueio individual for resolvido. Não há nenhuma ação do usuário que precise ser tomada para esse impasse. Os aplicativos devem ser gravados para incluir lógica de repetição para que continuem automaticamente após receber o erro 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

No entanto, é útil configurar alertas, pois os impasses podem voltar a ocorrer. Os alertas de deadlock permitem que você investigue se um padrão de deadlocks repetidos está acontecendo em seu banco de dados, caso em que você pode optar por tomar medidas para evitar que os deadlocks voltem a ocorrer. Saiba mais sobre alertas na secção Monitor e sobre o alerta de bloqueios na secção deste artigo.

Principais métodos para evitar impasses

A abordagem de menor risco para evitar que os deadlocks voltem a ocorrer é geralmente ajustar os índices não agrupados para otimizar as consultas envolvidas no deadlock.

  • O risco é baixo para essa abordagem porque o ajuste de índices não clusterizados não requer alterações no próprio código de consulta, reduzindo o risco de um erro do usuário ao reescrever Transact-SQL que faz com que dados incorretos sejam retornados ao usuário.

  • O ajuste eficaz do índice não clusterizado ajuda as consultas a encontrar os dados para ler e modificar com mais eficiência. Ao reduzir a quantidade de dados que uma consulta precisa acessar, a probabilidade de bloqueio é reduzida e os bloqueios muitas vezes podem ser evitados.

Em alguns casos, criar ou ajustar um índice agrupado pode reduzir bloqueios e deadlocks. Como o índice clusterizado está incluído em todas as definições de índice não clusterizado, criar ou modificar um índice clusterizado pode ser uma operação intensiva e demorada de E/S em tabelas maiores com índices não clusterizados existentes. Saiba mais sobre diretrizes de design de índice clusterizado.

Quando o ajuste do índice não é bem-sucedido na prevenção de deadlocks, outros métodos estão disponíveis:

  • Se o bloqueio ocorrer apenas quando um plano específico for escolhido para uma das consultas envolvidas no bloqueio, forçar um plano de consulta com o Query Store pode impedir que os bloqueios ocorram novamente.

  • Reescrever Transact-SQL para uma ou mais transações envolvidas no impasse também pode ajudar a evitar bloqueios. Dividir transações explícitas em transações menores requer codificação e testes cuidadosos para garantir a validade dos dados quando ocorrerem modificações simultâneas.

Saiba mais sobre cada uma dessas abordagens na seção Impedir que um impasse se repita deste artigo.

Monitorar e alertar sobre impasses

Neste artigo, usamos o banco de dados de exemplo AdventureWorksLT para configurar alertas para deadlocks, causar um exemplo de deadlock, analisar o gráfico de deadlock para o deadlock de exemplo e testar alterações para evitar que o deadlock volte a ocorrer.

Usamos o cliente SQL Server Management Studio (SSMS) neste artigo, pois ele contém funcionalidade para exibir gráficos de deadlock em um modo visual interativo. Você pode usar outros clientes, como do Azure Data Studio, para acompanhar os exemplos, mas talvez só consiga exibir gráficos de deadlock como XML.

Criar o banco de dados AdventureWorksLT

Para acompanhar os exemplos, crie um novo banco de dados no Banco de Dados SQL do Azure e selecione dados de de exemplo como a fonte de dados .

Para obter instruções detalhadas sobre como criar AdventureWorksLT com o portal do Azure, a CLI do Azure ou o PowerShell, selecione a abordagem de sua escolha em Guia de início rápido: criar um banco de dados único do Banco de Dados SQL do Azure.

Configurar alertas de deadlock no portal do Azure

Para configurar alertas para eventos de deadlock, siga as etapas no artigo Criar alertas para o Banco de Dados SQL do Azure e o Azure Synapse Analytics usando o portal do Azure.

Selecione Deadlocks como o nome do sinal para o alerta. Configure o do grupo Ação para notificá-lo usando o método de sua escolha, como o tipo de ação Email/SMS/Push/Voice.

Recolher gráficos de deadlock na Base de Dados SQL do Azure com Eventos Estendidos

Os gráficos de impasse são uma fonte rica de informações sobre os processos e bloqueios envolvidos em um impasse. Para recolher gráficos de impasse com Eventos Estendidos (XEvents) na Base de Dados SQL do Azure, capture o evento sqlserver.database_xml_deadlock_report.

Você pode recolher gráficos de deadlock com XEvents usando o destino do buffer de anel ou um destino de arquivo de evento . As considerações para selecionar o tipo de destino apropriado estão resumidas na tabela a seguir:

Abordagem Benefícios Considerações Cenários de utilização
Alvo do buffer de anel - Configuração simples apenas com Transact-SQL. - Os dados do evento são limpos quando a sessão XEvents é interrompida por qualquer motivo, como colocar o banco de dados offline ou um failover de banco de dados.

- Os recursos de banco de dados são usados para manter os dados no buffer de anel e para consultar os dados da sessão.		 - Coletar dados de rastreamento de amostra para testes e aprendizagem.

- Crie para necessidades de curto prazo se você não pode configurar uma sessão usando um destino de arquivo de evento imediatamente.

- Use como uma plataforma de aterrissagem para dados de rastreamento, quando você tiver configurado um processo automatizado para persistir dados de rastreamento em uma tabela.
Destino do arquivo de evento - Persiste os dados do evento em um blob no Armazenamento do Azure para que os dados fiquem disponíveis mesmo depois que a sessão for interrompida.

- Os arquivos de evento podem ser baixados do portal do Azure ou do Gerenciador de Armazenamento do Azure e analisados localmente, o que não requer o uso de recursos de banco de dados para consultar dados de sessão.		 - A instalação é mais complexa e requer a configuração de um contêiner de Armazenamento do Azure e uma credencial com escopo de banco de dados. - Uso geral quando você deseja que os dados do evento persistam mesmo após a interrupção da sessão do evento.

- Você deseja executar um rastreamento que gera quantidades maiores de dados de eventos do que gostaria de persistir na memória.

Selecione o tipo de destino que você gostaria de usar:

O alvo do buffer de anel é conveniente e fácil de configurar, mas tem uma capacidade limitada, o que pode causar a perda de eventos mais antigos. O buffer de anel não persiste eventos para armazenamento e o alvo do buffer de anel é limpo quando a sessão XEvents é interrompida. Isso significa que quaisquer XEvents coletados não estarão disponíveis quando o mecanismo de base de dados for reiniciado por qualquer motivo, como uma comutação por erro. O destino do buffer de anel é mais adequado para necessidades de aprendizagem e de curto prazo se você não tiver a capacidade de configurar uma sessão XEvents para um destino de arquivo de evento imediatamente.

Este código de exemplo cria uma sessão de _XEvents_ que captura gráficos de deadlock na memória usando o alvo do anel de buffer . A memória máxima permitida para o destino do buffer circular é de 4 MB, e a sessão inicia automaticamente quando o banco de dados está disponível, como após uma transição em caso de falha.

Para criar e iniciar uma sessão XEvents para o evento sqlserver.database_xml_deadlock_report que grava no destino do buffer de anel, conecte-se ao banco de dados e execute o seguinte Transact-SQL:

CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO

Provocar um impasse no AdventureWorksLT

Observação

Este exemplo funciona na base de dados AdventureWorksLT com o esquema e os dados predefinidos quando o RCSI está ativado. Consulte Criar o banco de dados AdventureWorksLT para obter instruções sobre como criar o banco de dados.

Para causar um impasse, você precisa conectar duas sessões ao banco de dados AdventureWorksLT. Referimo-nos a estas sessões como Sessão A e Sessão B.

Na Sessão A, execute o seguinte código Transact-SQL. O código inicia uma transação explícita e executa uma única instrução que atualiza a tabela SalesLT.Product. Para tal, a transação adquire um bloqueio de atualização (U) numa linha na tabela SalesLT.Product que é convertido para um bloqueio exclusivo (X). Deixamos a transação em aberto.

BEGIN TRANSACTION;

UPDATE SalesLT.Product
SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

Agora, no Sessão B, execute o seguinte Transact-SQL. Este código não inicia explicitamente uma transação. Em vez disso, ele opera em modo de transação de confirmação automática. Esta declaração atualiza a tabela SalesLT.ProductDescription. A atualização elimina um bloqueio de atualização (U) em 72 linhas na tabela SalesLT.ProductDescription. A consulta se une a outras tabelas, incluindo a tabela SalesLT.Product.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

Para concluir esta atualização, Sessão B necessita de um bloqueio partilhado (S) sobre as linhas da tabela SalesLT.Product, incluindo a linha que está bloqueada por Sessão A. Sessão B está bloqueada em SalesLT.Product.

Volte à sessão A. Execute a instrução seguinte Transact-SQL. Isso executa uma segunda instrução UPDATE como parte da transação aberta.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

A segunda instrução de atualização na Sessão A é bloqueada pela Sessão B no SalesLT.ProductDescription.

Sessão A e Sessão B estão agora a bloquear-se mutuamente. Nenhuma transação pode prosseguir, pois cada uma precisa de um recurso bloqueado pela outra.

Após alguns segundos, o monitor de deadlock identifica que as transações na Sessão A e na Sessão B estão a bloquear-se mutuamente e que nenhuma delas pode progredir. Você deve ver um impasse ocorrer, com Sessão A escolhida como a vítima do impasse. É apresentada uma mensagem de erro na Sessão A com um texto semelhante ao seguinte:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Sessão B é concluída com êxito.

Se você configurar alertas de deadlock no portal do Azure, deverá receber uma notificação logo após o deadlock ocorrer.

Exibir gráficos de deadlock de uma sessão do XEvents

Se você configurar uma sessão XEvents para coletar deadlocks e um deadlock ocorrer após o início da sessão, você poderá exibir uma exibição gráfica interativa do gráfico de deadlock e o XML para o gráfico de deadlock.

Diferentes métodos estão disponíveis para obter informações de deadlock para o destino do buffer de anel e destinos de arquivo de evento. Selecione o destino que você usou para sua sessão XEvents:

Se configurar uma sessão de XEvents gravando no buffer circular, poderá consultar informações sobre deadlocks com o seguinte Transact-SQL. Antes de executar a consulta, substitua o valor de @tracename pelo nome da sessão XEvents.

DECLARE @tracename AS sysname = N'deadlocks';

WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'ring_buffer'),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Exibir e salvar um gráfico de deadlock em XML

A visualização de um gráfico de deadlock em formato XML permite copiar a inputbuffer de Transact-SQL instruções envolvidas no deadlock. Pode também analisar impasses num formato baseado em texto.

Se você tiver usado uma consulta Transact-SQL para retornar informações do gráfico de deadlock, para exibir o XML do gráfico de deadlock, selecione o valor na coluna deadlock_xml de qualquer linha para abrir o XML do gráfico de deadlock em uma nova janela no SSMS.

O XML para este exemplo de gráfico de deadlock é:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Silver';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Para salvar o gráfico de deadlock como um arquivo XML:

  1. Selecione Arquivo e Salvar como....
  2. Deixe o valor de Guardar como tipo como o predefinido Ficheiros XML (*.xml)
  3. Defina o Nome do arquivo para o nome de sua escolha.
  4. Selecione Salvar.

Salvar um gráfico de deadlock como um arquivo XDL que pode ser exibido interativamente no SSMS

A visualização de uma representação interativa de um gráfico de deadlock pode ser útil para obter uma visão geral rápida dos processos e recursos envolvidos em um deadlock e identificar rapidamente a vítima de deadlock.

Para salvar um gráfico de deadlock como um arquivo que pode ser exibido graficamente pelo SSMS:

  1. Selecione o valor de qualquer linha na coluna deadlock_xml para abrir o XML do gráfico de impasse numa nova janela no SSMS.

  2. Selecione Arquivo e Guardar como....

  3. Defina Salvar como tipo para Todos os arquivos.

  4. Defina o Nome do arquivo para o nome de sua escolha, com a extensão definida como .xdl.

  5. Selecione Salvar.

    Captura de tela no SSMS de salvar um arquivo XML de gráfico de deadlock em um arquivo com a extensão xsd.

  6. Feche o ficheiro selecionando o X no separador na parte superior da janela, ou selecionando Ficheiroe, em seguida, Fechar.

  7. Reabra o ficheiro no SSMS selecionando Arquivo, depois Abrire, em seguida, Ficheiro. Selecione o arquivo que você salvou com a extensão .xdl.

    O gráfico de deadlock agora é exibido no SSMS com uma representação visual dos processos e recursos envolvidos no deadlock.

    Captura de ecrã de um ficheiro XDL aberto no SSMS. O gráfico de interbloqueio é apresentado de forma gráfica, com processos indicados por ovais e os recursos de bloqueio representados como retângulos.

Analisar um impasse da Base de Dados SQL do Azure

Um gráfico de deadlock normalmente tem três nós:

  • Lista de vítimas. O identificador do processo da vítima de deadlock.

  • Lista de processos. Informações sobre todos os processos envolvidos no impasse. Os gráficos de deadlock usam o termo 'processo' para representar uma sessão executando uma transação.

  • Lista de recursos. Informações sobre os recursos envolvidos no impasse.

Ao analisar um impasse, é útil percorrer esses nós.

Lista de vítimas de impasse

A lista de vítimas de deadlock mostra o processo que foi escolhido como a vítima de deadlock. Na representação visual de um gráfico de deadlock, os processos são representados por ovais. O processo de vítima de impasse tem um "X" desenhado sobre o oval.

Captura de tela da exibição visual de um deadlock. O oval que representa o processo selecionado como vítima tem um X desenhado sobre ele.

Na visualização XML de um gráfico de deadlock, o nó victim-list fornece uma ID para o processo que foi vítima do deadlock.

No nosso exemplo de deadlock, o ID do processo da vítima é process24756e75088. Podemos usar esse ID ao examinar os nós de lista de processos e lista de recursos para saber mais sobre o processo da vítima e os recursos que ele estava bloqueando ou solicitando para bloquear.

Lista de processos de bloqueio

A lista de processos de impasse é uma fonte rica de informações sobre as transações envolvidas no impasse.

A representação gráfica do grafo de deadlock mostra apenas um subconjunto das informações contidas no XML do grafo de deadlock. As elipses no gráfico de impasse representam o processo e mostram informações, incluindo:

  • ID da sessão, também conhecido como SPID.

  • Prioridade de bloqueio da sessão. Se duas sessões tiverem prioridades de bloqueio diferentes, a sessão com a prioridade mais baixa será escolhida como a vítima do impasse. Neste exemplo, ambas as sessões têm a mesma prioridade de bloqueio.

  • A quantidade de log de transações usado pela sessão em bytes. Se ambas as sessões tiverem a mesma prioridade de deadlock, o monitor de deadlock escolhe a sessão que é menos custosa de reverter como vítima de deadlock. O custo é determinado comparando o número de bytes de log gravados até esse ponto em cada transação.

    Em nosso exemplo de deadlock, session_id 89 usou uma quantidade menor de log de transações e foi selecionado como a vítima do deadlock.

Além disso, você pode visualizar o do buffer de entrada para a última instrução executada em cada sessão antes do deadlock passando o mouse sobre cada processo. O buffer de entrada aparece em uma dica de ferramenta.

Imagem de um gráfico de deadlock exibido no SSMS. Dois ovais representam processos. O buffer de entrada para um processo é mostrado.

Informações adicionais para processos estão disponíveis na visualização XML do gráfico de impasse, incluindo:

  • Informações de identificação da sessão, como o nome do cliente, o nome do host e o nome de login.

  • O hash do plano de consulta para o último comando executado por cada sessão antes do deadlock. O hash do plano de consulta é útil para recuperar mais informações sobre a consulta do Repositório de Consultas.

No nosso exemplo de impasse:

  • Podemos ver que ambas as sessões foram executadas usando o cliente SSMS sob o login chrisqpublic.

  • O hash do plano de consulta, da última instrução executada antes do deadlock por nossa vítima de deadlock, é 0x02b0f58d7730f798. Podemos ver o texto desta instrução no buffer de entrada.

  • O hash do plano de consulta, da última instrução executada pela outra sessão em nosso deadlock, também é 0x02b0f58d7730f798. Podemos ver o texto desta instrução no buffer de entrada. Nesse caso, ambas as consultas têm o mesmo hash de plano de consulta porque as consultas são idênticas, exceto por um valor literal usado como um predicado de igualdade.

Usamos esses valores posteriormente neste artigo para encontrar informações adicionais no Query Store.

Limitações do buffer de entrada na lista de processos de deadlock

Existem algumas limitações a ter em conta relativamente às informações do buffer de entrada na lista de processos de deadlock.

O texto da consulta pode ser truncado no buffer de entrada. O buffer de entrada é limitado aos primeiros 4.000 caracteres da instrução que está sendo executada.

Além disso, algumas declarações envolvidas no impasse podem não ser incluídas no gráfico de impasse. Em nosso exemplo, Sessão A executou duas instruções de atualização em uma única transação. Apenas a segunda instrução de atualização, sendo esta a que causou o deadlock, é incluída no diagrama de deadlock. A primeira declaração de atualização executada pela Sessão A desempenhou um papel no impasse ao bloquear a Sessão B. O buffer de entrada, query_hash, e as informações relacionadas para a primeira instrução executada pela Sessão A não estão incluídos no gráfico de deadlock.

Para identificar o Transact-SQL completo executado em uma transação de várias instruções envolvida em um deadlock, você precisa encontrar as informações relevantes no procedimento armazenado ou no código do aplicativo que executou a consulta ou executar um rastreamento usando Extended Events para capturar instruções completas executadas por sessões envolvidas em um deadlock enquanto ele ocorre. Se uma instrução envolvida no deadlock estiver truncada e apenas Transact-SQL parcial aparecer no buffer de entrada, você poderá encontrar o Transact-SQL da instrução no Repositório de Consultas com o Plano de Execução.

Lista de recursos de impasse

A lista de recursos de deadlock mostra quais recursos de bloqueio são de propriedade e aguardados pelos processos no deadlock.

Os recursos são representados por retângulos na representação visual do impasse:

Captura de tela de um gráfico de deadlock, exibido visualmente no SSMS. Os retângulos mostram os recursos envolvidos no impasse.

Observação

Os nomes de bases de dados são representados como GUIDs (uniqueidentifier) em gráficos de interbloqueio para bases de dados no Azure SQL Database. Este é o physical_database_name para a base de dados listada no sys.databases e no sys.dm_user_db_resource_governance vistas de gestão dinâmica.

Neste exemplo de impasse:

  • A vítima do impasse, a que nos referimos como Sessão A,:

    • Possui um bloqueio exclusivo (X) sobre uma chave no índice PK_Product_ProductID na tabela SalesLT.Product.

    • Solicita um bloqueio de atualização (U) em uma chave no índice de PK_ProductDescription_ProductDescriptionID na tabela SalesLT.ProductDescription.

  • O outro processo, a que chamámos Sessão B:

    • Detém um bloqueio de atualização (U) numa chave no índice PK_ProductDescription_ProductDescriptionID na tabela SalesLT.ProductDescription.

    • Solicita um bloqueio partilhado (S) numa chave no índice PK_ProductDescription_ProductDescriptionID na tabela SalesLT.ProductDescription.

Podemos ver as mesmas informações no XML do gráfico de deadlock no nó da lista de recursos .

Localizar planos de execução de consulta no Repositório de Consultas

Muitas vezes, é útil examinar os planos de execução de consulta para instruções envolvidas no deadlock. Esses planos de execução geralmente podem ser encontrados no Query Store usando o hash do plano de consulta da lista de processos da visualização XML do gráfico de deadlock.

Esta consulta Transact-SQL procura planos de consulta correspondentes ao hash do plano de consulta que encontramos para o nosso exemplo de deadlock. Conecte-se ao banco de dados de usuário no Banco de Dados SQL do Azure para executar a consulta.

DECLARE @query_plan_hash AS BINARY (8) = 0x02b0f58d7730f798;

SELECT qrsi.end_time AS interval_end_time,
       qs.query_id,
       qp.plan_id,
       qt.query_sql_text,
       TRY_CAST (qp.query_plan AS XML) AS query_plan,
       qrs.count_executions
FROM sys.query_store_query AS qs
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_runtime_stats_interval AS qrsi
         ON qrs.runtime_stats_interval_id = qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

Talvez não seja possível obter um plano de execução de consulta a partir do Repositório de Consultas, dependendo das suas configurações de CLEANUP_POLICY ou QUERY_CAPTURE_MODE. Nesse caso, muitas vezes pode-se obter as informações necessárias exibindo o plano de execução estimado para a consulta.

Procure padrões que aumentem o bloqueio

Ao examinar os planos de execução de consultas envolvidos em deadlocks, procure padrões que possam contribuir para bloqueios e deadlocks.

  • Varreduras de tabela ou de índice. Quando consultas que modificam dados são executadas sob o RCSI, a seleção de linhas a serem atualizadas é feita usando uma varredura de bloqueio, na qual um bloqueio de atualização (U) é feito na linha de dados à medida que os valores de dados são lidos. Se a linha de dados não atender aos critérios de atualização, o bloqueio de atualização será liberado e a próxima linha será bloqueada e verificada.

    Ajustar índices para ajudar as consultas de modificação a encontrar linhas de forma mais eficiente reduz o número de bloqueios de atualização emitidos. Isso reduz as chances de bloqueio e impasses.

  • Exibições indexadas que fazem referência a mais de uma tabela. Quando você modifica uma tabela referenciada em um modo de exibição indexado, o mecanismo de banco de dados também deve manter o modo de exibição indexado. Isso requer a remoção de mais bloqueios e pode levar a um aumento de bloqueios e interbloqueios. As vistas indexadas também podem causar que as operações de atualização sejam executadas internamente sob o nível de isolamento de leitura confirmada.

  • Modificações em colunas referenciadas em restrições de chave estrangeira. Quando você modifica colunas em uma tabela que são referenciadas em uma restrição de FOREIGN KEY, o mecanismo de banco de dados deve procurar linhas relacionadas na tabela de referência. As versões de linha não podem ser usadas para essas leituras. Nos casos em que as atualizações ou exclusões em cascata estão habilitadas, o nível de isolamento pode ser escalado para serializável durante a duração da instrução para proteger contra inserções fantasmas.

  • Bloquear dicas. Procure pistas nas tabelas que especificam níveis de isolamento que exigem mais bloqueios. Essas dicas incluem HOLDLOCK (que é equivalente a serializável), SERIALIZABLE, READCOMMITTEDLOCK (que desativa o RCSI) e REPEATABLEREAD. Além disso, dicas como PAGLOCK, TABLOCK, UPDLOCKe XLOCK podem aumentar os riscos de bloqueio e deadlock.

    Se essas dicas estiverem em vigor, pesquise por que elas foram implementadas. Essas dicas podem prevenir situações de concorrência e garantir a validade dos dados. Talvez seja possível deixar essas dicas no lugar e evitar futuros impasses usando um método alternativo na seção Impedir que um impasse volte a ocorrer deste artigo, se necessário.

    Observação

    Saiba mais sobre o comportamento ao modificar dados usando o controle de versão de linha no Guia de bloqueio de transações e controle de versão de linha.

Ao examinar o código completo de uma transação, seja em um plano de execução ou no código de consulta do aplicativo, procure padrões problemáticos adicionais:

  • Interação do usuário em transações. A interação do usuário dentro de uma transação explícita de várias declarações aumenta significativamente a duração das transações. Isso torna mais provável que essas transações se sobreponham e que ocorram bloqueios e impasses.

    Da mesma forma, manter uma transação aberta e consultar um banco de dados ou sistema não relacionado durante a transação aumenta significativamente as chances de bloqueios e deadlocks.

  • Transações acessando objetos em diferentes ordens. Os impasses são menos prováveis de ocorrer quando transações explícitas simultâneas de várias instruções seguem os mesmos padrões e acessam objetos na mesma ordem.

Evitar que um impasse se repita

Há várias técnicas disponíveis para evitar que os impasses voltem a ocorrer, como a otimização de índices, a imposição de planos com o Repositório de Consultas e a modificação de consultas Transact-SQL.

  • Revise o índice agrupado da tabela. A maioria das tabelas se beneficia de índices agrupados, mas muitas vezes, as tabelas são implementadas como pilhas por acidente.

    Uma maneira de verificar se há um índice clusterizado é usando o procedimento armazenado do sistema sp_helpindex. Por exemplo, podemos exibir um resumo dos índices na tabela SalesLT.Product executando a seguinte instrução:

    EXECUTE sp_helpindex 'SalesLT.Product';
GO

    Analise a coluna index_description. Uma tabela pode ter apenas um índice clusterizado. Se um índice clusterizado foi implementado para a tabela, o index_description contém a palavra clustered.

    Se nenhum índice clusterizado estiver presente, a tabela será um heap. Nesse caso, analise se a tabela foi criada intencionalmente como um heap para resolver um problema de desempenho específico. Considere a implementação de um índice agrupado com base nas diretrizes de design de índice agrupado .

    Em alguns casos, criar ou ajustar um índice clusterizado pode reduzir ou eliminar o bloqueio em deadlocks. Em outros casos, você pode empregar uma técnica adicional, como as outras nesta lista.

  • Criar ou modificar índices não clusterizados. O ajuste de índices não clusterizados pode ajudar suas consultas de modificação a encontrar os dados a serem atualizados mais rapidamente, o que reduz o número de bloqueios de atualização necessários.

    No nosso exemplo de deadlock, o plano de execução de consulta encontrado no Repositório de Consultas contém uma análise de índice clusterizado em relação ao índice PK_Product_ProductID. O gráfico de deadlock indica que a espera por um bloqueio partilhado (S) sobre este índice é um elemento do deadlock.

    Captura de tela de um plano de execução de consulta. Uma verificação de índice clusterizado está sendo executada em relação ao índice PK_Product_ProductID na tabela Produto.

    Essa verificação de índice está sendo executada porque nossa consulta de atualização precisa modificar uma exibição indexada chamada vProductAndDescription. Como mencionado na seção Procurar padrões que aumentem o bloqueio deste artigo, as visualizações indexadas que fazem referência a várias tabelas podem aumentar o bloqueio e a probabilidade de bloqueios.

    Se criarmos o seguinte índice não clusterizado no banco de dados AdventureWorksLT que "cobre" as colunas de SalesLT.Product referenciadas pelo modo de exibição indexado, isso ajuda a consulta a localizar linhas com muito mais eficiência:

    CREATE INDEX IX_Product_ProductID_Name_ProductModelID
    ON SalesLT.Product(ProductID, Name, ProductModelID);
GO

    Depois de criar esse índice, o impasse não ocorre mais.

    Quando os deadlocks envolverem modificações em colunas referenciadas em restrições de chave estrangeira, certifique-se de que os índices na tabela de referência do FOREIGN KEY suportem a localização eficiente de linhas relacionadas.

    Embora os índices possam melhorar drasticamente o desempenho da consulta em alguns casos, os índices também têm custos gerais e de gerenciamento. Revise diretrizes gerais de design de índice para ajudar a avaliar o benefício dos índices antes de criar índices, especialmente índices amplos e índices em tabelas grandes.

  • Avaliar o valor das vistas indexadas. Outra opção para evitar que o nosso exemplo de deadlock se repita é eliminar a exibição indexada SalesLT.vProductAndDescription. Se esse modo de exibição indexado não estiver sendo usado, isso reduzirá a sobrecarga de manter o modo de exibição indexado ao longo do tempo.

  • Use o isolamento de instantâneo. Em alguns casos, definir o nível de isolamento da transacção para instantâneo em uma ou mais transações envolvidas num deadlock pode impedir que o bloqueio e os deadlocks voltem a ocorrer.

    É mais provável que essa técnica seja bem-sucedida quando usada em instruções SELECT quando instantâneo de leitura confirmada está desativado num banco de dados. Quando o instantâneo de leitura confirmada é desativado, as consultas SELECT que usam o nível de isolamento de leitura confirmada exigem bloqueios partilhados (S). O uso do isolamento de instantâneos nessas transações elimina a necessidade de bloqueios compartilhados, o que pode prevenir bloqueios e impasses.

    Em bases de dados onde o isolamento por instantâneo de leitura confirmada está ativado, nas consultas SELECT não são necessários bloqueios compartilhados (S), portanto, é mais provável que ocorram deadlocks entre transações que estão a modificar dados. Nos casos em que ocorrem impasses entre várias transações modificando dados, o isolamento instantâneo pode resultar em conflito de atualização em vez de um impasse. Da mesma forma, isso requer que uma das transações tente novamente sua operação.

  • Forçar um plano com o Repositório de Consultas. Você pode achar que uma das consultas no deadlock tem vários planos de execução, e o deadlock só ocorre quando um plano específico é usado. Você pode evitar que o bloqueio ocorra novamente ao forçar um plano no Repositório de Consultas.

  • Modificar o código Transact-SQL. Talvez seja necessário modificar Transact-SQL para evitar que o deadlock ocorra novamente. A modificação de Transact-SQL deve ser feita com cuidado e as alterações devem ser rigorosamente testadas para garantir que os dados estejam corretos quando as modificações são executadas simultaneamente. Ao reescrever o Transact-SQL, considere:

    • Ordenar instruções em transações para acederem a objetos na mesma ordem.

    • Separar as transações em transações menores quando possível.

    • Usando dicas de consulta, se necessário, para otimizar o desempenho. Você pode aplicar dicas sem alterar o código do aplicativo usando o Repositório de Consultas.

Encontre mais maneiras de minimizar os impasses no guia Deadlocks.

Observação

Em alguns casos, você pode ajustar a prioridade de bloqueio de uma ou mais sessões envolvidas em um impasse se for importante para uma das sessões ser concluída com sucesso sem tentar novamente, ou quando uma das consultas envolvidas no impasse não for crítica e deve ser sempre escolhida como vítima. Embora isso não impeça que o impasse volte a ocorrer, pode reduzir o efeito de impasses futuros.

Encerrar uma sessão XEvents

Você pode sair de uma sessão XEvents coletando informações de deadlock em execução em bancos de dados críticos por longos períodos. Se você usar um destino de arquivo de evento, isso poderá resultar em arquivos grandes se ocorrerem vários deadlocks. Você pode excluir ficheiros de blob do Armazenamento do Azure para um rastreamento ativo, com exceção do ficheiro que está a ser gravado no momento.

Quando pretender remover uma sessão de XEvents, o comando Transact-SQL para eliminar a sessão é o mesmo, independentemente do tipo de destino selecionado.

Para remover uma sessão XEvents, execute o seguinte Transact-SQL. Antes de executar o código, substitua o nome da sessão pelo valor apropriado.

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Usar o Gerenciador de Armazenamento do Azure

O Azure Storage Explorer é uma aplicação autónoma que simplifica o trabalho com destinos de ficheiros de eventos armazenados em blobs no Armazenamento do Azure. Você pode usar o Gerenciador de Armazenamento para:

Baixe o Gerenciador de Armazenamento do Azure.

Conteúdo relacionado