Analisar e prevenir deadlocks no banco de dados SQL do Azure e no banco de dados SQL no Fabric

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

Este artigo ensina como identificar deadlocks no Banco de Dados SQL do Azure, usar gráficos de deadlock e o Repositório de Consultas para identificar as consultas no deadlock, além de planejar e testar alterações para evitar que os deadlocks se repitam. Este artigo se aplica ao Banco de Dados SQL do Azure e ao Banco de Dados SQL no Fabric, que compartilha muitos recursos do Banco de Dados SQL do Azure.

Este artigo aborda como identificar e analisar deadlocks devido à contenção de bloqueio. Saiba mais sobre outros tipos de deadlocks em recursos que podem sofrer deadlock.

Como os deadlocks ocorrem

Cada novo banco de dados no Banco de Dados SQL do Azure tem a configuração de banco de dados RCSI ( isolamento de instantâneo confirmada por leitura ) 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, bloqueios 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.

• 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 consulta ou SET TRANSACTION ISOLATION LEVEL no Transact-SQL.

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

Um exemplo de deadlock

Um deadlock acontece quando duas ou mais tarefas se bloqueiam permanentemente porque uma tarefa está bloqueando um recurso que a outra tarefa está tentando bloquear. O deadlock também é chamado de dependência cíclica: no caso de um deadloak de duas tarefas, a transação A tem uma dependência em relação à transação B, e a transação B fecha o círculo com uma dependência em relação à transação A.

Por exemplo:

1. A 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) em uma linha na tabela SalesLT.Product que é convertida em um bloqueio exclusivo (X).

2. A sessão B executa uma instrução de atualização que modifica a tabela SalesLT.ProductDescription. A instrução de atualização se une à tabela SalesLT.Product para localizar as linhas corretas a serem atualizadas.

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

   • A sessão B precisa de um bloqueio compartilhado nas linhas na tabela SalesLT.Product, incluindo a linha bloqueada pela Sessão A. A sessão B é bloqueada em SalesLT.Product.

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

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

O monitor de deadlock do mecanismo de banco de dados verifica periodicamente se há tarefas em deadlock. Se o monitor de deadlock detectar uma dependência cíclica, ele escolherá uma das tarefas como vítima e encerrará 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 deadlock dessa forma permite que a outra tarefa ou tarefas no deadlock conclua suas transações.

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

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

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

Os novos bancos de dados no Banco de Dados SQL do Azure habilitam o RCSI (instantâneo confirmado por leitura) por padrão. O RCSI altera o comportamento do nível de isolamento confirmado de leitura para usar o controle de versão de linha para fornecer consistência no nível da instrução sem o uso de bloqueios compartilhados (S) para SELECT instruções.

Com o RCSI habilitado:

• Declarações que leem dados não bloqueiam declarações que modificam dados.
• Instruções que fazem modificações nos dados não bloqueiam instruções que fazem leituras desses dados.

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 por instantâneo está habilitado para o 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 RCSI estiver habilitado, a is_read_committed_snapshot_on coluna retornará o valor 1. Se o isolamento do instantâneo estiver habilitado, a snapshot_isolation_state_desc coluna retornará o valor ON.

Se o RCSI estiver desabilitado para um banco de dados no Banco de Dados SQL do Azure, investigue por que o RCSI foi desabilitado antes de habilitá-lo novamente. O código do aplicativo pode esperar que as consultas que leem dados sejam bloqueadas por consultas que gravam dados, resultando em resultados incorretos de condições de corrida quando o RCSI estiver habilitado.

Interpretar eventos de impasse

Um evento deadlock é emitido depois que o gerenciador de deadlock no Banco de Dados SQL do Azure detecta um deadlock e seleciona uma transação como vítima. Em outras palavras, se você configurar alertas para deadlocks, a notificação será acionada após um deadlock individual ser resolvido. Não há nenhuma ação do usuário que precise ser tomada para esse deadlock. Os aplicativos devem ser gravados para incluir a lógica de repetição para que continuem automaticamente após o recebimento do 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 deadlocks podem ocorrer novamente. Os alertas de deadlock permitem que você investigue se um padrão de deadlocks repetidos está acontecendo em seu banco de dados, nesse caso, você pode optar por executar uma ação para evitar que deadlocks se repitam. Saiba mais sobre alertas na seção Monitorar se há deadlocks e emitir alertas deste artigo.

