SQL Server Blocking and Deadlocks: Diagnosis and Prevention

Blocking vs. Deadlocks: Know the Difference

Blocking: Session A holds a lock, Session B is waiting. B will eventually get the lock when A commits or rolls back. Blocking itself isn't an error—it's expected. Excessive blocking is a performance problem.

Deadlock: Two sessions each hold a lock the other wants, and neither can proceed without the other's lock. SQL Server automatically picks a deadlock victim and rolls back their transaction. This IS an error—you'll see error 1205 in your logs.

Both cause application errors but have different solutions. Confusing them wastes time on the wrong fix.

Finding Blocking Sessions

Start with the blocked process report or dynamic management views:

-- Find blocking chains (run this when blocking occurs)
SELECT
    blocked.session_id AS blocked_session,
    blocked.status AS blocked_status,
    blocked.wait_time / 1000 AS wait_seconds,
    blocker.session_id AS blocking_session,
    blocker.login_name,
    blocker.program_name,
    blocker.cpu_time,
    blocker.memory_usage,
    blocker.total_elapsed_time / 1000 AS total_seconds,
    blockerReads.text AS blocker_sql,
    blockedReads.text AS blocked_sql
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocker
    ON blocked.blocking_session_id = blocker.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blockedReads
CROSS APPLY sys.dm_exec_sql_text(blocker.sql_handle) blockerReads
WHERE blocked.blocking_session_id > 0;

-- Also useful: who's holding the most locks?
SELECT session_id, request_id, dbid, object_id, resource_type, request_status
FROM sys.dm_tran_locks
WHERE request_session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0);

Root Causes of Blocking

Blocking is normal in a busy database. Excessive blocking comes from:

• Long-running transactions: Open transaction holds locks too long. Find and fix them.
• Unindexed foreign keys: Parent table locks child table rows during DELETE. Add indexes.
• Implicit transactions left open: application doesn't commit/rollback explicitly.
• Batch size issues: Large batch inserts hold locks longer than necessary.
• High isolation level: READ COMMITTED (default) is fine for most apps. SERIALIZABLE causes more blocking.

Fixing Excessive Blocking

Don't kill blocking sessions without understanding why they're blocking. Instead:

1. Identify the root transaction: which query opened the transaction, how long ago, what is it doing?
2. Shorten transaction duration: commit after each logical unit, not at the end of a procedure.
3. Reduce lock granularity: use lower isolation level, add NOLOCK hints (with caution) for reads, partition large tables.
4. Index foreign keys: CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId)
5. Batch large operations: insert 10,000 rows at a time with WITH (BATCHSIZE = 1000)

Monitoring for Blocking

Blocking won't show up in most monitoring tools until it's severe. Set up an alert:

-- Create an alert for blocking lasting more than 30 seconds
-- Schedule this as a SQL Agent job
SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
AND wait_time > 30000;  -- 30 seconds

-- Log to a table, alert if rows are returned