TSQL Exception Handling and Deadlocks

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

go

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

go

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

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

go

-- 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

DECLARE @retry INT

SET @retry = 5 -- number of retries

WHILE (@retry > 0)

BEGIN

   BEGIN TRY

      BEGIN TRANSACTION

      UPDATE jukebox.artists_tbl SET artistname='Chick Corea Electric Band' WHERE id = 2

      WAITFOR DELAY '00:00:10'

      UPDATE jukebox.artists_tbl SET artistname='Weather Report' WHERE id = 1

      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

You need to be quick and run SESSION #1 and SESSION #2 codes in two separate management studio connections. Give it a try and let me know if this scenario works for you.