Locks & Concurrency

SQL Server is rarely used by one person at a time. In production, hundreds of sessions read and write simultaneously — a customer places an order while a warehouse system updates stock, a report queries totals while an API inserts new rows, a batch job updates prices while users browse the product catalogue. SQL Server uses a locking system to ensure that concurrent sessions do not corrupt each other's data or read inconsistent results. Understanding how locks work, what causes one session to wait for another, when deadlocks occur, and how isolation levels control the trade-off between consistency and concurrency is essential knowledge for anyone writing T-SQL against a live system. Every performance problem traced back to contention, every application timeout, and every deadlock error starts with a locking concept covered in this lesson.

How SQL Server Locks Work

SQL Server automatically acquires locks before reading or modifying data and releases them according to the current isolation level. Locks protect data from being read in an inconsistent state or modified by two sessions simultaneously. There are two fundamental lock modes that interact with each other. A shared lock (S) is acquired for reads — multiple sessions can hold shared locks on the same resource simultaneously, allowing concurrent reads. An exclusive lock (X) is acquired for writes — only one session can hold an exclusive lock on a resource, and no other session can hold any lock (shared or exclusive) on that same resource at the same time. This is the mechanism that prevents two sessions from updating the same row simultaneously and prevents a read from seeing a half-written row.

-- Inspect current locks held in the DataplexaStore database
-- Run this in a second session while another transaction is open to see live locks
USE DataplexaStore;

SELECT
    r.session_id,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time                         AS wait_ms,
    r.command,
    SUBSTRING(t.text, 1, 80)            AS query_text
FROM       sys.dm_exec_requests         r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE      r.database_id = DB_ID()
  AND      r.session_id  <> @@SPID;    -- exclude current session

-- Lock detail — what is held and on what resource
SELECT
    l.request_session_id                AS session_id,
    l.resource_type,
    l.resource_description,
    l.request_mode                      AS lock_mode,
    l.request_status,
    OBJECT_NAME(p.object_id)            AS table_name
FROM       sys.dm_tran_locks            l
LEFT JOIN  sys.partitions               p
    ON     l.resource_associated_entity_id = p.hobt_id
WHERE      l.resource_database_id = DB_ID()
ORDER BY   l.request_session_id, l.resource_type;

• The lock hierarchy flows from DATABASE → OBJECT → PAGE → ROW — SQL Server acquires intent locks (IX, IS) at the higher levels to signal to other sessions what is happening at a lower level, avoiding the need to scan all row locks to check compatibility
• An X (exclusive) lock on the ROW means no other session can read (with default isolation) or write that row until the transaction releases it — the GRANT status confirms this session currently holds the lock
• sys.dm_tran_locks is a live view — it reflects lock state at the moment of the query; in a real concurrency investigation it is queried repeatedly to track whether locks are being acquired and released normally or building up

Blocking — When One Session Waits for Another

Blocking occurs when one session holds a lock that a second session needs and cannot acquire. The second session enters a wait state — its query does not error, it simply pauses until the first session releases its lock (by committing or rolling back). Short blocking (milliseconds) is normal and expected in any concurrent system. Long blocking (seconds or more) is a symptom of a problem — typically a transaction that holds locks for longer than necessary, a missing index that causes locks to be held over a large scan rather than a targeted seek, or a transaction left open accidentally. The blocked session's wait_type in sys.dm_exec_requests will show LCK_M_X, LCK_M_S, or a similar lock wait code.

-- Simulate blocking: open Session 1 first, then run Session 2 query
-- SESSION 1 — open a transaction and update a row without committing
BEGIN TRANSACTION;
    UPDATE Orders SET Status = 'processing' WHERE OrderID = 7;
    -- Do NOT commit yet — this holds an X lock on OrderID 7

-- SESSION 2 — this query will block waiting for Session 1's X lock to release
SELECT OrderID, Status, TotalAmount
FROM   Orders
WHERE  OrderID = 7;  -- waits here until Session 1 commits or rolls back

-- IDENTIFY BLOCKING — run in a third session or after observing the wait
SELECT
    blocked.session_id                  AS blocked_session,
    blocked.blocking_session_id         AS blocked_by,
    blocked.wait_type,
    blocked.wait_time                   AS wait_ms,
    blocked.command,
    SUBSTRING(bt.text, 1, 100)          AS blocking_query
