Locking, Blocking, and Concurrency in SQL Server

Big Idea: Concurrency tuning is a balance between correctness and speed.
SQL Server uses locks to protect data correctness. Your job is to reduce unnecessary blocking without allowing bad data.
The best DBAs do not “remove locks.” They design transactions, indexes, queries, and isolation levels to work well under load.

Recommended Class Duration

120–180 minutes (concepts + demos + lab)

Prerequisites

• Basic SQL querying (SELECT/INSERT/UPDATE/DELETE)
• Basic performance monitoring knowledge (DMVs, waits)
• Optional: familiarity with transactions (BEGIN/COMMIT/ROLLBACK)

Locking

Locks are internal mechanisms that protect data consistency.
SQL Server uses locks so multiple users can work at the same time without corrupting data.

Blocking

Blocking happens when one session holds a lock and another session needs a conflicting lock,
so the second session must wait. Blocking is normal sometimes, but excessive blocking causes timeouts and “system slowness.”

Deadlock

A deadlock happens when two (or more) sessions block each other in a cycle.
SQL Server detects this and kills one session (the “deadlock victim”) to break the cycle.
Deadlocks are not “slow.” They are failures.

Key teaching point: Blocking is waiting for a lock. Deadlock is a cycle that SQL Server must break by killing a session.

Common lock modes

• S (Shared): used for reading data.
• X (Exclusive): used for modifying data.
• U (Update): a transitional lock often used when SQL Server expects to update rows.
• Intent locks (IS/IX): signals that a session intends to lock something at a lower level.

Key teaching point: S and X locks conflict. That conflict is the common root of blocking.

What makes transactions “too long”?

• Open transactions waiting on user input or application timeouts.
• Large batch updates running as one massive transaction.
• ETL jobs that modify many rows and hold locks for minutes.
• “Chatty” applications that begin a transaction too early and commit too late.

Key teaching point: The fastest concurrency fix is often not “add hardware.”
It is “shorten transactions” and “reduce the rows touched.”

Key isolation levels (practical overview)

• READ COMMITTED (default): readers can block writers and writers can block readers (common blocking).
• READ COMMITTED SNAPSHOT (RCSI): readers use row versions (TempDB) and generally do not block writers.
• SNAPSHOT: transaction-level versioning; can reduce read/write blocking but increases TempDB usage.
• READ UNCOMMITTED: can read uncommitted data (“dirty reads”)—use carefully for specific reporting scenarios only.
• SERIALIZABLE: strongest isolation, most blocking risk; used only when necessary.

Key teaching point: Versioning isolation (RCSI/SNAPSHOT) can reduce blocking,
but it shifts load to TempDB and requires monitoring and planning.

Script 1 — Blocking overview (who is blocked, who is blocking)

/* Blocking overview */
SELECT
  r.session_id,
  r.blocking_session_id,
  r.status,
  r.wait_type,
  r.wait_time,
  r.wait_resource,
  r.total_elapsed_time,
  DB_NAME(r.database_id) AS database_name,
  SUBSTRING(t.text, (r.statement_start_offset/2)+1,
    CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), t.text))
         ELSE (r.statement_end_offset - r.statement_start_offset)/2 + 1
    END) AS statement_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

How to interpret: The blocking_session_id is the blocker. If many sessions are blocked by the same blocker,
that blocker is your primary incident target.

Script 2 — Inspect the blocker (what is the blocker running?)

/* Replace @blocker_session_id with the session_id you found */
DECLARE @blocker_session_id int = 0; -- TODO: set this

SELECT
  s.session_id,
  s.login_name,
  s.host_name,
  s.program_name,
  s.status,
  s.open_transaction_count,
  r.command,
  r.cpu_time,
  r.total_elapsed_time,
  r.logical_reads,
  r.reads,
  r.writes,
  r.wait_type,
  r.wait_time,
  r.wait_resource,
  DB_NAME(r.database_id) AS database_name,
  LEFT(t.text, 4000) AS running_batch_text
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r
  ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = @blocker_session_id;

How to interpret: If open_transaction_count is high or the transaction has been open for a long time,
the real fix may be application logic, batching, or transaction design—not “add an index.”

Script 3 — Lock wait signals (LCK waits)

/* Sessions waiting on locks (LCK waits) */
SELECT
  r.session_id,
  r.status,
  r.blocking_session_id,
  r.wait_type,
  r.wait_time,
  r.wait_resource,
  r.total_elapsed_time,
  DB_NAME(r.database_id) AS database_name,
  LEFT(t.text, 4000) AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type LIKE 'LCK%'
ORDER BY r.wait_time DESC;

How to interpret: Many sessions with LCK waits usually means a concurrency bottleneck.
The next step is always: find and analyze the blocker.

Common deadlock patterns

• Two sessions update tables in different order (A then B vs B then A).
• Different indexes cause different lock acquisition paths.
• Large transactions with multiple statements increase deadlock risk.
• Foreign key checks can introduce locks in unexpected tables.

Key teaching point: Deadlocks are fixed by changing design:
consistent access order, shorter transactions, better indexing, and reduced lock footprint.

Fix A: Shorten transactions

• Commit sooner. Do not hold transactions open while doing user interaction or network calls.
• Batch large modifications into smaller commits.
• Remove unnecessary locks by reducing work inside the transaction.

Fix B: Reduce lock footprint

• Use proper indexes to avoid scanning large ranges (scans can lock more pages/keys).
• Filter earlier and return fewer rows.
• Avoid hot spots (repeated updates to the same rows/pages).

Fix C: Use versioning isolation carefully (RCSI/SNAPSHOT)

• Can reduce read/write blocking dramatically for reporting-heavy systems.
• Requires TempDB capacity planning and monitoring version store growth.
• Must be tested for application correctness expectations.

Fix D: Deadlock prevention strategies

• Access tables in consistent order across procedures.
• Keep transactions short and predictable.
• Ensure proper indexes to reduce lock time and lock range.
• Review deadlock graphs and fix the specific cycle (not generic guesses).

Key teaching point: The best concurrency tuning is not hacks.
It is good design: minimal work per transaction, good indexes, and predictable access patterns.

Student Submission Template (Copy/Paste)

• Blocking detected? yes/no
• Root blocker session_id:
• Blocked sessions count:
• Blocker query / workload:
• Open transaction count:
• Root cause statement:
• Fix 1 (quick mitigation):
• Fix 2 (long-term design):
• Risks / validation plan:

Prompt:
“A reporting query blocks order entry every morning at 9:00 AM. What are your top 3 hypotheses?
What evidence would you collect first to confirm which one is true?”