Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 39 ยท Data Modification and Transactions

Isolation Levels, Locking and Deadlocks

Understand SQL Server locking, the five isolation levels, READ_COMMITTED_SNAPSHOT, snapshot isolation, blocking and deadlocks, with a simulated stand-in you can run.

When many people use a database at once, their transactions can interfere. Isolation is the "I" in ACID: how much one transaction can see of another's unfinished work. SQL Server offers a choice, and it lets you pick between correctness, concurrency and speed.

This lesson explains locks, the isolation levels, row versioning and the two things that cause most support tickets: blocking and deadlocks. The ideas also appear in the SQL isolation lesson. Concurrency needs two sessions, so the examples are read-only T-SQL, with a runnable simulation at the end.

What a lock is

SQL Server takes locks to protect data while a statement or transaction uses it. The common lock modes:

Mode Used for Compatible with
Shared (S) Reading rows Other S locks
Exclusive (X) Writing rows Nothing
Update (U) Finding rows that will be changed S, but not U or X
Intent (IS, IX) Table or page level flag that rows below are locked Other intent locks

Locks can be taken on a row, a page or the whole table. SQL Server may escalate thousands of row locks into one table lock to save memory, which suddenly blocks everyone else. That's one more reason to keep transactions short and to update in batches.

The isolation levels

You set the level per session: SET TRANSACTION ISOLATION LEVEL .... The anomalies they allow:

  • Dirty read: reading data another transaction hasn't committed (and may roll back).
  • Non-repeatable read: reading the same row twice and getting different values.
  • Phantom read: running the same query twice and getting extra rows.
Level Dirty Non-repeatable Phantom How
READ UNCOMMITTED yes yes yes No shared locks (same as NOLOCK)
READ COMMITTED (default) no yes yes Short shared locks, or row versions
REPEATABLE READ no no yes Shared locks held to the end
SERIALIZABLE no no no Key-range locks
SNAPSHOT no no no Row versions from a transaction-start snapshot

NOLOCK is often sprinkled on queries to "fix" blocking. It can return uncommitted data, miss rows or return rows twice during page splits. Treat it as a last resort and prefer row versioning.

Row versioning: RCSI and SNAPSHOT

With row versioning, readers do not take shared locks. SQL Server keeps older versions of changed rows in tempdb and gives readers the last committed version. Writers don't block readers and readers don't block writers.

SQL
-- Make READ COMMITTED use versions instead of shared locks (database-wide)
ALTER DATABASE Shop SET READ_COMMITTED_SNAPSHOT ON;

-- Allow SNAPSHOT isolation for sessions that ask for it
ALTER DATABASE Shop SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    SELECT Balance FROM dbo.Accounts WHERE AccountId = 1;   -- stable view
COMMIT;

READ_COMMITTED_SNAPSHOT (RCSI) is the setting most OLTP systems use (Azure SQL Database has it on by default). It changes the default behavior without code changes. SNAPSHOT gives you a consistent view for the whole transaction, but update conflicts can raise error 3960 and need retry logic.

Blocking

Blocking is normal: session B waits because session A holds an incompatible lock. It becomes a problem when A holds the lock too long. To find the culprit:

SQL
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

The head of the chain is a session with blocking_session_id = 0 that others wait on, often an open transaction left by an application. Set a limit so waits don't last forever: SET LOCK_TIMEOUT 5000; (milliseconds).

Deadlocks

A deadlock happens when two sessions each hold a lock the other needs. Neither can move, so SQL Server's monitor picks a victim, rolls it back and returns error 1205 to it.

SQL
-- Session 1                          -- Session 2
BEGIN TRAN;                           BEGIN TRAN;
UPDATE A SET x = 1 WHERE id = 1;      UPDATE B SET x = 1 WHERE id = 1;
UPDATE B SET x = 1 WHERE id = 1;      UPDATE A SET x = 1 WHERE id = 1;  -- each waits for the other
flowchart LR
  S1["Session 1 holds lock on A"] -- "waits for B" --> S2["Session 2 holds lock on B"]
  S2 -- "waits for A" --> S1

A cycle of waits. SQL Server kills one session so the other can finish.

How to prevent them:

  1. Touch objects in the same order in every procedure.
  2. Keep transactions short.
  3. Add the right indexes, so statements lock fewer rows.
  4. Use row versioning so readers don't join the cycle.
  5. Catch 1205 in the application and retry. A deadlock is not a bug you can eliminate, only reduce.

To investigate, read the xml_deadlock_report from the system_health Extended Events session.

Simulating the effect of isolation

You can't open two sessions in this editor, but a single script can simulate a "lost update", the problem you get when two transactions read a balance, compute, and write back without locks:

SQL The lost update, simulated

Press Run (or Ctrl + Enter) to see the result here.

The stock shows 6 instead of 3: A's sale vanished. The fix is to let the database do the arithmetic in one atomic statement, so no stale value is ever carried:

SQL One atomic statement fixes it

Press Run (or Ctrl + Enter) to see the result here.

In SQL Server you get the same safety with UPDATE ... SET Qty = Qty - 3, or with UPDLOCK hints when you must read first: SELECT Qty FROM dbo.Stock WITH (UPDLOCK, ROWLOCK) WHERE Item = N'widget'.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the default isolation level in SQL Server?

    READ COMMITTED prevents dirty reads. With the READ_COMMITTED_SNAPSHOT option it uses row versions instead of shared locks.
  2. 2. What does READ_COMMITTED_SNAPSHOT change?

    RCSI stores previous row versions so readers see the last committed value without shared locks.
  3. 3. Which error number does the deadlock victim receive?

    Error 1205 tells the session it was chosen as the deadlock victim; the application should retry.
  4. 4. Which habit prevents many deadlocks?

    A consistent lock order makes circular waits impossible, and short transactions reduce the window further.

Summary

  • Locks protect data; they can escalate from rows to a table.
  • Isolation levels trade anomalies for concurrency; READ COMMITTED is the default, SNAPSHOT and RCSI use row versions.
  • NOLOCK allows dirty reads and is no cure for blocking.
  • Deadlocks are cycles of waits; reduce them with consistent order, short transactions and indexes, and retry on error 1205.

Next: Variables and control flow in T-SQL