Usando o SQL Server 2008 R2:
No momento, estou tentando rastrear a causa de um problema de impasse que temos. Não tenho certeza para onde virar. Aqui está a configuração.
TABLE Scores:
id INT IDENTITY(1,1),
first_name VARCHAR(50),
last_name VARCHAR(50),
player_id VARCHAR(50),
score INT
PRIMARY KEY/CLUSTERED INDEX:
id
NON-CLUSTERED INDEX:
first_name, last_name INCLUDES id, player_id, score
Um aplicativo multithread processa mensagens e, em seguida, chama um dos dois procedimentos armazenados para cada mensagem.
Se o aplicativo não tiver processado o usuário, ele chamará um procedimento armazenado que insere uma linha na tabela.
Se o aplicativo tiver processado o usuário, ele atualizará qualquer um ou todos os first_name, last_name, scores, com base em first_namee last_name player_idnão será alterado.
Haverá apenas uma linha na tabela por player_id.
O thread que o aplicativo processa as mensagens é escolhido com base no player_id, portanto, dois threads NÃO processarão mensagens para o mesmo player ao mesmo tempo.
As instruções INSERT e UPDATE estão usando a WITH (ROWLOCK)diretiva.
Aqui está o problema:
O processo está causando deadlocks com o thread 1 bloqueando o índice clusterizado primário e o thread 2 bloqueando o índice não clusterizado.
Não entendo por que os bloqueios estão ocorrendo - as instruções UPDATE e INSERT estão usando bloqueios de linha e foi testado e comprovado que os threads não estão acessando as mesmas linhas.
O que eu poderia fazer para corrigir esse problema?
Ressalvas:
Devem existir dois stored procedures, um de atualização e outro de inserção. Por mais que eu queira mudar para um único MERGE, isso não vai acontecer. : /
A atualização de qualquer uma das chaves de índice NC é uma atualização que pode causar um impasse. Este é o impasse típico de leitura/gravação devido a diferentes caminhos de acesso. Mesmo se você atualizar chaves diferentes, isso não elimina conflitos porque os bloqueios estão em hashes de chave , consulte %%lockres%% marcador mágico de probabilidade de colisão: 16.777.215 . No entanto, você descreve o problema como aparecendo 'imediatamente', o que sugere fortemente que alguns outros fatores estão em jogo, e podemos apenas especular sobre a falta de informações reais.
Claro que seria muito mais útil capturar o gráfico do impasse e postá-lo aqui. A discussão sobre uma mera descrição do impasse tem apenas valor limitado, pois sua descrição pode ou não ser factual, seu entendimento pode ou não estar correto e nossa interpretação pode ou não ser precisa.
Não é possível confirmar sem um rastreamento de impasse, mas parece que você já obteve a resposta.
Provavelmente, um de seus procedimentos está obtendo um bloqueio compartilhado primeiro no índice não clusterizado e, em seguida, no índice clusterizado. O outro está fazendo o inverso, obtendo um bloqueio compartilhado primeiro no agrupado, seguido do não agrupado.