FROM       sys.dm_exec_requests         blocked
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) bt
WHERE      blocked.blocking_session_id <> 0;   -- only show blocked sessions

-- SESSION 1 — release the lock (commit or rollback to unblock Session 2)
COMMIT TRANSACTION;

• wait_type = LCK_M_S means Session 56 is waiting to acquire a Shared lock — it cannot get it because Session 55 holds an Exclusive lock on the same row, and X and S locks are incompatible
• wait_ms = 4823 shows Session 56 has been waiting 4.8 seconds — in a production system a query taking this long due to blocking would trigger application timeouts and user-visible errors
• The fix for this pattern is always the same: keep transactions short — acquire locks, do the work, commit immediately; never open a transaction and then perform user interaction, call external services, or run slow queries before committing

Isolation Levels

Isolation levels control the trade-off between data consistency and concurrency. A higher isolation level gives more consistent reads but acquires more locks and holds them longer, reducing throughput. A lower isolation level releases locks sooner or skips them entirely, improving concurrency but allowing a read to see data that is not fully consistent. SQL Server has five isolation levels. READ UNCOMMITTED reads data without acquiring any shared locks, so it can see uncommitted changes from other transactions — these are called dirty reads. READ COMMITTED (the default) acquires shared locks for reads but releases them immediately after each row is read rather than holding them for the transaction's duration. REPEATABLE READ holds shared locks until the transaction commits, preventing another session from modifying rows already read. SERIALIZABLE adds range locks to prevent new rows from appearing that would match the current query's WHERE clause — called phantom reads. SNAPSHOT reads a consistent point-in-time copy of committed data using row versioning, with no shared locks at all.

-- Isolation level effects — set and observe

-- Check current isolation level
SELECT
    session_id,
    transaction_isolation_level,
    CASE transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'READ UNCOMMITTED'
        WHEN 2 THEN 'READ COMMITTED'
        WHEN 3 THEN 'REPEATABLE READ'
        WHEN 4 THEN 'SERIALIZABLE'
        WHEN 5 THEN 'SNAPSHOT'
    END                                 AS isolation_name
FROM   sys.dm_exec_sessions
WHERE  session_id = @@SPID;

-- READ UNCOMMITTED — reads dirty (uncommitted) data from other sessions
-- Useful for approximate reporting on tables with high write contention
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT OrderID, Status, TotalAmount
FROM   Orders
ORDER BY OrderID;
-- May return rows being modified by another uncommitted transaction

-- Return to default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- SNAPSHOT isolation — requires enabling on the database first
-- ALTER DATABASE DataplexaStore SET ALLOW_SNAPSHOT_ISOLATION ON;

-- SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- Reads committed data as of the start of the transaction
-- No shared locks acquired — no blocking from writers
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- reset

• READ COMMITTED (level 2) is the SQL Server default — it prevents dirty reads by requiring shared locks for reads, but releases those locks immediately after each row is read rather than holding them for the transaction duration
• READ UNCOMMITTED trades consistency for speed — a session using it will not be blocked by writers and will not block writers, making it useful for approximate reporting dashboards where a momentarily inconsistent total is acceptable but blocking is not
• SNAPSHOT isolation is the cleanest concurrency solution for read-heavy workloads — readers never block writers and writers never block readers because reads are served from row version history rather than the live data page; the cost is tempdb space used to store the version store

Deadlocks

A deadlock occurs when two sessions each hold a lock the other needs and neither can proceed — Session A holds a lock on resource 1 and wants resource 2, while Session B holds a lock on resource 2 and wants resource 1. Both wait indefinitely for the other to release. SQL Server's deadlock monitor detects this cycle within seconds and resolves it by selecting one session as the deadlock victim — rolling back its transaction and raising error 1205 in that session. The other session is unblocked and continues. The deadlock victim is chosen based on a configurable priority and the cost of rolling back the transaction. The correct response to error 1205 in application code is to catch it and retry the transaction.

-- Deadlock scenario — two sessions create a circular lock dependency
-- Run each session's steps in the order shown to reproduce the deadlock

-- SESSION 1:
BEGIN TRANSACTION;
    UPDATE Products SET Price = ROUND(Price * 1.01, 2) WHERE ProductID = 1;
    -- Session 1 now holds X lock on Products row 1
    -- Next: Session 2 runs its first UPDATE, then Session 1 tries to lock Orders

