Locking — Shared, Exclusive and Update Locks

SQL Server uses locks to coordinate concurrent access to data. Understanding lock types, compatibility, and escalation is essential for diagnosing blocking — the most common performance complaint in SQL Server production systems. Most blocking stems from long-running transactions holding exclusive locks while other sessions wait for shared locks on the same rows. RCSI (from the previous lesson) eliminates most read/write blocking, but write/write blocking (two sessions updating the same rows) requires different strategies.

Locks and Blocking Diagnosis #

-- ── Diagnose active blocking ───────────────────────────────────────────────
-- Who is blocked and who is blocking them:
SELECT
    r.session_id         AS BlockedSession,
    r.blocking_session_id AS BlockingSession,
    r.wait_type,
    r.wait_time / 1000.0 AS WaitSeconds,
    r.status,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
          ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS BlockedSQL
FROM   sys.dm_exec_requests r
CROSS  APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE  r.blocking_session_id > 0;

-- ── What is the blocking session running? ────────────────────────────────
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.open_transaction_count,
    SUBSTRING(t.text, 1, 200) AS LastSQL
FROM   sys.dm_exec_sessions  s
CROSS  APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) t
WHERE  s.session_id = 55;   -- replace with the blocking session_id

-- ── Lock details for a session ────────────────────────────────────────────
SELECT
    tl.resource_type,
    tl.resource_description,
    tl.request_mode,      -- S=shared, X=exclusive, U=update, IS/IX=intent
    tl.request_status,    -- GRANT=has the lock, WAIT=waiting
    tl.request_session_id
FROM   sys.dm_tran_locks tl
WHERE  tl.resource_database_id = DB_ID('BlogApp')
ORDER  BY tl.request_session_id, tl.resource_type;

-- ── Lock compatibility matrix ─────────────────────────────────────────────
-- S  (Shared):    Compatible with S, IS     Incompatible with X, U, IX
-- X  (Exclusive): Incompatible with ALL     (only one writer at a time)
-- U  (Update):    Compatible with S, IS     Incompatible with U, X, IX
-- IS (Intent Shared):  Compatible with S, IS, IX, U
-- IX (Intent Exclusive): Compatible with IS, IX

-- ── WITH (NOLOCK) — READ UNCOMMITTED at query level ───────────────────────
-- ❌ Dangerous: reads uncommitted data and may miss rows or read duplicates
SELECT COUNT(*) FROM dbo.Posts WITH (NOLOCK);   -- approximate, not safe for logic

-- ── UPDLOCK hint — take update lock during read to prevent deadlocks ──────
-- Prevents two sessions from both taking shared locks, then both trying to upgrade
-- to exclusive (classic deadlock pattern):
BEGIN TRANSACTION;
    SELECT ViewCount FROM dbo.Posts WITH (UPDLOCK, ROWLOCK) WHERE Id = @PostId;
    -- UPDLOCK held — no other session can take UPDLOCK or XLOCK on this row
    UPDATE dbo.Posts SET ViewCount = ViewCount + 1 WHERE Id = @PostId;
COMMIT TRANSACTION;

Common Mistakes #

Mistake 1 — Transaction open during external API calls (long lock hold time) #

❌ Wrong — BEGIN TRAN → call external payment API (200ms-5s) → commit; locks held during the entire external call.

✅ Correct — call external API first → open transaction → record result → commit immediately.

Mistake 2 — Read-then-update without UPDLOCK (deadlock under concurrency) #

❌ Wrong — two sessions both SELECT (S lock), both try UPDATE (X lock) — deadlock.

✅ Correct — SELECT ... WITH (UPDLOCK) serialises access; first session reads and updates; second waits then proceeds.