Locking is an unavoidable activities in concurrency system. But, we can control and manage the resource effectively. But, in this blog post I am going to introduce a SET statement called DEADLOCK_PRIORITY. This is one of the locking set statement in SQL Server. It tells how to react when dead lock occurs in the current session. As the name priority suggest that the importance or the order in which the deadlock should be treated with other processes.
DEADLOCK_PRIORITY Syntax (SQL Server 2012)
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
Argument description
LOW, NORMAL and HIGH are the constant value pre-defined in SQL Server. The numeric value equivalent to the names are -5, 0 and 5 respectively. We can also use this strings in a variable to assign to the SET statement. And, We can also give any whole number value between -10 to 10 instead variable or constant. Setting low value means that the priority for the deadlock for that session is low and make the current session as deadlock victim.
Normal is the default value for DEADLOCK_PRIORITY.
SET statement examples
SET DEADLOCK_PRIORITY HIGH
or
Declare @dlVar as varchar(10) = ‘LOW’
SET DEADLOCK_PRIORITY @dlVar
or
SET DEADLOCK_PRIORITY -1
Deadlock victim
To resolves the deadlock by choosing one of the sessions as a deadlock victim. The current transaction of the deadlock victim is rolled back and deadlock error message 1205 is returned to the client. It helps the other session to continue its work without interruption. Priority value plays the vital role in choosing deadlock victim session.
How does this SET statement react to the deadlock in concurrency system?
When Session A’s DEADLOCK_PRIORITY is set with a value (-10 to 10) and If Deadlock occurred and the other session in the deadlock chain is having the DEADLOCK_PRIORITY value greater than Session A’s priority value then the Session A will be the deadlock victim.
if both sessions having same priority value than the SQL Server will choose the deadlock victim by estimating the cost to rollback the current transaction and choose the less cost session as deadlock victim.
I have already wrote a blog post about lock timeout set statement. Please read Lock_Timeout in SQL Server
Like this:
Like Loading...