Isolation levels and row versioning

Overview

Isolation levels control what one transaction can see when other transactions are reading or modifying the same data. In SQL Server, isolation levels affect read behavior through locking and row versioning. The main isolation levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE.

Row versioning is an alternative to many reader-writer blocking patterns. Instead of making readers wait for writers or writers wait for readers, SQL Server can give readers an older committed version of a row. The two common row-versioning options are read committed snapshot isolation, usually called RCSI, and explicit SNAPSHOT isolation.

This topic matters because isolation is a trade-off between correctness and concurrency. Weak isolation can allow dirty reads or inconsistent decisions. Strong isolation can reduce concurrency and increase blocking. Row versioning can reduce blocking for reads, but it adds version store overhead and has different update-conflict behavior.

For interviews, strong candidates can explain dirty reads, nonrepeatable reads, phantom reads, default READ COMMITTED, SERIALIZABLE range locks, RCSI statement-level snapshots, SNAPSHOT transaction-level snapshots, and why NOLOCK is not a safe performance strategy.

Core Concepts

Why Isolation Exists

Isolation protects transactions from unsafe interaction with concurrent work.

Example risk:

-- Session 1
BEGIN TRANSACTION;

UPDATE dbo.Accounts
SET Balance = Balance - 100.00
WHERE AccountId = 1;

-- Not committed yet.

If Session 2 reads that uncommitted balance and makes a decision, it may act on data that later rolls back.

Isolation determines whether Session 2 waits, reads an older committed version, or reads the uncommitted value.

Common Concurrency Phenomena

Dirty read:

• A transaction reads data modified by another transaction that has not committed.
• If the writer rolls back, the reader saw data that never truly existed.

Nonrepeatable read:

• A transaction reads the same row twice and sees different committed values because another transaction updated it between reads.

Phantom read:

• A transaction reruns a range query and sees new rows that another transaction inserted into the range.

Lost update:

• Two transactions read the same value and both write back changes, causing one change to overwrite the other.

Different isolation levels protect against different issues.

READ UNCOMMITTED

READ UNCOMMITTED allows dirty reads.

Example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT Balance
FROM dbo.Accounts
WHERE AccountId = 1;

This read can see uncommitted changes from another transaction.

NOLOCK has similar behavior for table reads:

SELECT *
FROM dbo.Orders WITH (NOLOCK);

Interview answer: NOLOCK is not "free speed." It allows dirty reads and other inconsistent results. It may be acceptable for rare diagnostic cases, but it is usually a bad default for application correctness.

READ COMMITTED

READ COMMITTED prevents dirty reads. It is the SQL Server default isolation level.

With traditional locking behavior, a statement running under READ COMMITTED cannot read rows modified but not committed by another transaction. Shared locks are used for reads and generally released as the statement progresses or completes.

Example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT Balance
FROM dbo.Accounts
WHERE AccountId = 1;

READ COMMITTED prevents dirty reads, but it can still allow nonrepeatable reads and phantom reads across multiple statements in the same transaction.

READ COMMITTED SNAPSHOT Isolation

Read committed snapshot isolation, or RCSI, changes READ COMMITTED behavior at the database level. When READ_COMMITTED_SNAPSHOT is on, READ COMMITTED readers use row versions instead of shared locks for read consistency.

Enable example:

ALTER DATABASE CurrentDatabase
SET READ_COMMITTED_SNAPSHOT ON;

Under RCSI:

• Each statement sees a transactionally consistent snapshot as of the start of that statement.
• Readers do not take shared locks to block writers.
• Writers do not block readers in the same way.
• The application can still use normal READ COMMITTED.

Important nuance: RCSI is statement-level versioning. Two SELECT statements in one transaction can see different committed snapshots if other transactions commit between those statements.

REPEATABLE READ

REPEATABLE READ prevents dirty reads and nonrepeatable reads for rows that were read.

Example:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

SELECT Balance
FROM dbo.Accounts
WHERE AccountId = 1;

-- Another transaction cannot modify that read row until this transaction completes.

SELECT Balance
FROM dbo.Accounts
WHERE AccountId = 1;

COMMIT TRANSACTION;

Shared locks on read rows are held until the transaction completes. This protects rows already read, but it does not prevent other transactions from inserting new rows that match a range predicate. Phantom rows can still appear.

SERIALIZABLE

SERIALIZABLE is the strictest locking isolation level.

Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT *
FROM dbo.Appointments
WHERE DoctorId = @DoctorId
  AND StartTime < @RequestedEnd
  AND EndTime > @RequestedStart;

-- If no conflict exists, insert appointment.

INSERT dbo.Appointments (DoctorId, StartTime, EndTime)
VALUES (@DoctorId, @RequestedStart, @RequestedEnd);

COMMIT TRANSACTION;

SERIALIZABLE can protect key ranges so another transaction cannot insert rows into the range that was checked. This helps prevent phantom reads and some race conditions.

Trade-off: SERIALIZABLE can significantly reduce concurrency and increase blocking because range locks are held until the transaction completes.

SNAPSHOT Isolation

SNAPSHOT isolation gives a transaction a consistent view of committed data as of the start of the transaction.

Enable example:

ALTER DATABASE CurrentDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;

