Optimize Transaction Deadlock Priority in SQL

SET DEADLOCK_PRIORITY HIGH;
BEGIN TRANSACTION;
— Your critical transaction code here
UPDATE Orders SET Status = ‘Processed’ WHERE OrderID = 12345;
COMMIT TRANSACTION;


You can also use numeric values for more precise control:

SET DEADLOCK_PRIORITY 7;
BEGIN TRANSACTION;
    -- Transaction code
COMMIT TRANSACTION;

Or set a lower priority for less critical operations:

SET DEADLOCK_PRIORITY LOW;
BEGIN TRANSACTION;
    -- Background cleanup job
    DELETE FROM AuditLog WHERE LogDate &lt; DATEADD(YEAR, -2, GETDATE());
COMMIT TRANSACTION;

The priority you set remains in effect for the entire session until you change it or the session ends.

Real-World Scenarios: When to Use Deadlock Priority

Let’s look at some practical situations where controlling deadlock priority makes sense.

Scenario 1: Critical User Transaction vs. Background Report

You have an e-commerce application where customers are placing orders. At the same time, a nightly reporting job is running that reads from the same tables. If a deadlock occurs, you want the customer’s order to complete, not the background report.

For the customer order transaction:

SET DEADLOCK_PRIORITY HIGH;
BEGIN TRANSACTION;
    INSERT INTO Orders (CustomerID, OrderDate, TotalAmount) VALUES (@CustomerID, GETDATE(), @Total);
    UPDATE Inventory SET Quantity = Quantity - @OrderQty WHERE ProductID = @ProductID;
COMMIT TRANSACTION;

For the background reporting job:

SET DEADLOCK_PRIORITY LOW;
BEGIN TRANSACTION;
    SELECT ProductID, SUM(Quantity) AS TotalSold
    FROM Orders o
    INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
    WHERE OrderDate >= @StartDate
    GROUP BY ProductID;
COMMIT TRANSACTION;

Now if these two processes deadlock, the reporting job becomes the victim and can be retried later. The customer order goes through successfully.

Scenario 2: Payroll Processing vs. Data Maintenance

Your payroll system needs to process employee payments, which is time-sensitive and critical. Meanwhile, a maintenance routine is updating employee addresses from a batch file. The payroll processing should always take priority.

-- Payroll processing
SET DEADLOCK_PRIORITY 8;
BEGIN TRANSACTION;
    UPDATE Employees SET LastPaymentDate = GETDATE(), PaymentAmount = @Amount 
    WHERE EmployeeID = @EmpID;
    INSERT INTO PaymentHistory (EmployeeID, PaymentDate, Amount) 
    VALUES (@EmpID, GETDATE(), @Amount);
COMMIT TRANSACTION;

-- Address update routine
SET DEADLOCK_PRIORITY -3;
BEGIN TRANSACTION;
    UPDATE Employees SET Address = @NewAddress WHERE EmployeeID = @EmpID;
COMMIT TRANSACTION;

SET DEADLOCK_PRIORITY

Real-World Scenarios: When to Use Deadlock Priority

Leave a Reply Cancel reply