Analisar e evitar impasses no Banco de Dados SQL do Azure

Aplica-se a:Banco de Dados SQL do Azure

Este artigo ensina-lhe como identificar impasses na Base de Dados SQL do Azure, utilizar gráficos de impasse e o Arquivo de Consultas para identificar as consultas no impasse e planear e testar alterações para evitar que os impasses se repitam.

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

Como os impasses ocorrem no Banco de Dados SQL do Azure

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

• As consultas que modificam dados podem bloquear umas às outras.
• As consultas podem ser executadas sob níveis de isolamento que aumentam o bloqueio. Os níveis de isolamento podem ser especificados por meio de métodos de biblioteca de cliente, dicas de consulta ou instruções SET no Transact-SQL.
• O RCSI pode ser desativado, fazendo com que o banco de dados use bloqueios compartilhados (S) para proteger instruções SELECT executadas sob o nível de isolamento confirmado de leitura. Isso pode aumentar o bloqueio e os bloqueios.

Um exemplo de impasse

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

Por exemplo:

1. A sessão A inicia uma transação explícita e executa uma instrução update 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 update que modifica a SalesLT.ProductDescription tabela. A instrução update se une à SalesLT.Product tabela para encontrar as linhas corretas a serem atualizadas.
   • A sessão B adquire um bloqueio de atualização (U) em 72 linhas na SalesLT.ProductDescription tabela.
   • A Sessão B precisa de um bloqueio partilhado nas linhas da tabela SalesLT.Product, incluindo a linha que está bloqueada pela Sessão A. A sessão B está bloqueada em SalesLT.Product.
3. A sessão A continua sua transação e agora executa uma atualização na SalesLT.ProductDescription tabela. A Sessão A está bloqueada pela Sessão B em SalesLT.ProductDescription.

Todas as transações num impasse irão aguardar indefinidamente, a menos que uma das transações participantes seja revertida, por exemplo, porque a sua sessão foi terminada.

O monitor de impasse do motor de base de dados verifica periodicamente as tarefas que estão num impasse. Se o monitor de deadlock detetar uma dependência cíclica, ele escolhe uma das tarefas como vítima e encerra sua transação com o erro 1205, "A transação (ID de processo N) foi bloqueada em recursos de bloqueio com outro processo e foi escolhida como a vítima de deadlock. Execute novamente a transação." Quebrar o impasse desta forma permite que a outra tarefa ou tarefas no impasse concluam suas transações.

Nota

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 do deadlock deve repetir a transação, que geralmente é concluída após a conclusão da outra transação ou transações envolvidas no deadlock.

É uma boa prática introduzir um atraso curto e aleatório antes de tentar novamente para evitar encontrar o mesmo impasse novamente. Saiba mais sobre como projetar 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 de 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 ativado:

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

O nível de isolamento de instantâneo também é habilitado por padrão para novos bancos de dados no Banco de Dados SQL do Azure. O isolamento de instantâneo é um nível de isolamento adicional baseado em linha que fornece consistência de nível de transação para dados e que usa versões de linha para selecionar linhas a serem atualizadas. Para usar o isolamento de instantâneo, as consultas ou conexões devem definir explicitamente seu nível de isolamento de transação como SNAPSHOT. Isso só pode ser feito quando o isolamento de instantâneo estiver habilitado para o banco de dados.

É possível identificar se o RCSI e/ou o isolamento de snapshot estão habilitados com o Transact-SQL. Conecte-se ao seu banco de dados no Banco de Dados SQL do Azure e execute a seguinte consulta:

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

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

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

Interpretação de eventos de impasse

Um evento de deadlock é emitido depois que o gerenciador de deadlock no Banco de Dados SQL do Azure deteta um deadlock e seleciona uma transação como vítima. Em outras palavras, se você configurar alertas para deadlocks, a notificação será acionada depois que um deadlock individual for resolvido. Não há nenhuma ação do usuário que precise ser tomada para esse impasse. Os aplicativos devem ser escritos para incluir a lógica de repetição para que continuem automaticamente após o recebimento do erro 1205, "A transação (ID do processo N) foi bloqueada nos recursos de bloqueio com outro processo e foi escolhida como a vítima do deadlock. Execute novamente a transação."

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

Principais métodos para evitar impasses

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