Principais métodos para evitar deadlocks

A abordagem de menor risco para evitar que deadlocks se repitam geralmente é ajustar índices não clusterizados 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 erro do usuário ao reescrever o Transact-SQL que faz com que dados incorretos sejam retornados ao usuário.

• Um ajuste eficaz do índice não clusterizado ajuda as consultas a localizar os dados a serem lidos e modificados com mais eficiência. Ao reduzir a quantidade de dados que uma consulta precisa acessar, a probabilidade de bloqueio é reduzida e os deadlocks geralmente podem ser evitados.

Em alguns casos, a criação ou o ajuste de um índice clusterizado pode reduzir bloqueios e deadlocks. Como o índice clusterizado está incluído em todas as definições de índice não clusterizado, a criação ou a modificação de um índice clusterizado pode ser uma operação demorada e com uso intensivo de E/S em tabelas maiores com índices não clusterizados existentes. Saiba mais em Diretrizes de design de índice clusterizado.

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

• Se o deadlock ocorrer somente quando um plano específico for escolhido para uma das consultas envolvidas no deadlock, usar o Query Store para forçar um plano de consulta poderá impedir que deadlocks se repitam.

• Você também pode reescrever o Transact-SQL para uma ou mais transações envolvidas no deadlock para evitar deadlocks. A divisão de 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 deadlock se repita neste artigo.

Monitorar se há deadlocks e emitir alertas

Neste artigo, usamos o AdventureWorksLT banco de dados de exemplo para configurar alertas para deadlocks, causar um deadlock de exemplo, analisar o grafo de deadlock para o exemplo de deadlock e testar alterações para impedir que o deadlock se repita.

Usamos o cliente SSMS (SQL Server Management Studio) neste artigo, pois ele contém funcionalidade para exibir gráficos de deadlock em um modo visual interativo. Você pode usar outros clientes, como a extensão MSSQL para Visual Studio Code, sqlcmd ou sua ferramenta de consulta de Transact-SQL favorita para acompanhar os exemplos, mas você só poderá exibir grafos deadlock como XML.

Criar o banco de dados AdventureWorksLT

Para acompanhar os exemplos, crie um banco de dados no Banco de Dados SQL do Azure e selecione dados 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 no Início Rápido: Criar um banco de dados individual no 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 Grupo de ações para enviar notificações usando o método da sua escolha, como o tipo de ação Email/SMS/Push/Voz.

Coletar grafos de deadlock no Banco de Dados SQL do Azure com Eventos Estendidos

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

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

Selecione o tipo de destino que você quer usar:

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

IF 0 = (SELECT COUNT(*)
        FROM sys.symmetric_keys
        WHERE symmetric_key_id = 101
              AND name = N'##MS_DatabaseMasterKey##')
    BEGIN
        PRINT N'Creating master key';
        CREATE MASTER KEY;
    END
ELSE
    BEGIN
        PRINT N'Master key already exists, no action taken';
    END
GO

CREATE DATABASE SCOPED CREDENTIAL [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=<sig>';
GO

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
(
    SET filename = 'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
)
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

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

Causar um impasse

Como o bloqueio otimizado está sempre habilitado no Banco de Dados SQL do Azure e no Banco de Dados SQL no Fabric, os deadlocks são menos prováveis. Para obter mais informações e para obter um exemplo de deadlock que pode ocorrer com bloqueio otimizado, consulte Bloqueio otimizado e deadlocks.

Exibir grafos de deadlock de uma sessão de XEvents

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

Há diferentes métodos disponíveis para obter informações de deadlock do destino do buffer de anel e dos destinos do arquivo de evento. Selecione o destino usado para a sessão de 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

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

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

DECLARE @tracename AS sysname = N'deadlocks_eventfile', @filename AS NVARCHAR (2000);

WITH eft
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'event_file')
SELECT @filename = ft.evtdata.value('(@name)[1]', 'nvarchar(2000)')
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') AS ft(evtdata);

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/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

DECLARE @filename AS NVARCHAR (2000) = N'https://<storage-account>.blob.core.windows.net/yourcontainername/yourfilename.xel';

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/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 grafo de deadlock em XML

A exibição de um grafo de deadlock no formato XML permite copiar o inputbuffer das instruções Transact-SQL envolvidas no deadlock. Você também pode analisar deadlocks em um formato baseado em texto.

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

