TSQL Exception Handling and Deadlocks

Article
01/12/2005

You probably hear about exception handling in TSQL already but here is a neat use of the feature: TSQL exception handling is very close to the top when it comes to popularity of the new TSQL features. The ability to handle exceptions without having to put an IF @@ERROR!=0 is extremely valuable for ease of coding. There is one pattern, however, that I really found extremely helpful with the new TRY … CATCH: the ability to handle deadlocks and implement a retry logic on the server side.

Quick sample code with a standard disclaimer. This is not intended to demonstrate any best practices but is purely to demonstrate the ability of the exception handling in TSQL.

CREATE SCHEMA jukebox

CREATE TABLE jukebox.artists_tbl(id INT IDENTITY(1,1) PRIMARY KEY,artistname NVARCHAR(128) not null)

INSERT INTO jukebox.artists_tbl VALUES('Spyro Gyra')

INSERT INTO jukebox.artists_tbl VALUES('Vital Information')

-- SESSION #1

DECLARE @retry INT

SET @retry = 5 -- number of retries

WHILE (@retry > 0)

BEGIN

BEGIN TRY

BEGIN TRANSACTION

UPDATE jukebox.artists_tbl SET artistname='Paco De Lucía' WHERE id = 1

WAITFOR DELAY '00:00:10'

UPDATE jukebox.artists_tbl SET artistname='Jan Garbarek' WHERE id = 2

SET @retry=-1

END TRY

BEGIN CATCH

if (error_number() = 1205)

SET @retry=@retry-1 --retry

else

SET @retry=-1 --not a deadlock

PRINT ERROR_NUMBER()

PRINT ERROR_SEVERITY()

PRINT ERROR_MESSAGE()

PRINT ERROR_STATE()

PRINT XACT_STATE()

if (@@trancount>0)

ROLLBACK TRANSACTION

END CATCH

END

IF (@@trancount>0)

COMMIT TRANSACTION

-- SESSION #2