• O risco é baixo para essa abordagem porque o ajuste de índices não clusterizados não requer alterações no próprio código de consulta, reduzindo o risco de um erro do usuário ao reescrever o Transact-SQL que faz com que dados incorretos sejam retornados ao usuário.
• O ajuste eficaz do índice não clusterizado ajuda as consultas a encontrar os dados para ler e modificar com mais eficiência. Ao reduzir a quantidade de dados que uma consulta precisa acessar, a probabilidade de bloqueio é reduzida e os bloqueios muitas vezes podem ser evitados.

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

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

• Se o deadlock ocorrer somente quando um plano específico for escolhido para uma das consultas envolvidas no deadlock, forçar um plano de consulta com o Query Store poderá impedir que os deadlocks voltem a ocorrer.
• Reescrever o Transact-SQL para uma ou mais transações envolvidas no deadlock também pode ajudar a evitar deadlocks. Dividir transações explícitas em transações menores requer codificação e testes cuidadosos para garantir a validade dos dados quando ocorrem modificações simultâneas.

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

Monitorar e alertar sobre impasses

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

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

Criar o banco de dados AdventureWorksLT

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

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

Configurar alertas de deadlock no portal do Azure

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

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

Coletar gráficos de deadlock no Banco de Dados SQL do Azure com Eventos Estendidos

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

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

Abordagem	Benefícios	Considerações	Cenários de utilização
Alvo do buffer de anel	Configuração simples apenas com Transact-SQL.	Os dados de evento são limpos quando a sessão XEvents é interrompida por qualquer motivo, como colocar o banco de dados offline ou um failover de banco de dados. Os recursos de banco de dados são usados para manter dados no buffer de anel e para consultar dados de sessão.	Colete dados de rastreamento de amostra para teste e aprendizagem. Crie para necessidades de curto prazo se não puder configurar uma sessão usando um destino de arquivo de evento imediatamente. Use como uma "plataforma de aterrissagem" para dados de rastreamento, quando você tiver configurado um processo automatizado para persistir dados de rastreamento em uma tabela.
Destino do arquivo de evento	Persiste os dados do evento em um blob no Armazenamento do Azure para que os dados fiquem disponíveis mesmo depois que a sessão for interrompida. Os arquivos de evento podem ser baixados do portal do Azure ou do Gerenciador de Armazenamento do Azure e analisados localmente, o que não requer o uso de recursos de banco de dados para consultar dados de sessão.	A instalação é mais complexa e requer a configuração de um contêiner de Armazenamento do Azure e de uma credencial com escopo de banco de dados.	Uso geral quando você deseja que os dados de evento persistam mesmo após a sessão de evento parar. Você deseja executar um rastreamento que gera quantidades maiores de dados de eventos do que gostaria de persistir na memória.

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

Alvo do buffer de anel

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

Este código de exemplo cria uma sessão XEvents que captura gráficos 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 iniciar uma sessão XEvents para o evento que grava no destino do buffer de anel, conecte-se ao banco de dados e execute o sqlserver.database_xml_deadlock_report 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

Destino do arquivo de evento

O destino do arquivo de eventos persiste gráficos de deadlock para arquivos para que eles fiquem disponíveis mesmo depois que a sessão XEvents for interrompida. O destino do arquivo de eventos também permite capturar mais gráficos de deadlock sem alocar memória adicional para um buffer de anel. O destino do arquivo de eventos é adequado para uso a longo prazo e para coletar grandes quantidades de dados de rastreamento.

Para criar uma sessão XEvents que grava em um destino de arquivo de evento, iremos:

1. Configure um contêiner de Armazenamento do Azure para armazenar os arquivos de rastreamento usando o portal do Azure.
2. Crie uma credencial com escopo de banco de dados com Transact-SQL.
3. Crie a sessão XEvents com Transact-SQL.

Configurar um contêiner de Armazenamento do Azure

Para configurar um contêiner de Armazenamento do Azure, primeiro crie ou selecione uma conta de Armazenamento do Azure existente e, em seguida, crie o contêiner. Gere um token SAS (Assinatura de Acesso Compartilhado) para o contêiner. Esta seção descreve a conclusão desse processo no portal do Azure.

Nota

Se desejar criar e configurar o contêiner de blob de Armazenamento do Azure com o PowerShell, consulte Código de destino do Arquivo de Eventos para eventos estendidos no Banco de Dados SQL do Azure. Como alternativa, você pode achar conveniente Usar o Gerenciador de Armazenamento do Azure para criar e configurar o contêiner de blob de Armazenamento do Azure em vez de usar o portal do Azure.

Criar ou selecionar uma conta de Armazenamento do Azure