O XML deste exemplo de grafo 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 grafo de deadlock como um arquivo XML:

1. Selecione Arquivo e Salvar Como....
2. Deixe o valor Salvar como tipo como os Arquivos XML (*.xml) padrão
3. Defina o Nome do arquivo para o nome da sua escolha.
4. Selecione Salvar.

Salvar um grafo de deadlock como um arquivo XDL que pode ser exibido interativamente no SSMS

A exibição de uma representação interativa de um grafo 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 do deadlock.

Para salvar um grafo de deadlock como um arquivo que pode ser exibido graficamente pelo SSMS:

1. Selecione o valor na coluna deadlock_xml de qualquer linha para abrir o XML do grafo de deadlock em uma nova janela no SSMS.

2. Selecione Arquivo e Salvar Como....

3. Defina o Tipo em Salvar como para Todos os Arquivos.

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

5. Selecione Salvar.

   

6. Feche o arquivo selecionando o X na guia na parte superior da janela ou selecionando Arquivo e Fechar.

7. Reabra o arquivo no SSMS selecionando Arquivo, Abrir e Arquivo. Selecione o arquivo salvo com a extensão .xdl.

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

   

Analisar um deadlock de um Banco de Dados SQL do Azure

Um grafo de deadlock geralmente tem três nós:

• Victim-list. O identificador de processo da vítima do deadlock.

• Process-list. Informações sobre todos os processos envolvidos no deadlock. Os grafos de deadlock usam o termo 'processo' para representar uma sessão que executa uma transação.

• Resource-list. Informações sobre os recursos envolvidos no deadlock.

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

Lista de vítimas do deadlock

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

Na exibição XML de um grafo de deadlock, o nó victim-list fornece uma ID do processo que foi vítima do deadlock.

Em nosso exemplo de deadlock, a ID do processo da vítima é process24756e75088. Podemos usar essa ID ao examinar os nós process-list e resource-lists para saber mais sobre o processo vítima e os recursos que ele estava bloqueando ou solicitando o bloqueio.

Lista de processos do deadlock

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

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

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

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

• A quantidade de log de transações usada pela sessão em bytes. Se as duas sessões tiverem a mesma prioridade de deadlock, o monitor de deadlock escolherá a sessão mais simples de ser revertida como a vítima do deadlock. O custo é determinado comparando o número de bytes de log gravados naquele 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 vítima de deadlock.

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

Há informações adicionais disponíveis para processos na exibição XML do grafo de deadlock, incluindo:

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

• O hash do plano de consulta para a última instrução executada 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 exemplo de deadlock:

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

• O hash do plano de consulta, da última instrução executada antes do deadlock pela vítima do deadlock, é 0x02b0f58d7730f798. Podemos ver o texto dessa 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 dessa instrução no buffer de entrada. Nesse caso, as duas consultas têm o mesmo hash do plano de consulta porque as consultas são idênticas, com exceção de um valor literal usado como um predicado de igualdade.

Usamos esses valores mais adiante neste artigo para encontrar informações adicionais no Repositório de Consultas.

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

Há algumas limitações a serem observadas em relação à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 mil caracteres da instrução que está sendo executada.

Além disso, algumas instruções envolvidas no deadlock podem não ser incluídas no diagrama de deadlock. No exemplo, a Sessão A executou duas instruções de atualização em uma só transação. Somente a segunda instrução de atualização, que causou o deadlock, está incluída no grafo de deadlock. A primeira instrução de atualização executada pela Sessão A desempenhou um papel no deadlock bloqueando a Sessão B. O buffer query_hashde entrada 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 Eventos Estendidos para capturar instruções completas executadas por sessões envolvidas em um deadlock enquanto ela ocorre. Se uma instrução envolvida no deadlock for truncada e apenas um 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 do deadlock

A lista de recursos do deadlock mostra quais recursos de bloqueio pertencem aos processos no deadlock e são aguardados por eles.

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

Neste exemplo de deadlock:

• A vítima do deadlock, que chamamos de Sessão A:

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

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

• O outro processo, que chamamos de Sessão B:

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

  • Solicita um bloqueio compartilhado (S) em uma chave no índice PK_ProductDescription_ProductDescriptionID na tabela SalesLT.ProductDescription.

Podemos ver as mesmas informações no XML do grafo de deadlock no nó resource-list.

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

