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
