SQL Server Locks, Blocking, and Deadlocks Made Easy for Beginners

 

Understand how SQL Server protects your data and why sessions sometimes wait for each other

A practical beginner-friendly guide to understanding concurrency, troubleshooting waits, and reducing production risk.

=================================================================== 

Introduction

Have you ever executed a query and noticed that it keeps running without completing? Or perhaps an application suddenly becomes slow even though server CPU usage looks normal.

In many cases, the cause is related to three important SQL Server concepts:

·       Locks

·       Blocking

·       Deadlocks

Key idea  These mechanisms are not automatically problems. SQL Server uses locking to protect data and maintain consistency when multiple sessions access the same resources.

 

1. What Is a Lock?

A lock is like a "Do Not Disturb" sign placed on data while it is being used. If one user updates a bank account balance, SQL Server can temporarily lock that data so another user cannot modify it at the same time.

Example

Session 1 starts a transaction and updates one employee:

BEGIN TRAN UPDATE Employees SET Salary = Salary + 1000 WHERE EmployeeID = 1;

 

The row remains locked until the transaction is committed or rolled back. If Session 2 tries to update the same row, it must wait:

UPDATE Employees SET Salary = Salary + 500 WHERE EmployeeID = 1;

 

2. Common Lock Types

Shared Lock (S)

Used while reading data. Multiple sessions can normally hold compatible shared locks at the same time.

SELECT * FROM Employees;

 

Exclusive Lock (X)

Used while modifying data. An exclusive lock prevents incompatible access to the locked resource.

UPDATE Employees SET Salary = 50000 WHERE EmployeeID = 1;

 

Update Lock (U)

Used when SQL Server reads a resource with the intention of possibly changing it. Update locks help reduce certain conversion deadlocks.

3. What Is Blocking?

Blocking occurs when one session waits for another session to release an incompatible lock. Think of one person using an ATM while the next person waits for the transaction to finish.

Blocking Example

Session 1:

BEGIN TRAN UPDATE Customers SET City = 'London' WHERE CustomerID = 10; -- Leave the transaction open for demonstration

 

Session 2:

SELECT * FROM Customers WHERE CustomerID = 10;

 

Result  Depending on the isolation level and database configuration, Session 2 may wait for Session 1. That wait is blocking.

 

How to Detect Blocking

EXEC sp_who2;

 

In the output, the BlkBy column can identify the blocking session. For a more focused view:

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

 

4. What Is a Deadlock?

A deadlock occurs when two or more sessions form a cycle in which each session is waiting for a resource held by another. SQL Server detects the cycle, selects one transaction as the deadlock victim, rolls it back, and allows the other transaction to continue.

Simplified Deadlock Scenario

Session A                         Session B ---------                         --------- Locks Customer row                Locks Order row Needs Order row                   Needs Customer row         \                         /          +------ deadlock -------+

 

A typical application receives error 1205, stating that the transaction was deadlocked and chosen as the deadlock victim. The application should handle the error appropriately, often by retrying the transaction after a short delay.

5. How to Capture Deadlocks

SQL Server Extended Events can capture a deadlock graph that shows the sessions, resources, statements, and victim involved. The built-in system_health session is commonly used as a starting point.

SELECT * FROM sys.dm_xe_sessions WHERE name = 'system_health';

 

Important  The query above only confirms whether the session exists. To investigate a specific deadlock, review the captured xml_deadlock_report event and its graphical deadlock report.

 

6. Best Practices to Reduce Blocking

·       Keep transactions short and commit or roll back promptly.

·       Avoid unnecessary explicit transactions.

·       Create appropriate indexes so queries touch fewer rows and pages.

·       Avoid waiting for user input while a transaction remains open.

·       Schedule large maintenance or data-change operations carefully.

·       Evaluate row-versioning options such as Read Committed Snapshot Isolation only after application testing and capacity review.

7. Best Practices to Reduce Deadlocks

·       Access tables and resources in a consistent order across code paths.

·       Keep each transaction focused on the smallest practical unit of work.

·       Use selective predicates and effective indexes.

·       Avoid holding locks while performing unrelated processing.

·       Capture and analyze the deadlock graph instead of guessing.

·       Implement controlled retry logic for error 1205 where appropriate.

Consistent Access Order

Preferred pattern:

Customers  ->  Orders  ->  Payments

 

Riskier pattern:

Session A: Customers -> Orders Session B: Orders -> Customers

 

Quick Summary

Concept	Meaning
Lock	Protects a resource while SQL Server processes an operation.
Blocking	One session waits for another session to release an incompatible lock.
Deadlock	Sessions form a circular dependency and cannot proceed.
Deadlock victim	The transaction SQL Server terminates and rolls back to break the cycle.
Primary response	Use short transactions, good indexing, consistent access order, monitoring, and tested retry logic.

Conclusion

Locks are essential for data consistency. Blocking is a normal consequence of incompatible locking, although prolonged blocking can affect performance. Deadlocks are circular waiting conditions that SQL Server resolves by terminating one transaction.

Understanding these concepts helps DBAs, developers, data engineers, and architects diagnose application slowdowns and design safer transaction patterns.