Set (customize) deadlock priority for a specific session (script/TSQL/stored procedure) in SQL Server…

By default, when a deadlock occurs, SQL Server rolls back the least expensive (which will take minimum time and resource to rollback) session. However this may not be a desirable situation in some specific case. You may have to set priority of a specific transaction (session) in a way that it will not rollback even if it is least expensive to rollback in a deadlock situation.

To satisfy this requirement you can use SET DEADLOCK_PRIORITY option in your code as per below examples:

Example to set deadlock priority to HIGH/LOW or NORMAL:

-- Declaration and script components before SET statements

SET DEADLOCK_PRIORITY HIGH; -- Replace HIGH with NORMAL or LOW to set it to NORMAL or LOW

GO

--Script continues…

Example to set deadlock priority using numeric priority:

-- Declaration and script components before SET statements

SET DEADLOCK_PRIORITY 5; -- Replace 5 with any value from -10 to 10 as per prioroty

GO

--Script continues…

Please refer below for explanation of the deadlock priority options with this code:

LOW:  

a. The current session will be the deadlock victim if it is involved in a deadlock and other sessions involved in the deadlock chain have deadlock priority set to either NORMAL or HIGH or to an integer value greater than -5.
b. The current session will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5.
c. The current session is eligible to be the deadlock victim if another session has set deadlock priority set to LOW or to an integer value equal to -5. (If both of the involved session’s deadlock priority is LOW, then the lease expensive session to rollback till be deadlock victim)

NORMAL: NORMAL is the default priority for SQL Server.

           

a. The current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to HIGH or to an integer value greater than 0, but will not be the deadlock victim if the other sessions have deadlock priority set to LOW or to an integer value less than 0.
b. The current session is eligible to be the deadlock victim if another other session has set deadlock priority to NORMAL or to an integer value equal to 0. (If both of the involved session’s deadlock priority is NORMAL, then the lease expensive session to rollback till be deadlock victim. This is default behavior of SQL Server)

HIGH:

1. The current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to an integer value greater than 5, or is eligible to be the deadlock victim if another session has also set deadlock priority to HIGH or to an integer value equal to 5.

numeric-priority:

It is an integer value range (-10 to 10) to provide 21 levels of deadlock priority. It specifies that the current session will be the deadlock victim if other sessions in the deadlock chain are running at a higher deadlock priority value, but will not be the deadlock victim if the other sessions are running at a deadlock priority value lower than the value of the current session.

It also specifies that the current session is eligible to be the deadlock victim if another session is running with a deadlock priority value that is the same as the current session.

For more details and passing this priority as a variable values, you may refer to http://msdn.microsoft.com/en-us/library/ms186736.aspx