Tenho abaixo duas consultas sobre Produção:
Consulta nº 1: Script que exclui dados históricos mais antigos da tabela principal "Tabela1" com base na coluna "Data"
Existem ~20 tabelas que apontam para "Tabela1" por chave estrangeira com regra de cascata, portanto, quando você exclui da "Tabela1", os dados também são excluídos de ~20 outras tabelas
O script exclui dados em lotes de 50 linhas por vez com intervalo de 1 segundo entre os lotes
O script NÃO faz com que os bloqueios Exclusivos escalem para bloqueio em nível de tabela, quando o lote tem apenas 50 linhas; ele apenas coloca bloqueios de nível de linha/página (X)
Consulta nº 2: Aplicativo de produção que extrai dados de "Tabela1", "Tabela2", "Tabela3" e algumas outras tabelas
Esta é uma consulta de leitura pesada, que coloca bloqueios compartilhados em tabelas. Não faz nenhuma inserção/atualização/exclusão
Problema:
Às vezes, o deadlock ocorre quando Query # 2(aplicativo Prod) lê dados e eu executo simultaneamente Query # 1(script excluindo dados mais antigos) no SSMS. Query # 2é sempre uma vítima, pelo que entendo, porque NÃO está fazendo nenhuma alteração no log de transações, Query # 1enquanto
Tentando resolver isso, adicionei set deadlock_priority -10a Query # 1, esperando que ele se torne Query # 1vítima em quaisquer possíveis impasses
Primeiro parecia que funcionou, Query # 1tornou-se vítima em situações de impasse, lançando o erro 1205 ao excluir alguns dos lotes.
MAS - recentemente descobri que às vezes Query # 2ainda se torna uma vítima
Pergunta:
Como é possível que Query # 2às vezes se torne uma vítima mesmo depois de eu ter set deadlock_priority -10parado Query # 1?
Existe uma maneira de corrigi-lo e ter certeza de Query # 1que será a vítima em 100% dos casos?
Atualização : gráfico adicionado abaixo
Você pode tentar resolver o problema alterando o nível de isolamento da transação. Habilitar especificamente o isolamento de instantâneo e definir o instantâneo de leitura confirmada para que as leituras e gravações não interfiram umas nas outras. Você também pode atualizar a consulta de produção para usar o isolamento de instantâneo sem atualizar o nível de isolamento do banco de dados padrão.
Seja cuidadoso e teste isso completamente porque aumentará o tráfego no tempdb à medida que os instantâneos de linha são gravados. A configuração Read Committed Snapshot também é uma alteração de comportamento padrão em todo o banco de dados que precisa de testes de regressão para verificar se não há efeitos colaterais inesperados.
Uma explicação melhor sobre essa opção está disponível na Microsoft aqui: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server