Gracefully Handling Deadlocks

In some situations, deadlocks may need to be dealt with not by changing the source of the deadlock, but by changing handling the deadlock gracefully. An example of this may be an external subscription that runs on a schedule deadlocking with another process. If the subscription deadlocks then it would be ok to just kill it and not allow it to interfere with the source process. However if it raises an error, then the source of publication pull will fail.

First to make sure one of the deadlocking procedures becomes a victim over the other, a deadlock priority can be set in order to tell SQL Server which process it should kill first. If the priority is set to “high”, then the procedure will not be the victim of the deadlock, (that is, if the other process does not have a priority).

Now to handle the deadlock gracefully, wrap it in a try / catch.
If the process is the victim of the deadlock, can simply be rolled back without the error bubbling up to the end-user.

To give an example of this, let’s simulate a deadlock by creating two tables and write to them from separate query windows.

First we’ll setup the tables.

[cc lang=”sql”]
——— setup ———-
CREATE TABLE ##t1
(
i int
)
CREATE TABLE ##t2
(
i int
)

INSERT INTO ##t1
SELECT 4

INSERT INTO ##t2
SELECT 5
——— /setup ———-
[/cc]

Now copy the two code snippets into separate windows and see the results.

[cc lang=”sql”]

——— copy into window 1 ———-
SET DEADLOCK_PRIORITY N’High’;
BEGIN TRAN
BEGIN TRY
UPDATE ##t2 SET i = 5 WHERE i = 5
WAITFOR DELAY ’00:00:10′
UPDATE ##t1 SET i = 4 WHERE i = 1
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK
SELECT ‘Rolled back’
END CATCH
——— /copy into window 1 ———-

——— copy into window 2 ———-
BEGIN TRAN
BEGIN TRY
UPDATE ##t1 SET i = 4 WHERE i = 4
WAITFOR DELAY ’00:00:10′
UPDATE ##t2 SET i = 5 WHERE i = 5
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK
SELECT ‘Rolled back’
END CATCH
——— /copy into window 2 ———-
[/cc]

The transaction that does not contain the High priority is rolled back gracefully.