Quais são as principais causas de deadlocks e podem ser evitadas?

rastrear impasses é o mais fácil dos dois:

Por padrão, os deadlocks não são gravados no log de erros. Você pode fazer com que o SQL grave impasses no log de erros com os sinalizadores de rastreamento 1204 e 3605.

Gravar informações de impasse no log de erros do SQL Server: DBCC TRACEON(-1, 1204, 3605)

Desligue: DBCC TRACEOFF(-1, 1204, 3605)

Consulte "Solucionando problemas de deadlocks" para obter uma discussão sobre o sinalizador de rastreamento 1204 e a saída que você obterá quando for ativado. https://msdn.microsoft.com/en-us/library/ms178104.aspx

A prevenção é mais difícil, essencialmente você deve estar atento ao seguinte:

O bloco de código 1 bloqueia o recurso A e, em seguida, o recurso B, nessa ordem.

O bloco de código 2 bloqueia o recurso B e, em seguida, o recurso A, nessa ordem.

Esta é a condição clássica em que um deadlock pode ocorrer, se o bloqueio de ambos os recursos não for atômico, o Bloco de Código 1 pode bloquear A e ser antecipado, então o Bloco de Código 2 bloqueia B antes que A recupere o tempo de processamento. Agora você tem impasse.

Para evitar essa condição, você pode fazer algo como o seguinte

Bloco de código A (pseudo código)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Bloco de código B (pseudo código)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

não esquecendo de desbloquear A e B quando terminar com eles

isso evitaria o bloqueio entre o bloco de código A e o bloco de código B

Do ponto de vista do banco de dados, não tenho certeza de como evitar essa situação, pois os bloqueios são tratados pelo próprio banco de dados, ou seja, bloqueios de linha/tabela ao atualizar dados. Onde eu vi a maioria dos problemas ocorrerem é onde você viu o seu, dentro de um cursor. Os cursores são notoriamente ineficientes, evite-os se possível.

meus artigos favoritos para ler e aprender sobre deadlocks são: Simple Talk - Rastreie deadlocks e SQL Server Central - Using Profiler to solve deadlocks . Eles lhe darão amostras e conselhos sobre como lidar com uma situação ruim.

Resumindo, para resolver um problema atual, eu faria as transações envolvidas mais curtas, tiraria a parte desnecessária delas, cuidaria da ordem de uso dos objetos, veria qual nível de isolamento é realmente necessário, não leria desnecessário dados...

Mas é melhor ler os artigos, eles serão muito mais legais em conselhos.

Às vezes, um impasse pode ser resolvido adicionando indexação, pois permite que o banco de dados bloqueie registros individuais em vez de toda a tabela, reduzindo a contenção e a possibilidade de as coisas ficarem congestionadas.

Por exemplo, no InnoDB :

Se você não tiver índices adequados para sua instrução e o MySQL precisar varrer toda a tabela para processar a instrução, cada linha da tabela ficará bloqueada, o que, por sua vez, bloqueará todas as inserções de outros usuários na tabela. É importante criar bons índices para que suas consultas não examinem desnecessariamente muitas linhas.

Outra solução comum é desativar a consistência transacional quando não for necessária ou alterar seu nível de isolamento , por exemplo, um trabalho de longa duração para calcular estatísticas ... uma resposta próxima geralmente é suficiente, você não precisa de números precisos, como eles estão mudando debaixo de você. E se levar 30 minutos para ser concluído, você não quer que ele interrompa todas as outras transações nessas tabelas.

...

Quanto a rastreá-los, depende do software de banco de dados que você está usando.

Só para desenvolver na coisa do cursor. é realmente muito ruim. Ele bloqueia a tabela inteira e processa as linhas uma a uma.

É melhor percorrer as linhas na forma de um cursor usando um loop while

No loop while, uma seleção será realizada para cada linha do loop e o bloqueio ocorrerá em apenas uma linha por vez. O restante dos dados na tabela é livre para consulta, reduzindo assim as chances de ocorrer um deadlock.

Além disso, é mais rápido. Faz você se perguntar por que existem cursores de qualquer maneira.

Veja um exemplo desse tipo de estrutura:

DECLARE @LastID INT = (SELECT MAX(ID) FROM Tbl)
DECLARE @ID     INT = (SELECT MIN(ID) FROM Tbl)
WHILE @ID <= @LastID
    BEGIN
    IF EXISTS (SELECT * FROM Tbl WHERE ID = @ID)
        BEGIN
        -- Do something to this row of the table
        END

    SET @ID += 1  -- Don't forget this part!
    END

Se o seu campo de ID for esparso, você pode querer puxar uma lista separada de IDs e iterar por ela:

DECLARE @IDs TABLE
    (
    Seq INT NOT NULL IDENTITY PRIMARY KEY,
    ID  INT NOT NULL
    )
INSERT INTO @IDs (ID)
    SELECT ID
    FROM Tbl
    WHERE 1=1  -- Criteria here

DECLARE @Rec     INT = 1
DECLARE @NumRecs INT = (SELECT MAX(Seq) FROM @IDs)
DECLARE @ID      INT
WHILE @Rec <= @NumRecs
    BEGIN
    SET @ID = (SELECT ID FROM @IDs WHERE Seq = @Seq)

    -- Do something to this row of the table

    SET @Seq += 1  -- Don't forget this part!
    END

A falta de uma chave primária não é o problema. Pelo menos por si só. Primeiro, você não precisa de um primário para ter índices. Segundo, mesmo se você estiver fazendo varreduras de tabela (o que deve acontecer se sua consulta em particular não estiver usando um índice, um bloqueio de tabela não causará um deadlock por si só. Um processo de gravação esperaria por uma leitura e um processo de leitura esperar por uma gravação e, é claro, as leituras não precisariam esperar uma pela outra.

Adicionando às outras respostas, o nível de isolamento da transação é importante, porque a leitura repetível e a serialização são o que faz com que os bloqueios de 'leitura' sejam mantidos até o final da transação. Bloquear um recurso não causa um deadlock. Mantê-lo trancado sim. As operações de gravação sempre mantêm seus recursos bloqueados até o final da transação.

Minha estratégia de prevenção de bloqueio favorita é usar os recursos de 'instantâneo'. O recurso Read Committed Snapshot significa que as leituras não usam bloqueios! E se você precisar de mais controle do que 'Leitura confirmada', há o recurso 'Nível de isolamento de captura instantânea'. Este permite que uma transação serializada (usando os termos do MS aqui) ocorra sem bloquear os outros jogadores.

Por fim, uma classe de deadlocks pode ser evitada usando um bloqueio de atualização. Se você ler e segurar a leitura (HOLD, ou usando Repeatable Read), e outro processo fizer o mesmo, então ambos tentarem atualizar os mesmos registros, você terá um deadlock. Mas se ambos solicitarem um bloqueio de atualização, o segundo processo aguardará o primeiro, enquanto permite que outros processos leiam os dados usando bloqueios compartilhados até que os dados sejam realmente gravados. Obviamente, isso não funcionará se um dos processos ainda solicitar um bloqueio HOLD compartilhado.

Embora os cursores sejam lentos no SQL Server, você pode evitar o bloqueio em um cursor puxando os dados de origem do cursor para uma tabela Temp e executando o cursor nela. Isso evita que o cursor bloqueie a tabela de dados real e os únicos bloqueios que você obtém são para as atualizações ou inserções realizadas dentro do cursor, que são mantidas apenas pela duração da inserção/atualização e não pela duração do cursor.