Analisar e evitar deadlocks no Banco de Dados SQL do Azure

Aplica-se a: Banco de Dados SQL do Azure

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, bem como planejar e testar alterações para evitar que os deadlocks se repitam.

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 no Banco de Dados SQL do Azure

Cada novo banco de dados no Banco de Dados SQL do Azure tem a configuração RCSI (instantâneo de leitura confirmada) do banco de dados habilitada por padrão. O bloqueio entre sessões que leem dados e sessões que gravam dados é minimizado com o RCSI, que usa o controle de versão de linha para aumentar a simultaneidade. No entanto, o bloqueio e os deadlocks ainda podem ocorrer em bancos de dados no Banco de Dados SQL do Azure porque:

• Consultas que modificam dados podem bloquear umas às outras.
• Consultas podem ser executadas em níveis de isolamento que aumentam o bloqueio. Os níveis de isolamento podem ser especificados por meio de métodos de biblioteca de cliente, dicas de consulta ou instruções SET em Transact-SQL.
• O RCSI pode estar desabilitado, fazendo com que o banco de dados use bloqueios compartilhados (S) para proteger as instruções SELECT executadas no nível de isolamento de leitura confirmada. Isso pode aumentar 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 aguardarão indefinidamente, a menos que uma das transações participantes seja revertida, por exemplo, porque a sessão foi encerrada.

O monitor de deadlock do mecanismo de banco de dados verifica periodicamente se há tarefas em deadlock. Quando o monitor de deadlock detecta uma dependência cíclica, ele escolhe uma das tarefas como vítima e encerra a respectiva transação com o erro 1205, "A transação (ID N do processo) foi bloqueada em recursos de bloqueio com outro processo e foi escolhida como vítima de deadlock. Execute a transação novamente". Esse tipo de eliminação do deadlock permite que as outras tarefas ou as tarefas no deadlock concluam as 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 para evitar o mesmo deadlock. 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 instruções SELECT.

Com o RCSI habilitado:

• As instruções que leem dados não bloqueiam instruções que modificam dados.
• As instruções que modificam dados não bloqueiam instruções que leem 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 de instantâneo está habilitado no banco de dados.

Você pode identificar se o isolamento de instantâneo e/ou RCSI está habilitado com o Transact-SQL. Conecte-se ao banco de dados no Banco de Dados SQL do Azure e execute a seguinte consulta:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Se o RCSI estiver habilitado, a coluna is_read_committed_snapshot_on retornará o valor 1. Se o isolamento de instantâneo estiver habilitado, a coluna snapshot_isolation_state_desc retornará o valor ATIVADO.

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

Como interpretar eventos de deadlock

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á disparada depois que um deadlock individual for 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 receberem o erro 1205, "A transação (ID do processo N) foi bloqueada em recursos de bloqueio com outro processo e foi escolhida como vítima de deadlock. Execute a transação novamente."

No entanto, é bom 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 no banco de dados e, nesse caso, você pode tomar medidas para impedir que os 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, você poderá impedir que os deadlocks se repitam forçando um plano de consulta com Repositório de Consultas.
• 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, usaremos o banco de dados de exemplo AdventureWorksLT para configurar alertas para deadlocks, causar um deadlock de exemplo, analisar o grafo de deadlock do exemplo de deadlock e testar alterações para impedir que o deadlock se repita.

Usaremos o cliente SSMS (SQL Server Management Studio) neste artigo, pois ele contém funcionalidade para exibir grafos de deadlock em um modo visual interativo. Você pode usar outros clientes, como o Azure Data Studio, para acompanhar os exemplos, mas só é possível exibir grafos de 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 de como criar o AdventureWorksLT com o portal do Azure, a CLI do Azure ou o PowerShell, selecione a abordagem da escolha no Guia de início rápido: criar um banco de dados individual do Banco de Dados SQL do Azure.

Configurar alertas de deadlock no portal do Azure

Para configurar alertas sobre eventos de deadlock, siga as etapas do 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 grafos de deadlock com XEvents (Eventos Estendidos) no Banco de Dados SQL do Azure, 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=pUNbbsmDiMzXr1vuNGZh84zyOMBFaBjgWv53IhOzYWQ%3D'
    ;
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 deadlock no AdventureWorksLT

Para causar um deadlock, você precisará conectar duas sessões ao banco de dados AdventureWorksLT. Vamos nos referir a essas sessões como Sessão A e Sessão B.

Na Sessão A, execute o Transact-SQL a seguir. Esse código inicia uma transação explícita e executa uma só instrução que atualiza a tabela SalesLT.Product. Para fazer isso, a transação adquire um bloqueio de atualização (U) em uma linha na tabela SalesLT.Product que é convertido em um bloqueio exclusivo (X). Deixamos a transação aberta.

BEGIN TRAN

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

Agora, na Sessão A, execute o Transact-SQL a seguir. Esse código não inicia uma transação explicitamente. Ele opera no modo de transação de confirmação automática. Essa instrução atualiza a tabela SalesLT.ProductDescription. A atualização fará 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
    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';

Para concluir essa atualização, a Sessão B precisa de um bloqueio compartilhado (S) na tabela SalesLT.Product, incluindo a linha bloqueada pela Sessão A. A sessão B é bloqueada em SalesLT.Product.