-- SESSION 2 (while Session 1 is still open):
BEGIN TRANSACTION;
    UPDATE Orders  SET Status = 'processing' WHERE OrderID = 7;
    -- Session 2 now holds X lock on Orders row 7
    -- Next: Session 2 tries to lock Products row 1 — BLOCKED by Session 1

-- SESSION 1 (continues):
    UPDATE Orders  SET Status = 'shipped'    WHERE OrderID = 7;
    -- Session 1 tries to lock Orders row 7 — BLOCKED by Session 2
    -- DEADLOCK: Session 1 waits for Session 2, Session 2 waits for Session 1

-- SQL Server deadlock monitor detects the cycle and chooses a victim
-- One session receives:
-- Msg 1205: Transaction was deadlocked on lock resources with another process
--           and has been chosen as the deadlock victim. Rerun the transaction.

-- Prevention strategies — always access tables in the same order across sessions
-- SESSION 1 and SESSION 2 both update Products first, then Orders
-- This eliminates the circular dependency

-- View deadlock history using Extended Events (system_health session)
SELECT
    xdr.value('@timestamp', 'datetime2')    AS deadlock_time,
    xdr.query('.')                          AS deadlock_graph
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM   sys.dm_xe_session_targets   t
    JOIN   sys.dm_xe_sessions          s ON t.event_session_address = s.address
    WHERE  s.name = 'system_health'
      AND  t.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]')
    AS XEventData(xdr)
ORDER BY deadlock_time DESC;

• The deadlock was caused by inconsistent table access order — Session 1 locked Products then tried to lock Orders; Session 2 locked Orders then tried to lock Products, creating the circular dependency; consistent ordering eliminates the cycle
• Error 1205 is not a fatal application error — it is an expected concurrency event in busy systems; application code should catch it specifically and retry the transaction, typically with a short delay and a retry limit
• The system_health Extended Events session captures deadlock graphs automatically with no configuration — the XML graph identifies the exact resources and queries involved, making diagnosis straightforward without needing to reproduce the deadlock

READ COMMITTED SNAPSHOT ISOLATION — RCSI

Read Committed Snapshot Isolation (RCSI) is a database-level option that changes the behaviour of the default READ COMMITTED isolation level. With RCSI enabled, readers no longer acquire shared locks — instead they read the last committed version of each row from the version store in tempdb. This means readers never block writers and writers never block readers, eliminating the most common form of concurrency contention in OLTP systems without requiring any application code changes. RCSI is enabled at the database level and is the recommended configuration for most production SQL Server databases. The cost is tempdb space for the version store and a small overhead on each write to generate row versions.

-- Enable RCSI on DataplexaStore
-- Must be done with no active connections to the database (single-user or brief window)
ALTER DATABASE DataplexaStore
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK AFTER 5 SECONDS;   -- allow 5s for existing connections to finish

-- Verify RCSI is active
SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM   sys.databases
WHERE  name = 'DataplexaStore';

-- With RCSI on, this SELECT no longer acquires shared locks
-- It reads committed row versions from the version store
-- Session 1 can UPDATE Orders without blocking this SELECT on the same rows
SELECT OrderID, Status, TotalAmount
FROM   Orders
ORDER BY OrderID;

-- Monitor version store usage in tempdb
SELECT
    reserved_page_count         AS version_store_pages,
    reserved_page_count * 8     AS version_store_kb
FROM   sys.dm_db_file_space_usage
WHERE  database_id = 2;         -- tempdb is always database_id 2

• is_read_committed_snapshot_on = 1 confirms RCSI is active — all sessions using READ COMMITTED (the default) now automatically read from the version store without acquiring shared locks
• Version store usage of 24 KB is trivial on a small dataset — in production with many concurrent long-running transactions generating many row versions the version store can grow to gigabytes; monitoring its size is part of ongoing tempdb management
• RCSI is transparent to applications — no connection string changes, no isolation level commands, no stored procedure changes are needed; the improved concurrency is automatic for all READ COMMITTED queries

Summary Table

Practice Questions

Quiz

Next up - Query Optimisation - Learn how SQL Server's query optimiser chooses execution plans, how to read them, and the techniques that turn slow queries into fast ones.