SQL Deadlock Chain Across Sessions – Fix It Fast

First, The 30-Second Fix: Kill the Blocking Session

You're staring at a query that won't finish. Maybe your app threw a 1205 error. Don't panic. First thing: find the session that's holding everything up. Open SQL Server Management Studio, run this:

SELECT session_id, blocking_session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

If you see a row where blocking_session_id isn't 0, you've found the culprit. Kill it with:

KILL {blocking_session_id};

Replace {blocking_session_id} with the actual ID. This works 80% of the time when it's a single stuck transaction. Had a client last month whose whole sales system froze because an overnight report left an open transaction. One KILL command, everything back up in 10 seconds.

If That Didn't Work: The 5-Minute Fix – Find and Break the Chain

Sometimes there's more than two sessions involved. You get a whole chain – A blocks B, B blocks C, C blocks A. That's the deadlock chain. Run this to see the full picture:

WITH BlockingChain AS (
    SELECT session_id, blocking_session_id, 0 AS depth
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
    UNION ALL
    SELECT r.session_id, r.blocking_session_id, bc.depth + 1
    FROM sys.dm_exec_requests r
    INNER JOIN BlockingChain bc ON r.blocking_session_id = bc.session_id
)
SELECT * FROM BlockingChain ORDER BY depth;

This recursive query shows you who's blocking who, and how deep the chain goes. The session at the top with no blocker (blocking_session_id = 0) is the root cause. Kill that session with KILL {session_id}. But here's a tip: don't just kill the middle of the chain. Kill the root – that clears the whole thing. I learned that the hard way after killing three sessions in a row and watching the chain rebuild itself.

Still Stuck? The 15-Minute Fix: Change Deadlock Priority or Use Trace Flags

If this keeps happening, you've got a systemic problem. The root cause is usually poor transaction design or conflicting index access patterns. Here's the real fix:

Option A: SET DEADLOCK_PRIORITY

You can tell SQL Server which session should die first. Run this in your problem session before the transaction:

SET DEADLOCK_PRIORITY LOW;

This tells SQL Server: "Kill me before killing the other guy." Do this for non-critical queries – reports, background jobs. Keep the critical ones at NORMAL or HIGH. Yes, you lose that session's work, but it doesn't bring down the whole system. I've got a client's inventory system that runs this on their hourly stock sync – the occasional deadlock kills the sync instead of the checkout process.

Option B: Enable Trace Flag 1222

This gives you detailed deadlock info in the SQL Server error log. Run:

DBCC TRACEON(1222, -1);

Then check the error log (EXEC xp_readerrorlog 0, 1, N'deadlock';). It shows you the exact queries involved, the locks held, and the lock order. You'll see patterns like: Session A holds lock on Table1, wants lock on Table2; Session B holds lock on Table2, wants lock on Table1. Classic deadlock.

How to interpret the trace output:

Look for the deadlock victim section – that's the session SQL Server chose to kill. Then look at the process-list to see the exact SQL statements. You'll often find two update statements that touch tables in opposite order. Example: Session 1 updates TableA then TableB; Session 2 updates TableB then TableA. Reverse one to match the other.

Option C: Fix the Transaction Design

This is the long-term solution. If you have control over the application code, ensure all transactions access tables in the same order. Like: always update Customers before Orders. No exceptions. This prevents deadlocks entirely. Also, keep transactions short. Move logging outside the transaction. Don't hold locks while waiting for user input – that's a deadlock waiting to happen.

Real talk: Trace flag 1222 is your friend. I've solved more deadlocks with this than any other tool. Turn it on for 10 minutes while the problem occurs, then turn it off. Don't leave it on in production – it adds overhead.

When to Call for Backup

If you're still seeing deadlocks after all this, it's probably an application design issue. You need a dev to review the transaction logic. But for 90% of the cases I see, killing the root session or setting DEADLOCK_PRIORITY LOW is all you need. Skip the fancy stuff until you've confirmed the pattern.