Retorne à Sessão A. Execute a instrução Transact-SQL a seguir. Uma segunda instrução UPDATE é executada como parte da transação aberta.

    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';

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

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

Após alguns segundos, o monitor de deadlock identificará que as transações na Sessão A e na Sessão B estão se bloqueando mutuamente e que nenhuma delas consegue progredir. Você verá um deadlock ocorrer, com a Sessão A escolhida como a vítima do deadlock. Uma mensagem de erro será exibida na Sessão A com um texto semelhante ao seguinte:

Msg 1205, Nível 13, Estado 51, Linha 7 A transação (ID do Processo 91) entrou em deadlock devido ao bloqueio de recursos com outro processo e foi escolhida como a vítima do deadlock. Execute a transação novamente.

A sessão B será concluída com êxito.

Se você configurar alertas de deadlock no portal do Azure, receberá uma notificação logo depois que o deadlock ocorrer.

Exibir grafos de deadlock de uma sessão de XEvents

Se for configurada uma sessão de XEvents para coletar deadlocks e um deadlock ocorrer após o início da sessão, você poderá ver 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 sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    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 sysname = N'deadlocks_eventfile',
    @filename nvarchar(2000);

WITH eft as (SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    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 nvarchar(2000) = N'https://yourstorageaccountname.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 preferir analisar os 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 da sua escolha, com a extensão .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.

   Agora, o grafo de deadlock será 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, convém 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.

No exemplo de deadlock, a ID do processo 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:

• A ID do processo do servidor, também conhecida como ID da sessão ou 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.

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

Além disso, você poderá ver o buffer de entrada da última execução da instrução em cada sessão antes do deadlock focalizando cada processo. O buffer de entrada aparecerá 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 da última instrução executada por todas as sessões 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 as duas sessões foram executadas usando o cliente SSMS no logon 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 no deadlock é 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.

Usaremos 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 grafo 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 teve um papel no deadlock bloqueando a Sessão B. O buffer de entrada query_hash e as informações relacionadas à primeira instrução executada pela Sessão A não estão incluídas no grafo de deadlock.

Para identificar a execução completa do Transact-SQL em uma transação de várias instruções envolvida em um deadlock, você precisará 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 ele ocorre. Se uma instrução envolvida no deadlock foi truncada e apenas o 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, convém examinar os planos de execução de consulta em busca das 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 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
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval 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 no Repositório de Consultas, dependendo das configurações CLEANUP_POLICY ou 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 os 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. As exibições indexadas também podem fazer com que as operações de atualização sejam executadas internamente no nível de isolamento de confirmação de leitura.

• 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 FOREIGN KEY, o mecanismo de banco de dados precisa procurar as 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 como proteção 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 corrida e garantir a validade dos dados. É possível deixar essas dicas em vigor e impedir futuros deadlocks usando um método alternativo na seção Impedir que um deadlock se repita deste artigo, caso seja 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 se repitam, incluindo ajuste de índice, planos forçados com o Repositório de Consultas e modificação de 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:

  exec sp_helpindex 'SalesLT.Product';
  GO
  

  Examine a coluna index_description. Uma tabela pode ter apenas um índice clusterizado. Se um índice clusterizado tiver sido implementado para a tabela, o 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, a criação ou o ajuste de um índice clusterizado pode reduzir ou eliminar o bloqueio em deadlocks. Em outros casos, pode ser necessário 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. Como mencionado na seção Procurar padrões que aumentam o bloqueio deste artigo, as exibiçõ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 os deadlocks envolverem modificações em colunas referenciadas em restrições de chave estrangeira, verifique se os índices na tabela de referência da FOREIGN KEY dão suporte à localização eficiente de linhas relacionadas.

  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, a sobrecarga de manter a exibição indexada ao longo do tempo será reduzida.

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

  É mais provável que essa técnica tenha êxito quando usada em instruções SELECT com o instantâneo de confirmação de leitura desabilitado em um banco de dados. Quando o instantâneo de confirmação de leitura está desabilitado, as consultas SELECT que usam o nível de isolamento de confirmação 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 com confirmação de leitura está habilitado, as consultas SELECT não exigem bloqueios compartilhados (S), portanto, é mais provável que ocorram os deadlocks entre transações que modificam dados. Nos casos em que ocorrem deadlocks entre várias transações que modificam dados, o isolamento de instantâneo pode resultar em um conflito de atualização, não em um 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. Você pode descobrir 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 o Transact-SQL para evitar 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 de XEvents coletando informações de deadlock em execução em bancos de dados críticos por longos períodos. Mas saiba que, se você usar um destino de arquivo de evento, isso poderá resultar em arquivos grandes se ocorrerem vários deadlocks. Você pode excluir os arquivos de blob do Armazenamento do Azure de um rastreamento ativo, com exceção do 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.

Baixar o Gerenciador de Armazenamento do Azure.

Próximas etapas

Saiba mais sobre o desempenho no Banco de Dados SQL do Azure:

• Entender e resolver problemas de bloqueio do Banco de Dados SQL do Azure
• Guia de Controle de Versão de Linha e Bloqueio de Transações
• Guia Deadlocks
• SET TRANSACTION ISOLATION LEVEL
• Banco de Dados SQL do Azure: melhorando o ajuste de desempenho com ajuste automático
• Fornecer desempenho consistente com o SQL Azure
• Lógica de repetição para erros transitórios.