Sql-server – Deadlocks in SQL Server – how to avoid

deadlocksql server

As per the code mentioned below, I am trying to understand how 'Deadlock' can be avoided with the help of a table and a single row. Could someone help me understand how this technic can help avoid deadlock situations.?

Disclaimer:
I found this from an answer to a different but relevant question in this forum but I could not add a comment on that thread.. Hence thought to raise it as a question by itself..

The idea is to – Instead of invoking sp_getapplock, we can use the following table to avoid deadlocks

CREATE TABLE dbo.DeadlockTestMutex(
ID INT NOT NULL,
CONSTRAINT PK_DeadlockTestMutex PRIMARY KEY(ID),
Toggle INT NOT NULL);
GO

INSERT INTO dbo.DeadlockTestMutex(ID, Toggle)
VALUES(1,0); 

Once the above table is ready and populated with the data as shown above, use this statement inside your stored procedures

UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1; 

Such that, the stored procedures are as follows:

ALTER PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;

----------
UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;
----------

UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
SET @Key1=@Key1-10000;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
COMMIT;
GO

ALTER PROCEDURE dbo.UpdateCounter2 @Key2 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;

----------
UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;
----------    

SET @Key2=@Key2-10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
SET @Key2=@Key2+10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
COMMIT;
GO 

So, how does adding this one liner can avoid deadlocks?

Here is the code for the table used by the stored procedures..

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY); 
GO 

INSERT INTO dbo.Numbers 
    ( n ) 
        VALUES  ( 1 ); 
GO 
DECLARE @i INT; 
    SET @i=0; 
WHILE @i<21  
    BEGIN 
    INSERT INTO dbo.Numbers 
        ( n ) 
        SELECT n + POWER(2, @i) 
        FROM dbo.Numbers; 
    SET @i = @i + 1; 
    END;  
GO

SELECT n AS ID, n AS Key1, n AS Key2, 0 AS Counter1, 0 AS Counter2
INTO dbo.DeadlockTest FROM dbo.Numbers
GO

ALTER TABLE dbo.DeadlockTest ADD CONSTRAINT PK_DeadlockTest PRIMARY KEY(ID);
GO

CREATE INDEX DeadlockTestKey1 ON dbo.DeadlockTest(Key1);
GO

CREATE INDEX DeadlockTestKey2 ON dbo.DeadlockTest(Key2);
GO

Best Answer

Due to the fact that both stored procedures issue

UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;

before attempting to execute any other code, the first stored procedure to successfully run that update will get an exclusive lock on dbo.DeadlockTestMutex and the other process will wait until that lock is released. This allows each stored procedure to run to full completion without competition from the other stored procedure.