Deadlock priority statement in SQL Server

Deadlock is occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. So in this case, SQL Server intervenes and ends the deadlock by cancelling one of the transactions, so the other transaction can move forward.

Read more : Deadlock in transaction in SQL Server

https://bageshkumarbagi-msbi.blogspot.com/2016/06/deadlock-in-transaction-in-sql-server.html

SQL Server automatically detects deadlocks, chooses one of these sessions as a victim, and rolls back the transaction, allowing the other session to proceed. SQL Server determines the victim session based on its estimations and picks up the less costly transaction to roll back.

We can set the deadlock using SET DEADLOCK_PRIORITY. If a deadlock occurs and other sessions in the deadlock chain have lower priorities, the current session will not be chosen as a deadlock victim and will proceed. If the sessions have equal priorities, SQL Server chooses the victim based on its estimations.

DEADLOCK_PRIORITY can be

·       HIGH

·       NORMAL

·       LOW

Syntex

SET DEADLOCK_PRIORITY HIGH 

See the example

Create two tables for demo

--Two global temp tables with sample data CREATE TABLE ##TableA (     ID INT IDENTITY,     Val CHAR(1) ) GO  INSERT INTO ##TableA (Val) VALUES ('A'), ('B') GO  CREATE TABLE ##TableB(         ID INT IDENTITY,         Val CHAR(1) ) GO  INSERT INTO ##TableB (Val) VALUES ('C'), ('D') GO

 Running the below script in two

Window 1

-- code for query window 1 BEGIN TRANSACTION SELECT @@SPID AS ProcessID --1 UPDATE ##TableA SET Val = 'E' WHERE ID = 1 ------------------------------------ WAITFOR DELAY '00:00:10' --3 UPDATE ##TableB SET Val= N'G' WHERE ID = 1 ------------------------------------------------------------- COMMIT SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1 SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

 

 Window 2

-- code for query window 2 BEGIN TRANSACTION  SELECT @@SPID AS ProcessID  --2 UPDATE ##TableB SET Val = N'F' WHERE ID = 1 -------------------------------------- WAITFOR DELAY '00:00:10'  --4 UPDATE ##TableA SET Val = N'H' WHERE ID = 1  COMMIT  SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1 SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

 

A deadlock occurs as both transactions try to update the same resource in the opposite order. The first transaction is chosen as a deadlock victim and rolled back in this case. The second one succeeded. As we haven’t set the deadlock priority, SQL Server decides which transaction to roll back.

Setting the priority

Window 1

-- code for query window 1   SET DEADLOCK_PRIORITY high GO BEGIN TRANSACTION SELECT @@SPID AS ProcessID --1 UPDATE ##TableA SET Val = 'E' WHERE ID = 1 ------------------------------------ WAITFOR DELAY '00:00:10' --3 UPDATE ##TableB SET Val= N'G' WHERE ID = 1 ------------------------------------------------------------- COMMIT SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1 SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

 

Window 2

-- code for query window 2 SET DEADLOCK_PRIORITY low GO BEGIN TRANSACTION  SELECT @@SPID AS ProcessID  --2 UPDATE ##TableB SET Val = N'F' WHERE ID = 1 -------------------------------------- WAITFOR DELAY '00:00:10'  --4 UPDATE ##TableA SET Val = N'H' WHERE ID = 1  COMMIT  SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1 SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

This configuration will give the guarantee that the high session will not be chosen as a victim if involved in a deadlock with the Low session.

Where we can use this setting

·       SET DEADLOCK_PRIORITY tells SQL Server which session to kill first if a deadlock occurs.

·       If two sessions are deadlocked, the one with the lower priority will be chosen as the deadlock victim.

·       we have two types of operations running on your database:

o   Critical Transaction: Processes salary payments (must complete, never be killed if possible)

o   Non-Critical Transaction: Generates a reporting summary (can be retried later if killed)

·       We want SQL Server to always prefer killing the non-critical session if a deadlock happens.