Home > SQL Server > SQL SERVER – Control SQL locks using locking statements – SET DEADLOCK_PRIORITY

SQL SERVER – Control SQL locks using locking statements – SET DEADLOCK_PRIORITY


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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: