Causes and Solutions for Deadlocks - SQL Server Query Performance Tuning - page 434

The SET statement allows you to mark a session as a HIGH deadlock priority, too. This won't prevent deadlocks
on a given session, but it will reduce the likelihood of a given session being picked as the victim. You can even set the
priority level to a number value from -10 for the lowest priority or to 10 for the highest.

■ setting the deadlock priority is not something that should be applied promiscuously. You could accidently
set the priority on a report that causes mission-critical processes to be chosen as a victim. Careful testing is necessary
with this setting.

Caution

In the event of a tie, one of the processes is chosen as a victim and rolled back as if it had the least cost. Some
processes are invulnerable to being picked as a deadlock victim. These processes are marked as such and will never
be chosen as a deadlock victim. The most common example that I've seen occurs when processes are already involved
in a rollback.

Using Error Handling to Catch a Deadlock
When SQL Server chooses a session as a victim, it raises an error with the error number. You can use the TRY/CATCH
construct within T-SQL to handle the error. SQL Server ensures the consistency of the database by automatically
rolling back the transaction of the victim session. The rollback ensures that the session is returned to the same state
it was in before the start of its transaction. On determining a deadlock situation in the error handler, it is possible to
attempt to restart the transaction within T-SQL a number of times before returning the error to the application.
Take the following T-SQL statement as an example of one method for handling a deadlock error:

DECLARE @retry AS TINYINT = 1,
@retrymax AS TINYINT = 2,
@retrycount AS TINYINT = 0;
WHILE @retry = 1
AND @retrycount <= @retrymax
BEGIN
SET @retry = 0;
BEGIN TRY
UPDATE HumanResources.Employee
SET LoginID = '54321'
WHERE BusinessEntityID = 100;
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 1205)
BEGIN
SET @retrycount = @retrycount + 1;
SET @retry = 1;
END
END CATCH
END

The TRY/CATCH methodology allows you to capture errors. You can then check the error number using the
ERROR_NUMBER() function to determine whether you have a deadlock. Once a deadlock is established, it's possible to
try restarting the transaction a set number of times—two, in this case. Using error trapping will help your application
deal with intermittent or occasional deadlocks, but the best approach is to analyze the cause of the deadlock and
resolve it, if possible.