Analisar e evitar deadlocks do Banco de Dados SQL do Azure e no Banco de Dados SQL do Fabric
Aplica-se a: Banco de Dados SQL do Azure Banco 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 do 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 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:
- 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). - 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 à tabelaSalesLT.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 emSalesLT.Product
.
- A sessão B adquire um bloqueio de atualização (U) em 72 linhas na tabela
- 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 emSalesLT.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.
Observação
Saiba mais sobre os critérios para escolher uma vítima de deadlock na seção Lista de processos de deadlock deste artigo.
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:
Abordagem | Benefícios | Considerações | Cenários de uso |
---|---|---|---|
Destino do buffer de anel |
|
|
|
Destino do arquivo de evento |
|
|
|
Selecione o tipo de destino que você quer usar:
O destino 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 no armazenamento e o destino do buffer de anel é limpo quando a sessão de XEvents é interrompida. Isso significa que os XEvents coletados não estarão disponíveis quando o mecanismo de banco de dados for reiniciado por algum motivo, como um failover. O destino do buffer de anel é mais adequado para aprendizagem e necessidades de curto prazo quando você não tem a capacidade de configurar imediatamente uma sessão de XEvents para um destino do arquivo de evento.
Este código de exemplo cria uma sessão de XEvents que captura grafos de deadlock na memória usando o destino do buffer de anel. A memória máxima permitida para o destino do buffer de anel é de 4 MB e a sessão será executada automaticamente quando o banco de dados ficar online, como após um failover.
Para criar e depois iniciar uma sessão de XEvents para o evento sqlserver.database_xml_deadlock_report
que grave 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
Causar um deadlock no AdventureWorksLT
Observação
Este exemplo funciona no banco de dados AdventureWorksLT
com o esquema e os dados padrão quando o RCSI está habilitado. Confira Criar o banco de dados AdventureWorksLT para obter instruções de como criar o banco de dados.
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:
Se você configurar uma sessão de XEvents gravando no buffer de anel, poderá consultar informações de deadlock com o SQL Transact a seguir. Antes de executar a consulta, substitua o valor de @tracename
pelo nome da sua sessão 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
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:
- Selecione Arquivo e Salvar Como....
- Deixe o valor Salvar como tipo como os Arquivos XML (*.xml) padrão
- Defina o Nome do arquivo para o nome da sua escolha.
- 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:
Selecione o valor na coluna
deadlock_xml
de qualquer linha para abrir o XML do grafo de deadlock em uma nova janela no SSMS.Selecione Arquivo e Salvar Como....
Defina o Tipo em Salvar como para Todos os Arquivos.
Defina o Nome do arquivo como o nome da sua escolha, com a extensão .xdl.
Selecione Salvar.
Feche o arquivo selecionando o X na guia na parte superior da janela ou selecionando Arquivo e Fechar.
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:
Observação
Você pode observar que os nomes de banco de dados são representados como identificadores exclusivos nos grafos de deadlock para bancos de dados no Banco de Dados SQL do Azure. Trata-se do physical_database_name
do banco de dados listado nas exibições de gerenciamento dinâmico sys.databases e sys.dm_user_db_resource_governance.
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 tabelaSalesLT.Product
. - Solicita um bloqueio de atualização (U) em uma chave no índice
PK_ProductDescription_ProductDescriptionID
na tabelaSalesLT.ProductDescription
.
- Tem um bloqueio exclusivo (X) em uma chave no índice
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 tabelaSalesLT.ProductDescription
. - Solicita um bloqueio compartilhado (S) em uma chave no índice
PK_ProductDescription_ProductDescriptionID
na tabelaSalesLT.ProductDescription
.
- Tem um bloqueio de atualização (U) em uma chave no índice
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) eREPEATABLEREAD
. Além disso, dicas comoPAGLOCK
,TABLOCK
,UPDLOCK
eXLOCK
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 deSalesLT.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.
Observação
Em alguns casos, convém ajustar a prioridade de deadlock de uma ou mais sessões envolvidas em um deadlock quando é importante que uma das sessões seja concluída com êxito sem repetição ou quando uma das consultas envolvidas no deadlock não é crítica e deve ser sempre escolhida como vítima. Embora isso não impeça a repetição do deadlock, pode reduzir o impacto de deadlocks futuros.
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 doQuery 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.