Narcis Miclaus

7 min read

SQL Server, from the ground up Lesson 19 / 40

Isolation levels demystified

READ COMMITTED, SNAPSHOT, SERIALIZABLE, and the four anomalies they prevent. Why SQL Server's default can bite you, and why RCSI is the setting most EU shops should flip on.

Last lesson we covered what transactions are. Today we cover the I in ACID: isolation. What happens when two transactions are happening at the same time and they touch some of the same data?

The short answer: it depends on which isolation level your database is running. And SQL Server’s default isolation level (READ COMMITTED) does not do what most new developers expect. There’s a better default (READ COMMITTED SNAPSHOT) that ships disabled. Flipping it on is one of the highest-leverage single decisions a DBA can make for a modern OLTP workload.

The four classic anomalies

Before we get to the levels, let’s look at what can go wrong in concurrent SQL.

Dirty read

Transaction A writes a row. Before A commits, Transaction B reads that row. A rolls back. B now has data that officially never existed.

Runehold scenario: warehouse starts decrementing stock. Ops dashboard reads stock in that moment. Decrement fails and rolls back. Dashboard reports the “new” stock level, which was never real.

Non-repeatable read

Transaction A reads a row. Transaction B updates that same row and commits. Transaction A re-reads the row, sees different data.

Runehold scenario: customer service agent opens a customer record, starts reading details. Another session processes the customer’s bank details update. Agent re-reads, sees different data mid-conversation.

Phantom read

Transaction A runs a query. Transaction B inserts a new row that matches A’s query. A re-runs the query, sees a new row (“phantom”) that wasn’t there before.

Runehold scenario: finance runs a report of “all orders placed today.” Somewhere in the report generation, a new order lands. Finance re-runs a sub-query, totals don’t match the first query.

Lost update

Transaction A reads X, computes X’, and is about to write X’. Transaction B reads X, computes X”, writes X”. A writes X’, overwriting B’s change. B’s update is lost.

Runehold scenario: two customer service agents open the same customer profile. Both edit. Both save. One’s changes vanish.

The five isolation levels

SQL Server offers five named isolation levels. Each prevents some or all of the anomalies, at the cost of concurrency or memory.

READ UNCOMMITTED (also NOLOCK)

Reads uncommitted data. Allows dirty reads. Mostly never use this in new code.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- or equivalently, per query:
SELECT * FROM Sales.Orders WITH (NOLOCK);

You’ll see WITH (NOLOCK) everywhere in old SQL Server code. People think it makes queries faster. It does — at the cost of potentially reading:

  • Data that’s about to be rolled back (wrong).
  • Rows that are moving due to page splits (counted twice, or missed).
  • Inconsistent data mid-transaction (two rows of a related set are in different states).

For monitoring queries where approximate data is OK, NOLOCK is acceptable. For anything else, it’s a ticking bug.

READ COMMITTED

SQL Server’s default. Prevents dirty reads. Allows non-repeatable reads and phantoms.

Under READ COMMITTED, readers block on writers and writers block on readers. A SELECT waits if another session is updating the row; an update waits if another session is reading.

This is the source of the classic “my dashboard is slow because finance is running end-of-month” performance issue. Reads are blocked by writes and vice versa.

READ COMMITTED SNAPSHOT ISOLATION (RCSI)

This is the setting you want for most OLTP workloads. It’s READ COMMITTED, but readers don’t block writers and writers don’t block readers — instead, readers get a consistent point-in-time snapshot of the data as of when their statement started.

Turned on at the database level:

ALTER DATABASE Runehold SET READ_COMMITTED_SNAPSHOT ON;

Once enabled, every READ COMMITTED query (which is the default) becomes a snapshot read. No behavior change in your code; massive improvement in concurrency. It’s the single best setting most EU e-commerce shops flip on and forget about.

Cost: SQL Server uses tempdb to store previous versions of rows for in-flight snapshots. tempdb grows. Plan for it (lesson 34).

SNAPSHOT ISOLATION

A step beyond RCSI: the whole transaction sees a consistent snapshot, not just each statement. Prevents dirty reads, non-repeatable reads, and phantoms. Allows concurrent updates that don’t conflict.

ALTER DATABASE Runehold SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;
    SELECT ...;
    SELECT ...;      -- both selects see the same snapshot as of BEGIN TRAN
COMMIT;

Useful for reports that must be internally consistent: “as of when I started, what was the total revenue and customer count?”

Gotcha: if two SNAPSHOT transactions try to modify the same row, the second one fails with an update conflict error. Your app must handle that (usually: retry).

REPEATABLE READ and SERIALIZABLE