Você pode usar uma conta de Armazenamento do Azure existente ou criar uma nova conta de Armazenamento do Azure para hospedar um contêiner para arquivos de rastreamento.

Para usar uma conta de Armazenamento do Azure existente:

1. Navegue até o grupo de recursos com o qual você deseja trabalhar no portal do Azure.
2. No painel Visão geral, em Recursos, defina a lista suspensa Tipo como Conta de armazenamento.
3. Clique na conta de armazenamento que pretende utilizar.

Para criar uma nova conta de Armazenamento do Azure, siga as etapas em Criar uma conta de armazenamento do Azure. Conclua o processo selecionando Ir para recurso na etapa final.

Criar um contentor

Na página da conta de armazenamento no portal do Azure:

1. Em Armazenamento de dados, selecione Contêineres.
2. Selecione + Contêiner para criar um novo contêiner. O painel Novo contêiner será exibido.
3. Insira um nome para o contêiner em Nome.
4. Selecione Criar.
5. Selecione o contêiner na lista depois de criado.

Criar um token de acesso compartilhado

Na página de contêiner no portal do Azure:

1. Em Configurações, selecione Tokens de acesso compartilhado.
2. Deixe o botão de opção Método de assinatura definido como a seleção padrão, Chave da conta.
3. Na lista suspensa Permissões, selecione as permissões Leitura, Gravação e Lista.
4. Defina Início para a data e hora em que você gostaria de poder escrever arquivos de rastreamento. Opcionalmente, configure o fuso horário na lista suspensa abaixo de Iniciar.
5. Defina Expiração para a data e hora em que você deseja que essas permissões expirem. Opcionalmente, configure o fuso horário na lista suspensa abaixo de Expirar. Você é capaz de definir isso para uma data distante no futuro, como dez anos, se desejar.
6. Selecione Gerar token SAS e URL. O token SAS de Blob e o URL SAS de Blob serão exibidos na tela.
7. Copie e preserve o token SAS de Blob e os valores de URL de SAS de Blob para uso em etapas adicionais.

Criar uma credencial com escopo de banco de dados

Conecte-se ao seu banco de dados no Banco de Dados SQL do Azure com o SSMS para executar as etapas a seguir.

Para criar uma credencial com escopo de banco de dados, você deve primeiro criar uma chave mestra no banco de dados, caso não exista.

Execute o seguinte Transact-SQL para criar uma chave mestra, se não existir:

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

Em seguida, crie uma credencial com escopo de banco de dados com o seguinte Transact-SQL. Antes de executar o código:

• Modifique o URL para refletir o nome da conta de armazenamento e o nome do contêiner. Este URL estará presente no início do URL SAS de Blob que copiou quando criou o token de acesso partilhado. Você só precisa do texto antes do primeiro ? na cadeia de caracteres.
• Modifique o SECRET para conter o valor do token SAS de Blob copiado quando criou o token de acesso compartilhado.

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

Criar a sessão XEvents

Crie e inicie a sessão XEvents com o seguinte Transact-SQL. Antes de executar a instrução:

• Substitua o valor para refletir o nome da conta de armazenamento e o nome do filename contêiner. Este URL estará presente no início do URL SAS de Blob que copiou quando criou o token de acesso partilhado. Você só precisa do texto antes do primeiro ? na cadeia de caracteres.
• Opcionalmente, altere o nome do arquivo armazenado. O nome do arquivo especificado aqui fará parte do(s) arquivo(s) real(is) usado(s) para o(s) blob(s) que armazenam dados de eventos: valores adicionais serão acrescentados para que todos os arquivos de evento tenham um nome exclusivo.
• Opcionalmente, adicione eventos adicionais à sessão.

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 no AdventureWorksLT

Nota

Este exemplo funciona no AdventureWorksLT banco de dados com o esquema e os dados padrão quando o RCSI foi habilitado. Consulte Criar o banco de dados AdventureWorksLT para obter instruções sobre como criar o banco de dados .

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

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

BEGIN TRAN

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

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

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 esta atualização, a Sessão B necessita de um bloqueio partilhado (S) nas linhas da tabelaSalesLT.Product, incluindo a linha que está bloqueada pela Sessão A. A sessão B será bloqueada em SalesLT.Product.

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

    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        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.

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

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

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

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

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

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

Se você configurou uma sessão XEvents para coletar deadlocks e um deadlock ocorreu depois que a sessão foi iniciada, você pode visualizar uma exibição gráfica interativa do gráfico de deadlock, bem como o XML para o gráfico de deadlock.

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

Alvo do buffer de anel

Se você configurar uma sessão XEvents gravando no buffer de anel, poderá consultar informações de deadlock com o seguinte Transact-SQL. Antes de executar a consulta, substitua o valor de @tracename pelo nome da 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

Destino do arquivo de evento

Se você configurar uma sessão XEvents gravando em um arquivo de evento, poderá baixar arquivos do portal do Azure e exibi-los localmente ou poderá consultar arquivos de evento com o Transact-SQL.

O download de arquivos do portal do Azure é recomendado porque esse método não requer o uso de recursos de banco de dados para consultar dados de sessão.

Opcionalmente, reinicie a sessão XEvents

Se uma sessão de Eventos Estendidos estiver atualmente em execução e gravando em um destino de arquivo de evento, o contêiner de blob que está sendo gravado terá um estado de Concessão de Leasing no portal do Azure. O tamanho será o tamanho máximo do arquivo. Para baixar um arquivo menor, você pode parar e reiniciar a sessão de Eventos Estendidos antes de baixar os arquivos. Isso fará com que o arquivo altere seu estado de concessão para Disponível, e o tamanho do arquivo será o espaço usado pelos eventos no arquivo.

Para parar e reiniciar uma sessão XEvents, conecte-se ao seu banco de dados e execute o seguinte Transact-SQL. Antes de executar o código, substitua o nome da sessão xEvents pelo valor apropriado.

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

Baixar arquivos de rastreamento do portal do Azure

Para exibir eventos de deadlock que foram coletados em vários arquivos, baixe os arquivos de sessão de eventos para o computador local e exiba os arquivos no SSMS.

Nota

Você também pode usar Usar o Gerenciador de Armazenamento do Azure para baixar rápida e convenientemente arquivos de sessão de evento de um contêiner de blob no Armazenamento do Azure.

Para baixar os arquivos do portal do Azure:

1. Navegue até a conta de armazenamento que hospeda seu contêiner no portal do Azure.
2. Em Armazenamento de dados, selecione Contêineres.
3. Selecione o contêiner que contém seus arquivos de rastreamento XEvent.
4. Para cada arquivo que você deseja baixar, selecione ..., em seguida , Download.

Exibir arquivos de rastreamento XEvents no SSMS

Se você tiver baixado vários arquivos, poderá abrir eventos de todos os arquivos juntos no visualizador XEvents no SSMS. Para tal:

1. Abra o SQL Server Management Studio.
2. Selecione Arquivo, Abrir e Mesclar arquivos de eventos estendidos....
3. Selecione Adicionar.
4. Navegou até o diretório onde você baixou os arquivos. Use a tecla Shift para selecionar vários arquivos.
5. Selecione Abrir.
6. Selecione OK na caixa de diálogo Mesclar arquivos de eventos estendidos.

Se tiver transferido um único ficheiro, clique com o botão direito do rato no ficheiro e selecione Abrir com e, em seguida, SSMS. Isso abrirá o visualizador XEvents no SSMS.

Navegue entre os eventos coletados selecionando o carimbo de data/hora relevante. Para exibir o XML de um deadlock, clique duas vezes na xml_report linha no painel inferior.

Consultar arquivos de rastreamento com Transact-SQL

Importante

Consultar arquivos de rastreamento XEvents grandes (1 GB ou mais) usando esse método não é recomendado porque ele pode consumir grandes quantidades de memória em seu banco de dados ou pool elástico.

Para consultar arquivos de rastreamento XEvents de um contêiner de Armazenamento do Azure com Transact-SQL, você deve fornecer o nome exato do arquivo de rastreamento. Você também deve executar a consulta no contexto do banco de dados com a credencial para acessar o armazenamento, ou seja, o mesmo banco de dados que criou os arquivos XEvents.

Execute o seguinte Transact-SQL para consultar o arquivo de rastreamento XEvents atualmente ativo. Antes de executar a consulta, substitua @tracename pelo nome da sua sessão XEvents.

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

Para consultar arquivos não ativos, navegue até a Conta de Armazenamento e o contêiner no portal do Azure para identificar os nomes dos arquivos.

Execute a seguinte consulta Transact-SQL em seu banco de dados para consultar um arquivo XEvents específico. Antes de executar a consulta, substitua o nome da conta de armazenamento, o nome do contêiner e o nome do arquivo na URL por @filename:

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 gráfico de deadlock em XML

A visualização de um gráfico de deadlock no formato XML permite copiar as inputbuffer instruções Transact-SQL envolvidas no deadlock. Você também pode preferir analisar deadlocks em um formato baseado em texto.

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

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

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

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

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

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

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

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

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

2. Selecione Arquivo e Salvar como....

3. Defina Salvar como tipo como Todos os arquivos.

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

5. Selecione Guardar.

   

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

7. Reabra o arquivo no SSMS selecionando Arquivo, Abrir e Arquivo. Selecione o arquivo que você salvou com a .xdl extensão.

   O gráfico de impasse será agora apresentado no SSMS com uma representação visual dos processos e recursos envolvidos no impasse.

   

Analisar um impasse para o Banco de Dados SQL do Azure

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

• Lista de vítimas. O identificador do processo da vítima do impasse.
• Lista de processos. Informações sobre todos os processos envolvidos no impasse. Os gráficos de deadlock usam o termo 'processo' para representar uma sessão executando uma transação.
• Lista de recursos. Informações sobre os recursos envolvidos no impasse.

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

Lista de vítimas de impasse

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

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

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

Lista de processos de impasse

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

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

• ID do processo do servidor, também conhecido como ID da sessão ou SPID.

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

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

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

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

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

• Informações de identificação da sessão, como o nome do cliente, o nome do host e o nome de login.
• O hash do plano de consulta para 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 nosso exemplo de impasse:

• Podemos ver que ambas as sessões foram executadas usando o cliente SSMS sob o login chrisqpublic .
• O hash do plano de consulta da última instrução executada antes do deadlock por nossa vítima de deadlock é 0x02b0f58d7730f798. Podemos ver o texto desta instrução no buffer de entrada.
• O hash do plano de consulta da última instrução executada pela outra sessão em nosso deadlock também é 0x02b0f58d7730f798. Podemos ver o texto desta instrução no buffer de entrada. Nesse caso, ambas as consultas têm o mesmo hash de plano de consulta porque as consultas são idênticas, exceto por um valor literal usado como um predicado de igualdade.

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.000 caracteres da instrução que está sendo executada.

Além disso, algumas declarações envolvidas no impasse podem não ser incluídas no gráfico de impasse. Em nosso exemplo, a Sessão A executou duas instruções de atualização em uma única transação. Apenas a segunda instrução update, a atualização que causou o deadlock, é incluída no gráfico de deadlock. A primeira declaração de atualização executada pela Sessão A desempenhou um papel no impasse ao bloquear a Sessão B. O buffer de entrada, query_hashe 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 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 tiver sido truncada e apenas o Transact-SQL parcial aparecer no buffer de entrada, você poderá encontrar o Transact-SQL para a instrução no Repositório de Consultas com o Plano de Execução.

Lista de recursos de deadlock

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

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

Nota

Você pode notar que os nomes de banco de dados são representados como uniquedientifers em gráficos de deadlock para bancos de dados no Banco de Dados SQL do Azure. Este é o physical_database_name para o banco de dados listado nas exibições sys.databases e sys.dm_user_db_resource_governance gerenciamento dinâmico.

Neste exemplo de impasse:

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

  • Possui um cadeado exclusivo (X) em uma chave no PK_Product_ProductID índice na SalesLT.Product mesa.
  • Solicita um bloqueio de atualização (U) em uma chave no PK_ProductDescription_ProductDescriptionID índice na SalesLT.ProductDescription tabela.

• O outro processo, a que nos referimos como Sessão B:

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

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

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

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

Esta consulta Transact-SQL procura planos de consulta correspondentes ao hash do plano de consulta que encontramos para o nosso deadlock de exemplo. Conecte-se ao banco de dados de 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 não seja possível obter um plano de execução de consulta do Repositório de Consultas, dependendo das configurações de 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 para a consulta.

Procure padrões que aumentem o bloqueio

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

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

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

• Exibições indexadas que fazem referência a mais de uma tabela. Quando você modifica uma tabela referenciada em um modo de exibição indexado, o mecanismo de banco de dados também deve manter o modo de exibição indexado. Isso requer a retirada de mais bloqueios e pode levar a um aumento do bloqueio e dos bloqueios. As exibições indexadas também podem fazer com que as operações de atualização sejam executadas internamente sob o nível de isolamento confirmado 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 de CHAVE ESTRANGEIRA, o mecanismo de banco de dados deve procurar linhas relacionadas na tabela de referência. As versões de linha não podem ser usadas para essas leituras. Nos casos em que as atualizações ou exclusões em cascata estão habilitadas, o nível de isolamento pode ser escalado para serializável durante a duração da instrução para proteger contra inserções fantasmas.

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

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

  Nota

  Saiba mais sobre o comportamento ao modificar dados usando o controle de versão de linha no guia Bloqueio de transação e controle de versão de linha.

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

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

  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 bloqueio e bloqueios.

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

