Deadlock no mesmo recurso chamando o mesmo procedimento

O problema é que o HOLDLOCKestá criando o bloqueio "Compartilhado" (os Mode: Sbloqueios) nesse recurso durante a transação. Isso não impede que outros processos (como o mesmo proc executado em outra sessão) coloquem seu próprio bloqueio "Compartilhado" no mesmo recurso. Mas então ambas as sessões chegam à UPDATEinstrução que está tentando converter (ou seja, atualizar) seu bloqueio para "Intent eXclusive" (os Mode: IXbloqueios), o que não pode ser feito devido à outra sessão ainda manter seu bloqueio "Compartilhado".

Se o objetivo do HOLDLOCKé reservar a linha para essa sessão (para que outros não possam modificá-la), você precisa impedir que outras sessões possam colocar seu próprio bloqueio "Compartilhado" nesse mesmo recurso (ao mesmo tempo, é claro).

Uma maneira de fazer isso é alterando o bloqueio HOLDLOCKpara UPDLOCK(ou seja, "Atualizar"). Isso permite que outras transações tenham o mesmo bloqueio no mesmo recurso (como HOLDLOCK), mas uma dessas transações pode converter/atualizar para um XLOCKque forçará todas as outras transações (ainda com seu UPDLOCK) a esperar até que a transação termine (diferente de HOLDLOCK) . Além disso, enquanto o bloqueio ainda é um bloqueio de "Atualização" (ou seja, antes de converter em um bloqueio "exclusivo"), outras transações/sessões podem ler esse recurso (também como HOLDLOCK).

Se houver pelo menos uma linha para atualizar e o bloqueio for convertido em um bloqueio "exclusivo" e você tiver outros processos que precisam acessar essa tabela sem esperar que todas essas atualizações sejam concluídas, você terá algumas opções:

1. use WITH (NOLOCK)dica (ler modificado, mas ainda não confirmado, portanto, pode reverter a linha)
2. use WITH (READPAST)dica (pule a linha inteiramente como se ela não existisse)
3. olhar para SNAPSHOT ISOLATION(ler a linha atual / não modificada)

O teste a seguir deve ilustrar tudo isso:

Guia de consulta A

Cole o seguinte em uma guia de consulta no SSMS (ou seja, sessão) e execute apenas as instruções dentro da citação de bloco (por enquanto; ou seja USE, CREATE TABLE, e INSERT)

USE [tempdb];

/*
-- DROP TABLE dbo.test;

USE [tempdb];
CREATE TABLE dbo.test
(
  ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [col1] INT NOT NULL
);
INSERT INTO dbo.test ([col1]) VALUES (11), (13), (15);
*/

DECLARE @ID INT;

BEGIN TRAN;

SELECT @ID = [ID] FROM dbo.test WITH (ROWLOCK, UPDLOCK) WHERE [col1] = 13;
WAITFOR DELAY '00:00:10.000';

UPDATE dbo.test SET [col1] = 88 WHERE [ID] = @ID;
WAITFOR DELAY '00:00:5.000';

ROLLBACK TRAN;

Guia de consulta B

Cole o seguinte em outra guia de consulta no SSMS (ou seja, outra sessão), mas não a execute (ainda).

USE [tempdb];

DECLARE @ID INT;

BEGIN TRAN;

SELECT @ID = [ID] FROM dbo.test WITH (ROWLOCK, UPDLOCK) WHERE [col1] = 15;
WAITFOR DELAY '00:00:10.000';

UPDATE dbo.test SET [col1] = 999 WHERE [ID] = @ID;
WAITFOR DELAY '00:00:5.000';

ROLLBACK TRAN;

Guia de consulta C

Cole o seguinte em outra guia de consulta no SSMS (ou seja, outra sessão), mas não a execute (ainda).

USE [tempdb];

SELECT * FROM sys.dm_tran_locks --WHERE [request_session_id] IN (x, y);

SELECT 'readpast', * FROM dbo.test WITH (READPAST);

SELECT 'nolock', * FROM dbo.test WITH (NOLOCK);
GO -- separate next query as it will lock up once an UPDATE statement starts

SELECT 'no hint', * FROM dbo.test

Teste principal

1. Vá para "Consultar Tab A", pressione F5.
2. Espere um ou dois segundos, então vá para "Query Tab B", pressione F5.
3. Vá para "Query Tab C", pressione F5.
   1. Você deve ver 6 ou mais linhas para a primeira consulta (ou seja, sys.dm_tran_locks). Se você tem um monte de coisas acontecendo no sistema, descomente a WHEREcláusula e substitua xe ypelos IDs de sessão (ou seja, SPIDs) para "Query Tab A" e "Query Tab B".
   2. Você também deve obter 3 linhas de volta para cada uma das 3 SELECT ... FROM dbo.test ...consultas. Até agora, apenas bloqueios de "Atualização" foram feitos.
   3. Espere um ou dois segundos e bata F5novamente. Faça isso algumas vezes até que a consulta final não volte imediatamente.
   4. Assim que "Query Tab A" chegar à UPDATEinstrução, por alguns segundos:
      1. você receberá apenas 2 linhas de volta da primeira SELECT ... FROM dbo.test ...consulta (ou seja READPAST)
      2. você obterá todas as 3 linhas de volta da próxima consulta (ou seja NOLOCK, ), mas a linha #3 mostrará o valor não confirmado.
      3. a consulta final não retornará (ou seja, sem dicas), pois agora está bloqueada pelo bloqueio "exclusivo" (é por isso que GOexiste, logo antes desta consulta, para que os outros possam retornar seus conjuntos de resultados enquanto esta fica bloqueado)