No.7: Mastering SQL Server: Locking, Blocking & Isolation Levels

DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.

Welcome to No.8 of our SQL Server series. We have covered Backup & Restore (No.7).

Have you ever experienced a situation where “CPU usage is low, but the database is extremely slow”? This is almost always caused by Blocking. In this post, we dive deep into the mechanism of Locks, visualize Deadlocks, and master Transaction Isolation Levels to balance data integrity with performance.

STEP 1. Overview & Setup

1.1 What is Locking? (The Traffic Light)

Databases are multi-user environments. If User A is updating a row, User B cannot read that same row until User A finishes. This protection mechanism is called a Lock.

Blocking: When User B has to wait for User A. (This feels like “performance is slow”).
Deadlock: When User A and User B are waiting for each other. (The server kills one process).

1.2 Setup: sampleDB for Concurrency Tests

To test locking, we need two separate query windows (Session 1 and Session 2).

USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'sampleDB')
    CREATE DATABASE sampleDB;
GO
USE sampleDB;
GO

-- Create a test table
CREATE TABLE dbo.Accounts (
    AccountID int PRIMARY KEY,
    Balance int
);
INSERT INTO dbo.Accounts VALUES (1, 1000), (2, 2000);
GO

STEP 2. Locking Basics

2.1 Lock Types: Shared (S) vs Exclusive (X)

At a minimum, you must understand these two main lock types.

Lock Type	Symbol	Used For	Behavior
Shared Lock	S	Reading data (SELECT)	Others CAN read. Others CANNOT update.
Exclusive Lock	X	Modifying data (INSERT, UPDATE, DELETE)	Others CANNOT read. Others CANNOT update.

2.2 Monitoring Blocking (DMVs)

When the system feels slow, don’t guess. Check the Dynamic Management Views (DMVs).

-- Find who is blocking who
SELECT 
    tl.request_session_id AS WaitingSession,
    wt.blocking_session_id AS BlockingSession,
    r.command,
    wt.wait_type,
    t.text AS QueryText
FROM sys.dm_tran_locks tl
JOIN sys.dm_os_waiting_tasks wt ON tl.lock_owner_address = wt.resource_address
JOIN sys.dm_exec_requests r ON tl.request_session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

2.3 The Blocked Process Report

For long-term monitoring, enable the “Blocked Process Report” in Extended Events or Profiler. It alerts you if a lock lasts longer than X seconds.

-- Configure threshold to 10 seconds
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold (s)', 10;
RECONFIGURE;

2.4 Lock Granularity & Escalation

SQL Server tries to be efficient by locking the smallest possible unit.

Row Lock (RID/Key): Locks only 1 row. High concurrency, high memory usage.
Page Lock: Locks an 8KB page (multiple rows). Balanced.
Table Lock: Locks the entire table.

Lock Escalation: If a query acquires too many row locks (e.g., > 5,000), SQL Server automatically upgrades it to a Table Lock to save memory. This can suddenly block all other users.

2.5 Understanding Deadlocks

A Deadlock occurs when two sessions block each other in a cycle.

Deadlock Cycle:
[Session A] holds Lock X on Row 1 ---> Waits for Row 2
                                          ^
                                          |
[Session B] holds Lock X on Row 2 ---> Waits for Row 1

SQL Server automatically detects this cycle, chooses a “victim” (usually the one that is easiest to rollback), and kills it with Error 1205.

STEP 3. Transaction Isolation Levels

The Isolation Level controls “How strictly do we lock data during a read?”

3.1 The ACID Trade-off

Stricter Isolation: High data consistency, but slow performance (Blocking).
Looser Isolation: Fast performance, but data might be incorrect (Dirty Reads).

Use SET TRANSACTION ISOLATION LEVEL ... to change behavior.

3.2 Read Uncommitted (Dirty Reads)

“I don’t care about accuracy, just give me the data fast.” Equivalent to NOLOCK hint. It reads data that is currently being updated but not yet committed.

Risk: If the update rolls back, you read data that never existed (“Dirty Read”).

3.3 Read Committed (The Default)

“Only show me committed data.” SQL Server’s default. You cannot read a row that is being modified (X Lock). You must wait.

Phenomenon (Non-Repeatable Read): If you read Row A twice within one transaction, the value might change if someone else updates it in between.

3.4 Repeatable Read & Update Locks

“If I read it once, ensure it stays the same until I finish.” Holds S Locks until the end of the transaction.

The Conversion Deadlock Problem: A common deadlock pattern happens here:

Session A reads Row 1 (holds S Lock).
Session B reads Row 1 (holds S Lock).
Session A tries to Update Row 1 (Needs X Lock, waits for B to release S).
Session B tries to Update Row 1 (Needs X Lock, waits for A to release S).
BOOM! Deadlock.

Solution: Use UPDLOCK hint when reading data you intend to update later. (SELECT ... WITH (UPDLOCK)).

3.5 Serializable (Phantom Reads)

“Freeze the entire range.” Prevents new rows from being inserted into the range you read. Used for strict financial calculations. Performance is lowest.

STEP 4. Modern Concurrency (Row Versioning)

In the traditional “Pessimistic” model (above), Readers block Writers, and Writers block Readers.

4.1 Why Table Scans Cause Blocking

If you run SELECT * FROM LargeTable (Table Scan) in default mode, it takes S locks on every row. No one can update the table until you finish. This is why Indexes (No.5) are vital for Concurrency too!

4.2 Read Committed Snapshot (RCSI)

“Readers don’t block Writers. Writers don’t block Readers.” This is the modern standard (Default in Azure SQL). Instead of waiting for a lock, Readers get the “last committed version” of the row from TempDB.

-- Enabling RCSI
ALTER DATABASE sampleDB 
SET READ_COMMITTED_SNAPSHOT ON 
WITH ROLLBACK IMMEDIATE;

4.3 Snapshot Isolation

Similar to RCSI but provides transaction-level consistency. You see the database exactly as it was when your transaction started, regardless of changes by others.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN ...

4.4 The Cost of Versioning (TempDB)

While RCSI solves blocking, it puts heavy load on TempDB because that’s where the row versions are stored. Ensure your TempDB is on fast storage.

This concludes No.7 Locking & Blocking. You now understand that “slowness” is often just “waiting”.

Continue learning: To build on these concurrency labs, browse Udemy and search for SQL Server performance tuning courses with exercises on blocking, deadlocks, and transaction isolation. For interactive practice with transactions and error handling in SQL Server, explore DataCamp.

Table of Contents