MSSQL – How to Handle and Retry Deadlocks

Example Code below

–=====================================
— Windows/Session #1
–=====================================
SELECT @@SPID

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ‘SampleTable’)
DROP TABLE SampleTable

CREATE TABLE [SampleTable]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Value] [varchar](100) NULL,
[DateChanged] [datetime] DEFAULT(GETDATE()) NULL,
CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED ([Id] ASC)
)

INSERT INTO SampleTable(Name, Value)
SELECT ‘Name1’, ‘Value1’
UNION ALL
SELECT ‘Name2’, ‘Value2’
UNION ALL
SELECT ‘Name3’, ‘Value3′

SELECT * FROM SampleTable

–=====================================
— Windows/Session #2
–=====================================

—————————————————–
— This window/session is default CASE DEADLOCK —
—————————————————–

SET DEADLOCK_PRIORITY LOW

BEGIN TRAN
UPDATE SampleTable SET Name = Name + Name WHERE ID=2
WAITFOR DELAY ’00:00:10′
UPDATE SampleTable SET Name = Name + Name WHERE ID=1
COMMIT TRAN

–=====================================
— Windows/Session #3
–=====================================

—————————————————–
— This window/session is default CASE DEADLOCK —
—————————————————–

SET DEADLOCK_PRIORITY NORMAL

BEGIN TRAN
UPDATE SampleTable SET Name = Name + Name WHERE ID=1
WAITFOR DELAY ’00:00:10′
UPDATE SampleTable SET Name = Name + Name WHERE ID=2
COMMIT TRAN

–=====================================
— Windows/Session #4
–=====================================

—————————————————–
— This Is a Retry Block Template for
— INSERT/UPDATE/DELETE Scenarios
—————————————————–

SET DEADLOCK_PRIORITY LOW

DECLARE @RETRY_COUNT_CURRENT INT
DECLARE @RETRY_COUNT_MAXIMUM INT
DECLARE @ERROR_NUM INT
DECLARE @ERROR_MSG NVARCHAR(MAX)

SET @ERROR_NUM = 0
SET @RETRY_COUNT_CURRENT = 0
SET @RETRY_COUNT_MAXIMUM = 3

WHILE @RETRY_COUNT_CURRENT lessthan @RETRY_COUNT_MAXIMUM
BEGIN
BEGIN TRANSACTION
BEGIN TRY

UPDATE SampleTable SET Name = Name + Name WHERE ID=2
WAITFOR DELAY ’00:00:10′
UPDATE SampleTable SET Name = Name + Name WHERE ID=1
COMMIT

BREAK
END TRY
BEGIN CATCH
SELECT @ERROR_NUM = ERROR_NUMBER(), @ERROR_MSG = ERROR_MESSAGE()
PRINT @ERROR_MSG
PRINT @ERROR_NUM
ROLLBACK
SET @RETRY_COUNT_CURRENT = @RETRY_COUNT_CURRENT + 1
WAITFOR DELAY ’00:00:05′
CONTINUE
END CATCH;
END

–=====================================
— Windows/Session #5
–=====================================

—————————————————–
— This Is a Retry Block Template for SELECT victims
— SELECTs can be deadlock victims, in which case you
— have to buffer/restrict the ResultSet of the dead
— lock run, then retry, this requires temp tables
—————————————————–
SET DEADLOCK_PRIORITY LOW

DECLARE @RETRY_COUNT_CURRENT INT
DECLARE @RETRY_COUNT_MAXIMUM INT
DECLARE @ERROR_NUM INT
DECLARE @ERROR_MSG NVARCHAR(MAX)

SET @ERROR_NUM = 0
SET @RETRY_COUNT_CURRENT = 0
SET @RETRY_COUNT_MAXIMUM = 3

— Buffer the output, in order to clear the buffer in case of deadlock
— This is required to suppress any streaming output to the caller
CREATE TABLE #TABLE_01
(
Id INT,
Name VARCHAR(100),
Value VARCHAR(100),
CreatedOn DATETIME
)

WHILE @RETRY_COUNT_CURRENT lessthan @RETRY_COUNT_MAXIMUM
BEGIN
BEGIN TRANSACTION
BEGIN TRY
TRUNCATE TABLE #TABLE_01

INSERT INTO #TABLE_01
SELECT * FROM SampleTable

COMMIT

SELECT * FROM #TABLE_01
BREAK
END TRY
BEGIN CATCH
SELECT @ERROR_NUM = ERROR_NUMBER(), @ERROR_MSG = ERROR_MESSAGE()
PRINT @ERROR_MSG
PRINT @ERROR_NUM
ROLLBACK
SET @RETRY_COUNT_CURRENT = @RETRY_COUNT_CURRENT + 1
WAITFOR DELAY ’00:00:05’
CONTINUE
END CATCH;
END