SQL Server isolation levels

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Run a read under READ COMMITTED and distinguish it from other isolation choices.

Run the example

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT CustomerID, FirstName
FROM dbo.Customers WHERE CustomerID BETWEEN 1 AND 3
ORDER BY CustomerID;

Read it step by step

1. READ COMMITTED prevents dirty reads of uncommitted changes.
2. The SELECT reads three committed customer rows in key order.
3. READ COMMITTED may use locks or row versions depending on database settings; this single-session result does not demonstrate concurrent visibility.

Check the result

The dataset is not modified by setting a session isolation level.

Compare the behavior

The five SQL Server levels answer different concurrency questions. READ UNCOMMITTED permits dirty reads. READ COMMITTED prevents dirty reads but a later statement may see a changed row or a new matching row. REPEATABLE READ protects rows already read until the transaction ends, but new matching rows can appear as phantoms. SERIALIZABLE also protects matching key ranges. SNAPSHOT reads a transaction-level version and needs ALLOW_SNAPSHOT_ISOLATION enabled. READ_COMMITTED_SNAPSHOT is a separate database option that changes how READ COMMITTED reads are implemented.

Explore it further

Two-session check on a disposable instance: first confirm whether READ_COMMITTED_SNAPSHOT is ON or OFF for DDI_SQL_Training. In session A, run BEGIN TRANSACTION; UPDATE dbo.Products SET ListPrice = ListPrice + 1 WHERE ProductID = 1; and leave the transaction open briefly. In session B, set READ COMMITTED and read ProductID 1. With the option OFF, the read normally waits for A; with it ON, the read can see the previously committed price through row versioning. In session A, run ROLLBACK TRANSACTION immediately, then confirm the original price in both sessions. Never leave A open after the check.

Practice

Read Customers 4–5 under SERIALIZABLE, then restore READ COMMITTED for the session.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT CustomerID, FirstName
FROM dbo.Customers WHERE CustomerID BETWEEN 4 AND 5
ORDER BY CustomerID;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;