SET DEADLOCK_PRIORITY in SQL Server

By Santhosh N

This explains how to set DEADLOCK_PRIORITY in SQL Server and its benefits in doing so.

DEADLOCK_PRIORITY is extremely useful when working with multiple SQL Server sessions in multi-user sessions. Deadlocks are the ones which arise when two sessions are waiting to access the resources which are already locked by other. If we do not set any priority for our session, SQL server resolves the deadlock by choosing one among those. In doing so, it rollback the session transaction and returns the error message 1205 to the client.


To set the deadlock priority for the session you can use this command


SET DEADLOCK_PRIORITY str


Here str is one among LOW, NORMAL, HIGH, -10, -9, .. 9, 10
Which sets the priority based on the value supplied to the str

Ex: SET DEADLOCK_PRIORITY HIGH
This will always sets high priority and in case of deadlock this takes precedence.

Related FAQs

This explains how to use TEMP tables in the SELECT query for better query performance.
This explains the advanced query tuning concepts of the SQL Server in terms of using joins
This explains the QUERY_GOVERNOR_COST_LIMIT option for controlling long running queries in the SQL Server.
This explains where to have a loop if at all required to have one to get the desired result from the database.
This explains how to approach and check for bottlenecks in the SQL queries.
This explains the various precautions needs to be taken care while indexing and choosing what columns needs to be indexed for better performance.
SET DEADLOCK_PRIORITY in SQL Server  (2023 Views)