Geralmente, é útil examinar os planos de execução de consultas para instruções envolvidas no deadlock. Esses planos de execução geralmente podem ser encontrados no Repositório de Consultas usando o hash do plano de consulta da exibição XML da lista de processos do grafo do deadlock.

Essa consulta Transact-SQL procura planos de consulta que correspondam ao hash do plano de consulta que encontramos para o exemplo de deadlock. Conecte-se ao banco de dados do 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 você não consiga obter um plano de execução de consulta do Repositório de Consultas, dependendo das configurações do CLEANUP_POLICY ou do QUERY_CAPTURE_MODE do Repositório de Consultas. Nesse caso, muitas vezes você pode obter as informações necessárias exibindo o plano de execução estimado da consulta.

Procurar padrões que aumentam o bloqueio

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

• Verificações de tabela ou índice. Quando consultas que modificam dados são executadas no RCSI, a seleção de linhas a serem atualizadas é feita usando uma verificação de bloqueio em que um bloqueio de atualização (U) é feito na linha de dados conforme os valores dos 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.

  O ajuste dos índices para ajudar as consultas de modificação a encontrar linhas com mais eficiência reduz o número de bloqueios de atualização emitidos. Isso reduz as chances de bloqueios e deadlocks.

• Exibições indexadas que referenciam mais de uma tabela. Quando você modifica uma tabela referenciada em uma exibição indexada, o mecanismo de banco de dados também precisa manter a exibição indexada. Isso exige mais bloqueios e pode levar a um aumento de bloqueios e deadlocks. Exibições indexadas também podem fazer com que as operações de atualização executem internamente no nível de isolamento confirmado por leitura.

• Modificações em colunas referenciadas em restrições de chave estrangeira. Quando você modifica colunas em uma tabela referenciada em uma FOREIGN KEY restrição, 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 escalonado para serializável durante a instrução para proteger contra inserções fantasmas.

• Dicas de bloqueio. Procure dicas de tabela que especifiquem níveis de isolamento que exigem mais bloqueios. Essas dicas incluem HOLDLOCK (que é equivalente a serializável), SERIALIZABLE, READCOMMITTEDLOCK (que desabilita o RCSI) e REPEATABLEREAD. Além disso, dicas como PAGLOCK, TABLOCK, UPDLOCK e XLOCK podem aumentar os riscos de bloqueios e deadlocks.

  Se essas dicas estiverem em vigor, pesquise por que elas foram implementadas. Essas dicas podem impedir condições de concorrência e garantir a validade dos dados. Talvez seja possível deixar essas dicas no lugar e evitar deadlocks futuros usando um método alternativo na seção Impedir que um deadlock ocorra novamente 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 controle de versão de linha e bloqueio de transações.

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

• Interação do usuário em transações. A interação do usuário dentro de uma transação explícita de várias instruções aumenta significativamente a duração das transações. Isso aumenta a probabilidade de que essas transações se sobreponham e que haja bloqueios e deadlocks.

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

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

Impedir que um deadlock se repita

Há várias técnicas disponíveis para evitar que deadlocks ocorram novamente, como ajuste de índice, enforçar planos com o Query Store e modificar consultas Transact-SQL.

• Examinar o índice clusterizado da tabela. A maioria das tabelas se beneficia de índices clusterizados, mas geralmente, as tabelas são implementadas acidentalmente como heaps.

  Uma forma de verificar se há um índice clusterizado é usando o procedimento armazenado do sistema sp_helpindex. Por exemplo, podemos ver um resumo dos índices da 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 tiver sido implementado para a tabela, ele index_description conterá a palavra clustered.

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

  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 as consultas de modificação a localizar os dados a serem atualizados com mais rapidez, o que reduz o número de bloqueios de atualização necessários.

  No exemplo de deadlock, o plano de execução de consulta encontrado no Repositório de Consultas contém uma verificação de índice clusterizado em relação ao índice PK_Product_ProductID. O grafo de deadlock indica que uma espera de bloqueio compartilhado (S) nesse índice é um componente no deadlock.

  

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

  Se criarmos o seguinte índice não clusterizado no banco de dados AdventureWorksLT que "cobre" as colunas de SalesLT.Product referenciadas pela exibição indexada, a consulta poderá encontrar 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 deadlock não se repetirá mais.

  Quando deadlocks envolvem modificações em colunas referenciadas em restrições de chave estrangeira, verifique se os índices na tabela de referência oferecem suporte eficiente à localização de linhas relacionadas na FOREIGN KEY.

  Embora os índices possam aprimorar muito o desempenho da consulta em alguns casos, eles também têm custos de sobrecarga e gerenciamento. Examine as diretrizes gerais de design de índice para avaliar o benefício dos índices antes de criar índices, principalmente índices amplos e índices em tabelas grandes.

• Avaliar o valor das exibições indexadas. Outra opção para impedir que o exemplo de deadlock se repita é remover o modo de exibição indexado SalesLT.vProductAndDescription. Se essa exibição indexada não estiver sendo usada, isso reduzirá a sobrecarga de manter a exibição indexada ao longo do tempo.

• Usar o isolamento de instantâneo. Em alguns casos, definir o nível de isolamento da transação como instantâneo para uma ou mais das transações envolvidas em um deadlock pode evitar que bloqueios e deadlocks se repitam.

  Essa técnica provavelmente terá êxito quando usada em SELECT instruções quando o instantâneo confirmado de leitura estiver desabilitado em um banco de dados. Quando o instantâneo confirmado de leitura é desabilitado, SELECT as consultas que usam o nível de isolamento confirmado de leitura exigem bloqueios compartilhados (S). O uso do isolamento de instantâneo nessas transações elimina a necessidade de bloqueios compartilhados, o que pode impedir bloqueios e deadlocks.

  Em bancos de dados em que o isolamento de instantâneo confirmado de leitura está habilitado, SELECT as consultas não exigem bloqueios compartilhados (S), portanto, os deadlocks são mais propensos a ocorrer entre transações que estão modificando dados. Nos casos em que ocorrem deadlocks entre várias transações que modificam dados, o isolamento por instantâneo pode resultar em um conflito de atualização em vez de deadlock. Esse caso também exige que uma das transações repita a operação.

• Forçar um plano com o Repositório de Consultas. Pode ser que você descubra 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 impedir que o deadlock se repita forçando um plano no Repositório de Consultas.

• Modificar o Transact-SQL. Talvez seja necessário modificar Transact-SQL para impedir que o deadlock se repita. A modificação do Transact-SQL deve ser feita com cuidado e as alterações devem ser testadas rigorosamente para garantir que os dados estejam corretos quando as modificações forem executadas simultaneamente. Ao reescrever o Transact-SQL, considere:

  • A ordenação das instruções nas transações para que elas acessem os objetos na mesma ordem.

  • A divisão das transações em transações menores quando possível.

  • O uso de 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 deadlocks na guia Deadlocks.

Remover uma sessão de XEvents

Você pode deixar uma sessão XEvents em execução coletando informações de deadlock 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 arquivos de blob do Armazenamento do Azure para um rastreamento ativo, exceto pelo arquivo que está sendo gravado no momento.

Quando você quer remover uma sessão de XEvents, a remoção de sessão do Transact-SQL é igual, independentemente do tipo de destino selecionado.

Para remover uma sessão de XEvents, execute o SQL Transact a seguir. 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 Gerenciador de Armazenamento do Azure é um aplicativo autônomo que simplifica o trabalho com destinos de arquivo de evento armazenados em blobs no Armazenamento do Azure. Você pode usar o Gerenciador de Armazenamento para:

• Criar um contêiner de blob para armazenar dados de sessão de XEvent.

• Obter a SAS (Assinatura de Acesso Compartilhado) do contêiner de blob.

  • Como mencionado em Coletar grafos de deadlock no Banco de Dados SQL do Azure com Eventos Estendidos, as permissões de leitura, gravação e listagem são necessárias.

  • Remova todos os caracteres ? à esquerda do Query string para usar o valor como o segredo ao criar uma credencial no escopo do banco de dados.

• Ver e baixar arquivos de eventos estendidos de um contêiner de blob.

Baixe o Gerenciador de Armazenamento do Azure.

Conteúdo relacionado

• Entender e resolver problemas de bloqueios
• Guia de Controle de Versão de Linha e Bloqueio de Transações
• Guia Deadlocks
• DEFINIR NÍVEL DE ISOLAMENTO DE TRANSAÇÃO
• Banco de Dados SQL do Azure: melhorando o ajuste de desempenho com ajuste automático
• Fornecer desempenho consistente com o SQL Azure
• Tentar novamente a lógica para erros transitórios