SQL’s Choice–Helping SQL Server Pick Deadlock Victims

In my last post I show how to debug deadlocks using SQL Profiler.  That’ll help you figure out what’s causing them.  But what if for whatever reason you can’t resolve all of them?  How do I make sure that a high priority transaction isn’t chosen as the victim? 

SQL provides the answer with the SET DEADLOCK_PRIORITY statement.  You can pass it a value between –10 and 10 or the predefined values, HIGH, NORMAL or LOW that map to –5, 0 and 5 respectively.  A session with a lower value will picked as a deadlock victim over a session with a higher value. 

Below are the table and stored procedure scripts from the previous article that will allow us to create a deadlock by executing ProcedureA in one SSMS window and then immediately executing ProcedureB in a second window.

--  Create Tables
CREATE TABLE TableA(
    TableAId INT IDENTITY(1,1) NOT NULL,
    SomeValue NVARCHAR(50) NOT NULL,
)
GO

CREATE TABLE TableB(
    TableBId INT IDENTITY(1,1) NOT NULL,
    SomeOtherValue NVARCHAR(50) NOT NULL,
)
GO

--  Create Stored Procedures
CREATE PROCEDURE ProcedureA
AS
BEGIN
	SET DEADLOCK_PRIORITY HIGH

	BEGIN TRANSACTION
	
	INSERT INTO TableA WITH (TABLOCKX) (
			SomeValue)
		VALUES (
			'SomeValue A')
			
	WAITFOR DELAY '00:00:10'
	
	INSERT INTO TableB WITH (TABLOCKX) (
			SomeOtherValue)
		VALUES (
			'SomeOther Value A')
			
	COMMIT TRANSACTION
END
GO

CREATE PROCEDURE ProcedureB
AS
BEGIN
	SET DEADLOCK_PRIORITY LOW

	BEGIN TRANSACTION
	
	INSERT INTO TableB WITH (TABLOCKX) (
			SomeOtherValue)
		VALUES (
			'SomeValue B')

	INSERT INTO TableA WITH (TABLOCKX) (
			SomeValue)
		VALUES (
			'SomeOther Value B')
			
	COMMIT TRANSACTION
END
GO

Note that the difference here is that the first statement in ProcedureA is SET DEADLOCK_PRIORITY HIGH and in ProcedureB is SET DEADLOCK_PRIORITY LOW.  This will ensure that ProcedureB will always be the victim of a deadlock between Procedures A & B. 

By placing the SET statement within the stored procedure we’ve set the deadlock priority for the stored procedure alone.  If it’s run outside the stored procedure it can be used to set the priority of an entire session.

If your interested in finding out what the current priority is you can use the following query:

SELECT 
		session_id,
		deadlock_priority 
	FROM sys.dm_exec_sessions  
	WHERE session_id = @@SPID

So now even if you can’t prevent all deadlocks, at least you can give your mission critical processes priority.

For more information on DEADLOCK_PRIORITY you can read on MSDN at:

SET DEADLOCK_PRIORITY (Transact-SQL)