Evitar que um impasse se repita

Há várias técnicas disponíveis para evitar que os bloqueios voltem a ocorrer, incluindo ajuste de índice, forçar planos com o Repositório de Consultas e modificar consultas Transact-SQL.

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

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

  exec 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, o index_description conterá a palavra 'clustered'.

  Se nenhum índice clusterizado estiver presente, a tabela será um heap. Nesse caso, revise se a tabela foi criada intencionalmente como um heap para resolver um problema de desempenho específico. Considere a implementação de 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 precisar empregar uma técnica adicional, como as outras nesta lista.

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

  Em nosso exemplo de deadlock, o plano de execução de consulta encontrado no Repositório de Consultas contém uma verificação de índice clusterizada em relação ao PK_Product_ProductID índice. O gráfico de deadlock indica que uma espera de bloqueio compartilhada (S) neste í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 bloqueios.

  Se criarmos o seguinte índice não clusterizado no AdventureWorksLT banco de dados que "cobre" as colunas referenciadas pelo modo de SalesLT.Product exibição indexado, isso ajuda a consulta a 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 impasse não ocorre mais.

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

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

• Avalie o valor das visualizações indexadas. Outra opção para evitar que nosso exemplo de impasse ocorra novamente é descartar a SalesLT.vProductAndDescription exibição indexada. Se essa exibição indexada não estiver sendo usada, isso reduzirá a sobrecarga de manter a exibição indexada ao longo do tempo.

• Use 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 impedir que o bloqueio e os bloqueios voltem a ocorrer.

  Essa técnica tem maior probabilidade de ser bem-sucedida quando usada em instruções SELECT quando o instantâneo confirmado de leitura é desabilitado em um banco de dados. Quando o instantâneo confirmado de leitura é desabilitado, as consultas SELECT 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 evitar bloqueios e bloqueios.

  Em bancos de dados onde o isolamento de instantâneo confirmado de leitura foi habilitado, as consultas SELECT não exigem bloqueios compartilhados (S), portanto, é mais provável que ocorram deadlocks entre transações que estão modificando dados. Nos casos em que ocorrem impasses entre várias transações modificando dados, o isolamento de instantâneo pode resultar em um conflito de atualização em vez de um deadlock. Da mesma forma, isso requer que uma das transações tente novamente sua operação.

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

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

  • Ordenar extratos em transações para que eles acessem objetos na mesma ordem.
  • Separar as transações em transações menores quando possível.
  • Usando dicas de consulta, se necessário, para otimizar o desempenho. Você pode aplicar dicas sem alterar o código do aplicativo usando o Repositório de Consultas.

Encontre mais maneiras de minimizar os impasses no guia Deadlocks.

Nota

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

Soltar uma sessão XEvents

Você pode querer sair de uma sessão XEvents coletando informações de deadlock em execução em bancos de dados críticos por longos períodos. Lembre-se de que, 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 para o arquivo que está sendo gravado no momento.

Quando você deseja remover uma sessão XEvents, o Transact-SQL soltar a sessão é o mesmo, independentemente do tipo de destino selecionado.

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

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

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Utilizar o Explorador de Armazenamento do Azure

O Azure Storage Explorer é 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:

• Crie um contêiner de blob para armazenar dados de sessão XEvent.
• Obtenha a assinatura de acesso compartilhado (SAS) para um contêiner de blob.
  • Conforme mencionado em Coletar gráficos de deadlock no Banco de Dados SQL do Azure com Eventos Estendidos, as permissões de leitura, gravação e lista são necessárias.
  • Remova qualquer caractere principal ? do para usar o valor como o segredo ao criar uma credencial com escopo de banco de Query string dados.
• Visualize e baixe arquivos de eventos estendidos de um contêiner de blob.

Baixe o Azure Storage Explorer..

Próximos passos

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

• Compreender e resolver problemas de bloqueio da Base de Dados SQL do Azure
• Guia de Controlo de Versão de Linha e Bloqueio de Transações
• Guia de impasses
• DEFINIR NÍVEL DE ISOLAMENTO DE TRANSAÇÃO
• Banco de Dados SQL do Azure: melhorando o ajuste de desempenho com ajuste automático
• Forneça um desempenho consistente com o Azure SQL
• Repita a lógica para erros transitórios.