Introduction
When two processes are caught up in a deadlock, one will be chosen as the victim and the process will be terminated, while the other process will be successful. SQL Server will, by default, pick the process that has the lowest cost to rollback. This can present a potential issue if the terminated process is a business-critical process. The DEADLOCK_PRIORITY allows us to have more control over whether or not a process is chosen as the victim.
Before getting into how to set the DEADLOCK_PRIORITY, let's quickly go over what the options are. There are two ways to set the priority of a process. The first option is to use one of the keywords, LOW, NORMAL or HIGH. The second approach is to use a numeric value between -10 and 10. The lowest value will be chosen as a victim. For example, LOW will be the victim of the other process is HIGH and -1 will be the victim if the other process is greater than -1.
If you look at the chart below you will see the winners and the losers in the deadlock. These charts identify what process will be allowed to complete and not be the victim. As you can see, when one process is LOW and the other is MEDIUM, the LOW process will be the victim. If there is a tie, SQL Server will use the cost to rollback the transaction.
The chart below is somewhat incomplete. The allowable ranges for the priority as a numeric value is the range between -10 and 10. As you can see below, if one process is -9 and the other is -7, the -9 will be the victim. Rather than take the chart all the way out to 10, I think this gives you the idea of how to figure it out beyond the numbers that are in the chart.
So now that we know how SQL Server determines the victim, let's take a look at how we can determine what the priority was for the processes in a deadlock.
One way is to use the deadlock graph. The two highlighted items are the deadlock priority for each of the processes. As you can see, the priority 5 survived while the priority -5 was chosen as the victim.
You can also look at the XML to determine the priority of each of the processes. In the XML you will look for "taskpriority" in the Process-List node. As you can see below there are two of them, one for the process that succeeded and the victim. The "victimProcess id" identifies the process that was chosen as the victim.
To set the priority for a process, you would add the code below at the beginning of the batch. This setting is for the session.
SET DEADLOCK_PRIORITY 10
This is what the code would look like if using a numeric priority. Remember, the value must between -10 and 10, inclusive.
This is what it would look like if you use the LOW, NORMAL, HIGH option.
Thanks for stopping by my blog!!!