Use example:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT Balance
FROM dbo.Accounts
WHERE AccountId = 1;

-- Later statements in this transaction see the same snapshot
-- for data committed before the transaction began.

COMMIT TRANSACTION;

Under SNAPSHOT, readers generally do not block writers and writers generally do not block readers. However, update conflicts can occur if a snapshot transaction tries to update data that another transaction changed after the snapshot transaction began.

RCSI Vs SNAPSHOT

RCSI and SNAPSHOT both use row versions, but their consistency scope differs.

RCSI:

• Enabled with READ_COMMITTED_SNAPSHOT.
• Used by normal READ COMMITTED statements.
• Snapshot is statement-level.
• Each statement can see a different committed point in time.

SNAPSHOT:

• Enabled with ALLOW_SNAPSHOT_ISOLATION.
• Requested with SET TRANSACTION ISOLATION LEVEL SNAPSHOT.
• Snapshot is transaction-level.
• Statements in the transaction see the same committed data as of transaction start.

Example difference:

-- Under RCSI, these two statements may see different committed versions.
BEGIN TRANSACTION;
SELECT Status FROM dbo.Orders WHERE OrderId = 1;
SELECT Status FROM dbo.Orders WHERE OrderId = 1;
COMMIT TRANSACTION;

Under explicit SNAPSHOT, both reads use the transaction-start snapshot.

Row Versioning

Row versioning stores previous committed row versions so readers can access a consistent older version while writers continue.

When a row is updated:

• The writer modifies the current row.
• SQL Server maintains a previous version for versioned readers.
• Versioned readers can read the older committed row instead of blocking.

Row versioning helps read-heavy workloads where blocking between readers and writers is a problem.

Costs include:

• Additional storage for versions.
• More tempdb or persistent version store pressure depending on platform and feature.
• Cleanup work.
• Long-running transactions can keep old versions alive longer.
• Different conflict behavior for updates under SNAPSHOT.

Locking Still Matters Under Row Versioning

Row versioning reduces read/write blocking, but it does not remove all locks.

Important points:

• Writers still take exclusive locks for data they modify.
• Isolation level mainly changes read behavior.
• Schema stability and schema modification locks can still matter.
• Updates must still protect data integrity.
• Long-running versioned transactions can create storage pressure.

Interview trap: "We enabled RCSI, so locking no longer matters" is wrong.

Dirty Reads And NOLOCK

NOLOCK is often used as a quick attempt to avoid blocking, but it changes correctness.

Possible problems:

• Dirty reads.
• Reading rows that later roll back.
• Missing rows.
• Duplicate rows.
• Inconsistent aggregates.
• Decisions based on uncommitted data.

Better options:

• Fix slow queries.
• Add appropriate indexes.
• Keep write transactions short.
• Use RCSI where appropriate.
• Use reporting replicas or snapshots for reporting workloads.

Write Conflicts Under SNAPSHOT

SNAPSHOT isolation can produce update conflicts.

Example flow:

• Transaction A starts under SNAPSHOT and reads row 10.
• Transaction B updates row 10 and commits.
• Transaction A tries to update row 10.
• SQL Server detects that the row changed after Transaction A's snapshot began.
• Transaction A fails and must be retried or handled.

Applications using SNAPSHOT for writes must handle update conflicts.

Choosing An Isolation Level

Use READ COMMITTED when:

• Default correctness is enough.
• Dirty reads are not acceptable.
• Some change between statements is acceptable.

Use RCSI when:

• Reader/writer blocking is a major issue.
• Statement-level committed consistency is acceptable.
• The workload can support version store overhead.

Use SNAPSHOT when:

• A transaction needs a consistent point-in-time view across multiple statements.
• You can handle update conflicts.

Use SERIALIZABLE when:

• You must protect ranges from inserts or phantoms.
• Correctness requires serial execution semantics for that transaction.
• You can tolerate lower concurrency.

Avoid READ UNCOMMITTED for business-critical application logic.

Common Mistakes

Common mistakes include:

• Using NOLOCK to hide blocking without accepting dirty-read risk.
• Assuming READ COMMITTED gives repeatable results across a transaction.
• Using RCSI and expecting transaction-level snapshots.
• Using SNAPSHOT without handling update conflicts.
• Using SERIALIZABLE broadly and causing heavy blocking.
• Forgetting that writers still take locks under row versioning.
• Leaving long transactions open and growing version store pressure.
• Assuming isolation level changes protect data modifications from exclusive locks.
• Changing isolation level without testing concurrency behavior.
• Solving all blocking with isolation changes instead of fixing transaction scope and indexes.

Best Practices

Best practices:

• Start with the correctness requirement, not the fastest-looking isolation level.
• Avoid dirty reads for business decisions.
• Keep transactions short to reduce blocking and version pressure.
• Use RCSI carefully for read/write blocking problems.
• Use explicit SNAPSHOT when multi-statement point-in-time consistency is required.
• Handle snapshot update conflicts with retries or user-visible conflict handling.
• Use SERIALIZABLE only when range protection is necessary.
• Make predicates and indexes support the locks or versions you expect.
• Test concurrent sessions, not only single-user behavior.
• Monitor blocking, deadlocks, and version store usage after isolation changes.