Bloqueio de atualização do índice do SQL Server

Existe uma maneira de evitar o impasse mantendo as mesmas consultas?

O gráfico de impasse mostra que esse impasse específico era um impasse de conversão associado a uma pesquisa de marcador (uma pesquisa RID neste caso):

Como observa a pergunta, o risco de impasse geral surge porque as consultas podem obter bloqueios incompatíveis nos mesmos recursos em ordens diferentes. A SELECTconsulta precisa acessar o índice antes da tabela devido à pesquisa RID, enquanto a UPDATEconsulta modifica primeiro a tabela e depois o índice.

A eliminação do impasse requer a remoção de um dos ingredientes do impasse. Seguem as principais opções:

1. Evite a pesquisa de RID fazendo a cobertura de índice não clusterizada. Isso provavelmente não é prático no seu caso porque a SELECTconsulta retorna 26 colunas.
2. Evite a pesquisa de RID criando um índice clusterizado. Isso envolveria a criação de um índice clusterizado na coluna Proposal. Vale a pena considerar isso, embora pareça que esta coluna é do tipo uniqueidentifier, o que pode ou não ser uma boa escolha para um índice clusterizado, dependendo de questões mais amplas.
3. Evite bloqueios compartilhados durante a leitura habilitando as opções READ_COMMITTED_SNAPSHOTou do SNAPSHOTbanco de dados. Isso exigiria testes cuidadosos, especialmente com relação a qualquer comportamento de bloqueio projetado. O código do gatilho também exigiria testes para garantir que a lógica funcionasse corretamente.
4. Evite fazer bloqueios compartilhados durante a leitura usando o READ UNCOMMITTEDnível de isolamento para a SELECTconsulta. Todas as ressalvas usuais se aplicam.
5. Evite a execução simultânea das duas consultas em questão usando um bloqueio de aplicativo exclusivo (consulte sp_getapplock ).
6. Use dicas de bloqueio de tabela para evitar simultaneidade. Este é um martelo maior do que a opção 5, pois pode afetar outras consultas, não apenas as duas identificadas na pergunta.

Posso de alguma forma fazer um X-Lock no índice na transação de atualização antes da atualização para garantir que a tabela e o acesso ao índice estejam na mesma ordem

Você pode tentar isso agrupando a atualização em uma transação explícita e executando um SELECTcom uma XLOCKdica no valor do índice não clusterizado antes da atualização. Isso depende de você saber com certeza qual é o valor atual no índice não clusterizado, obter o plano de execução correto e antecipar corretamente todos os efeitos colaterais de obter esse bloqueio extra. Ele também depende do mecanismo de travamento não ser inteligente o suficiente para evitar a trava se for considerado redundante .

Em suma, embora isso seja viável em princípio, não o recomendo. É muito fácil perder alguma coisa, ou ser mais esperto de maneiras criativas. Se você realmente deve evitar esses impasses (em vez de apenas detectá-los e tentar novamente), eu o encorajaria a examinar as soluções mais gerais listadas acima.

Eu tenho um problema semelhante que ocorre ocasionalmente e aqui está a abordagem que eu tomo.

1. Adicionar set deadlock priority low;ao select. Isso fará com que essa consulta seja a vítima do impasse quando ocorrer um impasse.
2. Configure a lógica de repetição em seu aplicativo para repetir automaticamente a seleção se ela falhar devido a um impasse (ou tempo limite), após aguardar/dormir por um breve período de tempo para permitir que as consultas de bloqueio sejam concluídas.

Observação: se você fizer selectparte de uma transação explícita de várias instruções, certifique-se de tentar novamente toda a transação, e não apenas a instrução que falhou, caso contrário, poderá obter alguns resultados inesperados. Se for um único select, tudo bem, mas se for uma declaração dentro xde numa transação, certifique-se de repetir todas as ndeclarações durante a nova tentativa.