Handling concurrent access to a key table without deadlocks in SQL Server
First, I would avoid making a round trip to the database for every value. For example, if your application knows it needs 20 new IDs, do not make 20 round trips. Make only one stored procedure call, and increment the counter by 20. Also it might be better to split your table into multiple ones.
It is possible to avoid deadlocks altogether. I have no deadlocks at all in my system. There are several ways to accomplish that. I will show how I would use sp_getapplock to eliminate deadlocks. I have no idea if this will work for you, because SQL Server is closed source, so I cannot see the source code, and as such I do not know if I have tested all possible cases.
The following describes what works for me. YMMV.
First, let us start with a scenario where we always get a considerable amount of deadlocks. Second, we shall use sp_getapplock eliminate them. The most important point here is to stress test your solution. Your solution may be different, but you need to expose it to high concurrency, as I will demonstrate later.
Prerequisites
Let us set up a table with some test data:
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
The following two procedures are quite likely to embrace in a deadlock:
CREATE PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
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
CREATE PROCEDURE dbo.UpdateCounter2 @Key2 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
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
Reproducing deadlocks
The following loops should reproduce more than 20 deadlocks every time you run them. If you get less than 20, increase the number of iterations.
In one tab, run this;
DECLARE @i INT, @DeadlockCount INT;
SELECT @i=0, @DeadlockCount=0;
WHILE @i<5000 BEGIN ;
BEGIN TRY
EXEC dbo.UpdateCounter1 @Key1=123456;
END TRY
BEGIN CATCH
SET @DeadlockCount = @DeadlockCount + 1;
ROLLBACK;
END CATCH ;
SET @i = @i + 1;
END;
SELECT 'Deadlocks caught: ', @DeadlockCount ;
In another tab, run this script.
DECLARE @i INT, @DeadlockCount INT;
SELECT @i=0, @DeadlockCount=0;
WHILE @i<5000 BEGIN ;
BEGIN TRY
EXEC dbo.UpdateCounter2 @Key2=123456;
END TRY
BEGIN CATCH
SET @DeadlockCount = @DeadlockCount + 1;
ROLLBACK;
END CATCH ;
SET @i = @i + 1;
END;
SELECT 'Deadlocks caught: ', @DeadlockCount ;
Make sure you start both within a couple of seconds.
Using sp_getapplock to eliminate deadlocks
Alter both procedures, rerun the loop, and see that you no longer have deadlocks:
ALTER PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';
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 ;
EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';
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
Using a table with one row to eliminate deadlocks
Instead of invoking sp_getapplock, we can modify the following table:
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 we have this table created and populated, we can replace the following line
EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';
with this one, in both procedures:
UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;
You can rerun the stress test, and see for yourself that we have no deadlocks.
Conclusion
As we have seen, sp_getapplock can be used to serialize access to other resources. As such it can be used to eliminate deadlocks.
Of course, this can significantly slow down modifications. To address that, we need to choose the right granularity for the exclusive lock, and whenever possible, work with sets instead of individual rows.
Before using this approach you need to stress test it yourself. First, you need to make sure you get at least a couple dozen deadlocks with your original approach. Second, you should get no deadlocks when you rerun the same repro script using modified stored procedure.
In general, I do not think there is a good way to determine if your T-SQL is safe from deadlocks just by looking at it or looking at the execution plan. IMO the only way to determine if your code is prone to deadlocks is to expose it to high concurrency.
Good luck with eliminating deadlocks! We do not have any deadlocks in our system at all, which is great for our work-life balance.
Use of the XLOCK hint on either your SELECT approach or the following UPDATE should be immune to this type of deadlock:
DECLARE @Output TABLE ([NewId] INT);
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
UPDATE
dbo.tblIDs WITH (XLOCK)
SET
LastID = LastID + 1
OUTPUT
INSERTED.[LastId] INTO @Output
WHERE
IDName = @IDName;
IF(@@ROWCOUNT = 1)
BEGIN
SELECT @NewId = [NewId] FROM @Output;
END
ELSE
BEGIN
SET @NewId = 1;
INSERT dbo.tblIDs
(IDName, LastID)
VALUES
(@IDName, @NewId);
END
SELECT [NewId] = @NewId ;
COMMIT TRANSACTION;
Will return with a couple of other variants (if not beaten to it!).
Mike Defehr showed me an elegant way to accomplish this in a very lightweight way:
ALTER PROCEDURE [dbo].[GetNextID](
@IDName nvarchar(255)
)
AS
BEGIN
/*
Description: Increments and returns the LastID value from tblIDs for a given IDName
Author: Max Vernon / Mike Defehr
Date: 2012-07-19
*/
DECLARE @Retry int;
DECLARE @EN int, @ES int, @ET int;
SET @Retry = 5;
DECLARE @NewID int;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET NOCOUNT ON;
WHILE @Retry > 0
BEGIN
BEGIN TRY
UPDATE dbo.tblIDs
SET @NewID = LastID = LastID + 1
WHERE IDName = @IDName;
IF @NewID IS NULL
BEGIN
SET @NewID = 1;
INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID);
END
SET @Retry = -2; /* no need to retry since the operation completed */
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
SET @Retry = @Retry - 1;
ELSE
BEGIN
SET @Retry = -1;
SET @EN = ERROR_NUMBER();
SET @ES = ERROR_SEVERITY();
SET @ET = ERROR_STATE()
RAISERROR (@EN,@ES,@ET);
END
END CATCH
END
IF @Retry = 0 /* must have deadlock'd 5 times. */
BEGIN
SET @EN = 1205;
SET @ES = 13;
SET @ET = 1
RAISERROR (@EN,@ES,@ET);
END
ELSE
SELECT @NewID AS NewID;
END
GO
(For completeness, here is the table associated with the stored proc)
CREATE TABLE [dbo].[tblIDs]
(
IDName nvarchar(255) NOT NULL,
LastID int NULL,
CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED
(
[IDName] ASC
) WITH
(
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, FILLFACTOR = 100
)
);
GO
This is the execution plan for the latest version:
And this is the execution plan for the original version (deadlock susceptible):
Clearly, the new version wins!
For comparison, the intermediate version with the (XLOCK) etc, produces the following plan:
I'd say that's a win! Thanks for everyone's help!