Handling Deadlocks

"A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock [msdn]" 


When SQL Server detects a deadlock between two tasks, it will terminate one of the tasks. We don't know which task would be terminated by SQL Server. It will have its own computations. What if we don't want one of the tasks to be a victim of dead lock? (Victim is the task that will be terminated by SQL Server). The following steps helps to prioritize the deadlock. There is a keyword

SET DEADLOCK_PRIORITY. We can use this property at the start of transaction or a task. It takes in following values. LOW/NORMAL/HIGH/ -10 to 10

Syntax: SET DEADLOCK_PRIORITY HIGH

Consider there are two tasks/Connections T1 and T2 and these will be dead locked. Normally when you run the two connections, connection1 with Task T1 becomes a victim. But, you want T2 to be the victim. If you don't want task T1 to be a victim, Set the DEADLOCK_PRIORTY greater than the DEADLOCK_PRIORITY of Task T2 .

IN T1 at the top of the query

SET DEADLOCK_PRIORITY NORMAL

GO

IN T2 at the top of the query

SET DEADLOCK_PRIORITY HIGH

GO

Now open two connections and run the queries. T2 will be a victim.