Older lock-based isolation levels that prevent more anomalies at the cost of holding locks longer.

  • REPEATABLE READ: prevents dirty and non-repeatable reads. Allows phantoms.
  • SERIALIZABLE: prevents all four anomalies. Acquires range locks, heavily blocking.

Rarely used in new code. If you need stronger guarantees, SNAPSHOT is usually the better choice — gives similar safety with less blocking.

When to use what

Fast rules of thumb:

  • Default for OLTP apps: turn on RCSI. Leave queries at the default READ COMMITTED. Done.
  • Reports that must be internally consistent: SNAPSHOT ISOLATION.
  • Inventory updates, financial postings: explicit locking with SERIALIZABLE or app-level locks (more on this in lesson 20).
  • Ad-hoc diagnostic queries that don’t need to be perfectly accurate: NOLOCK is an acceptable shortcut.

Runehold’s prod database has RCSI on. The finance “month-end closing” procedure runs under SNAPSHOT so it sees a stable view. Hot inventory updates use optimistic concurrency (RowVersion column). Everything else uses the default.

The RowVersion pattern

Many update conflicts can be handled at the application level with optimistic concurrency via a ROWVERSION column (we mentioned it in lesson 5).

CREATE TABLE Sales.Customer (
    CustomerId INT PRIMARY KEY,
    Name       NVARCHAR(200),
    ...,
    Version    ROWVERSION        -- auto-updates on every change
);

The application reads the row, including Version. When updating, it includes the Version it saw:

UPDATE Sales.Customer
SET Name = @name, Email = @email
WHERE CustomerId = @id AND Version = @versionItSawAtRead;

IF @@ROWCOUNT = 0
    -- Someone else changed the row between read and update.
    -- Raise a conflict error; app retries with fresh data.
    THROW 50003, 'Optimistic concurrency conflict', 1;

No locks held, no waiting. Conflicts are detected at commit and handled explicitly. This is how most modern web apps handle “two users edited the same record.”

Deadlocks: a quick preview

If two transactions need the same locks in opposite order, they can deadlock. SQL Server detects deadlocks every 5 seconds and kills the cheaper of the two with error 1205.

Transaction 1:                    Transaction 2:
  BEGIN TRAN;                       BEGIN TRAN;
  UPDATE A;                         UPDATE B;
  ... wants B ...                   ... wants A ...
  [blocks, waiting for 2]           [blocks, waiting for 1]
  [KILLED by deadlock detector]     [COMMITS]

Full lesson on locks, blocking, and deadlocks is next (lesson 20). Short version: always acquire locks in a consistent order, keep transactions short, and build deadlock-retry logic in the application (retry the killed transaction 2-3 times before giving up).

Reading your current isolation level

SELECT CASE transaction_isolation_level
           WHEN 0 THEN 'Unspecified'
           WHEN 1 THEN 'ReadUncommitted'
           WHEN 2 THEN 'ReadCommitted'
           WHEN 3 THEN 'RepeatableRead'
           WHEN 4 THEN 'Serializable'
           WHEN 5 THEN 'Snapshot'
       END AS isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Useful when debugging. Every client (SSMS, app, ORM) can set a different default.

Run this on your own machine (needs two sessions)

-- Open two query windows; we'll call them Session A and Session B.

-- In both: USE Runehold;

-- ========== WITH READ COMMITTED (default, without RCSI) ==========

-- Session A:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'UPDATED IN A' WHERE CustomerId = 1;
-- Don't commit yet.

-- Session B:
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;
-- Blocks, because A is holding a lock.

-- Session A:
COMMIT;   -- now B unblocks and sees the new value

-- ========== WITH RCSI (recommended) ==========
-- Turn it on (outside any transaction; you need exclusive access briefly)
-- ALTER DATABASE Runehold SET READ_COMMITTED_SNAPSHOT ON;

-- Now rerun the same scenario:
-- Session A:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'UPDATED IN A' WHERE CustomerId = 1;

-- Session B:
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;
-- Returns the PRE-UPDATE value instantly. No blocking.

-- Session A:
ROLLBACK;
-- No harm done.

-- ========== SNAPSHOT isolation for stable reports ==========
-- ALTER DATABASE Runehold SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT COUNT(*) AS initial_count FROM Sales.Orders;
    -- Imagine someone inserts 1000 orders here.
    WAITFOR DELAY '00:00:02';
    SELECT COUNT(*) AS same_count FROM Sales.Orders;
COMMIT;
-- Both SELECTs return the same count: the snapshot at BEGIN TRAN.

RCSI is the single most impactful flip. Run the before/after demo; watch B block then not block. That moment is when most DBAs decide to enable it forever.

Next lesson: locks, blocking, and deadlocks — the daily triangle of any busy OLTP system. We’ll look at the DMVs to see what’s locked, how to find who’s blocking whom, and how to read a deadlock graph without crying.

Search