Click here to Skip to main content
Click here to Skip to main content
Go to top

Setting deadlock priority in SQL Server

, 19 Sep 2013
Rate this:
Please Sign up or sign in to vote.
Explains how to run a script with higher priority to avoid it getting terminated in a deadlock situation.

Introduction

Sometimes critical jobs might terminated because they faced a deadlock situation and was choosen as the deadlock victim by SQL Server. The other process which was allowed to continue may be a stored procedure call from a web page, and a failure on that would have impacted a single user's session. But the failed job might impact all users of the application. In these scenarios it is ideal to run the critical jobs with a higher deadlock priority so that they are not chosen as deadlock victims.

DEADLOCK_PRIORITY

We can set this connection property to appropriate level and SQL Server will terminate one with lowest priority. This feature is available from SQL Server 2005 onwards.

By setting the deadlock priority to high we can prevent our script being terminated as deadlock victim as shown below.

SET DEADLOCK_PRIORITY HIGH

-- Your script here

SET DEADLOCK PRIORITY NORMAL

You can give a numeric priority from -10 to 10 as well incase you want to set different priorities for a set of scripts which are expected to execute simultaneously with a chance of deadlock. 

Please refer to http://technet.microsoft.com/en-us/library/ms186736.aspx for more details.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Saral S Stalin
Technical Lead
India India
No Biography provided

Comments and Discussions

 
QuestionRegarding tip PinmemberAbdul Quader Mamun19-Sep-13 23:58 
AnswerRe: Regarding tip PinprofessionalAmol_B23-Sep-13 20:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 19 Sep 2013
Article Copyright 2013 by Saral S Stalin
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid