Isolation levels decide what a reading transaction may see of other transactions’ work and how long its shared locks last. They control concurrency side effects; they don’t validate data, which remains the job of constraints and application logic. Figure 9.1 summarises which effects each level prevents and whether it relies on locks or row versions.
The five isolation levels
SET TRANSACTION ISOLATION LEVEL applies to the connection until changed; set inside a stored procedure or trigger, it reverts to the caller’s level when the module returns. Writers always take exclusive locks on rows they modify and hold them to the end of the transaction, whatever the isolation level.
| Isolation level | Dirty reads | Non-repeatable reads | Phantoms | How reads are protected |
|---|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible | No shared locks; same as NOLOCK on every table |
| READ COMMITTED (READ_COMMITTED_SNAPSHOT OFF) | Prevented | Possible | Possible | Shared locks released as each row or page is read |
| READ COMMITTED with RCSI (READ_COMMITTED_SNAPSHOT ON) | Prevented | Possible | Possible | Row versions as of the start of each statement; no shared locks |
| REPEATABLE READ | Prevented | Prevented | Possible | Shared locks held to end of transaction |
| SNAPSHOT | Prevented | Prevented | Prevented | Row versions as of the start of the transaction |
| SERIALIZABLE | Prevented | Prevented | Prevented | Key-range locks held to end of transaction; same as HOLDLOCK |
Platform defaults for row versioning
| Platform | READ_COMMITTED_SNAPSHOT default | ALLOW_SNAPSHOT_ISOLATION default |
|---|---|---|
| SQL Server 2025 | OFF | OFF; must be turned on before SNAPSHOT can be used |
| Azure SQL Database (new databases) | ON | ON |
SQL database in Microsoft Fabric also has RCSI on by default, and READ COMMITTED is its default isolation level.
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;Setting READ_COMMITTED_SNAPSHOT needs the ALTER DATABASE connection to be the only one in the database. With RCSI on, the READCOMMITTEDLOCK table hint restores shared locking for one statement.
RCSI versus SNAPSHOT, and optimistic versus pessimistic
Both RCSI and SNAPSHOT isolation are optimistic: readers don’t block writers and writers don’t block readers, because readers use row versions. RCSI changes the behaviour of the existing READ COMMITTED level and gives statement-level consistency with no code change. SNAPSHOT must be requested explicitly with SET TRANSACTION ISOLATION LEVEL SNAPSHOT and gives one consistent view for the whole transaction.
SNAPSHOT has two rules that catch developers. A transaction that started under another isolation level can’t switch to SNAPSHOT (the transaction fails and rolls back), although a SNAPSHOT transaction may switch to another level. And if a SNAPSHOT transaction tries to update or delete a row that another transaction changed after the snapshot began, it fails with error 3960 (update conflict) and must be retried; an UPDLOCK hint on the initial read prevents the conflict at the cost of blocking. Pessimistic control (REPEATABLE READ, SERIALIZABLE, UPDLOCK and HOLDLOCK hints) prevents conflicts by blocking instead.
Row versions live in a version store: in the user database’s persistent version store when accelerated database recovery (ADR) is enabled, otherwise in tempdb. Long-running SNAPSHOT or RCSI transactions prevent version cleanup and make the store grow.
| Requirement | Suitable choice |
|---|---|
| Reports must not block, or be blocked by, OLTP writes, with no code change | RCSI |
| A multi-statement read must see one consistent point in time | SNAPSHOT |
| A row read early in a transaction must not change before the transaction ends | REPEATABLE READ, or UPDLOCK on the read |
| A range read twice must return the same rows, with no inserts into the range | SERIALIZABLE |
| Strict ordering between concurrent writers on RCSI | REPEATABLE READ or SERIALIZABLE, or locking hints |
Common trap: Raising the isolation level to stop bad values being saved - isolation levels address concurrency effects such as dirty reads and phantoms, not data validation; CHECK, FOREIGN KEY and UNIQUE constraints (or trigger logic) validate data.
Common trap: Switching a transaction to SNAPSHOT halfway through after it has already read data under READ COMMITTED - a transaction that started at another level can’t change to SNAPSHOT; the attempt fails and rolls the transaction back.