Em primeiro lugar, 1 thread selecionando uma grande quantidade de dados de uma tabela.
Então, antes que a consulta SELECT ainda não tenha terminado, outro thread exclui os dados dessa tabela.
Isso faz com que a consulta SELECT falhe com o erro errorCode=1205 sqlState=40001 e a mensagem de que ocorreu um impasse.
Eu pensei que a segunda transação deve esperar até que a primeira seja concluída, ou a primeira transação deve retornar dados sem as alterações feitas pela segunda transação. Como posso evitar isso?
Não posso alterar a ordem das transações ou controlá-las de alguma forma (existem muitos usuários do aplicativo e eles podem fazer o que quiserem em qualquer ordem a qualquer momento). Por que isso acontece?
É assim que as consultas se parecem (eu as ofusquei um pouco):
SELECT distinct P.pID, (LastNAME+' '+FirstName+' '+SurName) as NAME, 'person' as Type, CA.OrgName, PC.personemail as Email
FROM Persons P
INNER JOIN WORK PW ON (P.mainwork = PW.pwId)
INNER JOIN CONTACT PC ON (PC.pID = P.pID)
LEFT OUTER JOIN ORG CA ON (CA.orgId = PW.orgId)
WHERE (P.Status = ?)
e
DELETE FROM persons WHERE id = 1234
Basicamente, isso não é verdade. Seu banco de dados (ou de qualquer pessoa) não seria muito eficiente se apenas uma pessoa pudesse fazer algo com ele por vez.
Simplesmente:
Uma consulta SELECT está recebendo bloqueios compartilhados em todas as linhas que está examinando, portanto, embora não vá entrar em conflito com outra consulta SELECT, ela definitivamente entrará em conflito com bloqueios exclusivos de um DELETE. Como a consulta SELECT não está atualizando nada, geralmente será a vítima do impasse, pois provavelmente será o menos caro para reverter.
Por que isso acontece especificamente no caso que você está falando não é possível dizer sem maiores informações. Adicionarei alguns links para detecção de deadlock no final, mas suspeito que esse não seja o objetivo principal da pergunta, então ...
O que fazer?
É difícil saber qual será a melhor abordagem para você (sem muito mais informações). É possível que você deva dar uma olhada em seus níveis de isolamento.
Você pode usar READ_COMMITTED_SNAPSHOT, mas isso pode resultar em mais sobrecarga, ou usar READ_UNCOMMITED/usar uma dica NOLOCK - geralmente não sou fã dessas opções, pois os dados retornados podem não ser realmente o que você deseja, mas podem ser adequados em seu caso. É difícil dizer exatamente o que seria melhor, pois dependerá de muitas variáveis diferentes sobre o seu sistema e seu uso.
A melhor coisa que você pode fazer a esse respeito, porém, é ler sobre os níveis de isolamento. Estas são algumas postagens / fontes semelhantes que o iniciarão e entrarão em detalhes com mais detalhes.
MSDN - Como uma instrução select pode resultar em ser escolhida como vítima de impasse? , SQL Server central , MSDN - Compreendendo os níveis de isolamento
Mas...
Não se apresse em fazer uma alteração apenas para concluir suas consultas. Um caminho mais apropriado para explorar pode ser suas próprias consultas.
Por que a instrução SELECT está demorando tanto para ser executada? Por que as coisas estão sendo excluídas da tabela com tanta frequência? Talvez algum ajuste de consulta simples faça com que todos sejam concluídos mais rapidamente e reduza a chance de um impasse. Talvez você precise fazer algum ajuste geral de desempenho em seu servidor de banco de dados (não vou entrar em detalhes sobre isso aqui, pois está saindo do assunto).
Você precisa garantir que suas consultas sejam adequadas para fins comerciais, em vez de apenas 'concluir a todo custo'. Os bancos de dados são uma ferramenta comercial antes de mais nada e precisam contornar isso (por mais que não gostemos disso)!
Apenas seguir em frente e alterar o nível de isolamento, ou mesmo apenas usar o NOLOCK, pode ter grandes impactos em seu banco de dados e também nos resultados exatos que as consultas retornam - o que pode ter impactos nos negócios.
Também...
Para ajudar na detecção de deadlock, dê uma olhada nesta postagem no StackOverflow e verifique as ferramentas de informações de deadlock no site do MSDN, que explicarão como usar sinalizadores de rastreamento para fornecer mais informações sobre seus deadlocks.
Finalmente...
Conselhos mais específicos podem ser oferecidos se você colocar a versão do SQL Server em sua postagem, e mais alguns detalhes sobre o que exatamente é a consulta SELECT (para ver se há algo que possa estar fazendo com que ela demore tanto e esteja aberta por estar bloqueada) .