Deadlock retry logic

Deadlock_Retry.sql

DECLARE @Retry INT;
SET @Retry = 3;

SET DEADLOCK_PRIORITY LOW;
WHILE (@Retry > 0)
BEGIN
	BEGIN TRY
	
		--Code that can deadlock
		
		SET @Retry = -1;
	END TRY
	BEGIN CATCH
		IF (ERROR_NUMBER() = 1205) -- If deadlock victim error, then reduce retry count for next retry. 
		BEGIN
			SET @Retry = @Retry - 1;
			IF (@Retry = 0)
				THROW; --Max retry reached rethrow deadlock error
		END
		ELSE -- If some other error occurred, then exit retry WHILE loop.
		BEGIN
			SET @Retry = -1;
			THROW;
		END
	END CATCH
END
SET DEADLOCK_PRIORITY NORMAL;