SQL Server Snapshot Isolation Interview Questions: Version Stores and Update Conflicts

Snapshot isolation can make a busy SQL Server application feel less blocked, but the phrase “readers do not block writers” is not a complete interview answer. You must say which version each read sees, what happens when two transactions update the same row, and where old versions live. The distinction between READ_COMMITTED_SNAPSHOT and the SNAPSHOT isolation level changes the answer to a two-query consistency problem.

This guide works through an original two-session timeline, then turns it into a practical interview diagnosis. For a neighboring concurrency failure, see SQL Server Deadlock Interview Questions. A deadlock and a snapshot update conflict need different responses.

Evidence boundary: Official facts about SQL Server row versioning, database options, and conflicts are from Microsoft documentation linked below. Candidate reports: we have no verified same-cycle candidate report that a named company asks this exact scenario. Inference: the account values, schedule, operational checklist, and suggested interview response are original examples, not a transcript or universal production prescription.

The exercise: two reads and one concurrent update

The invented Accounts table begins with account 7 at balance $100. Session A starts a transaction and reads the balance. Session B changes it to $120 and commits. Session A reads again, then attempts to add $10 to the same row. What will A see on the second read, and can A commit its update?

The answer depends on the database option and A's isolation level. Under READ_COMMITTED_SNAPSHOT (RCSI) with A using ordinary READ COMMITTED, each statement gets a versioned view as of that statement. A can read $100, then later $120 after B commits. Under explicit SNAPSHOT isolation, A's transaction uses one consistent snapshot; its second read still sees $100. When A tries to update the row B changed after A's snapshot began, SQL Server detects an update conflict and A's transaction fails. It cannot silently turn its stale $100 into $110.

Official fact: Microsoft describes RCSI as a versioned implementation of READ COMMITTED, while SNAPSHOT gives a transaction-level consistent view in its snapshot isolation guide. State those labels before discussing performance; otherwise an interviewer cannot tell which guarantee you mean.

Read the schedule before naming an anomaly

The original timeline is intentionally small. It avoids unrelated inserts and joins so the visibility rule is testable.

Time	Session A	Session B	Consequence under RCSI / SNAPSHOT
T0	Begin transaction	—	A's isolation choice matters.
T1	Read account 7: $100	—	Both modes initially show $100.
T2	—	Set balance to $120; commit	A does not need to block B merely because it read.
T3	Read account 7 again	—	RCSI can see $120; SNAPSHOT continues to see $100.
T4	Attempt to update account 7	—	A SNAPSHOT transaction conflicts with B's post-snapshot write.

Do not import “repeatable read” claims into RCSI just because the word snapshot appears in its option name. An application that reads a set of rows in two READ COMMITTED statements can observe a different committed state on the second statement. If it requires a consistent multi-statement view, it needs an explicit design for that requirement, not a hopeful database label.

Also distinguish a committed value from an in-flight value. Neither versioned mode makes B's uncommitted $120 visible to A in this example. Versioning helps readers use committed older versions rather than waiting on a writer in many cases. It does not eliminate every lock, schema lock, or source of contention in SQL Server.

How do you enable each mode?

Official fact: SQL Server uses separate database options. READ_COMMITTED_SNAPSHOT ON changes the behavior of READ COMMITTED in that database. ALLOW_SNAPSHOT_ISOLATION ON permits transactions to request SNAPSHOT isolation. They are not synonyms. Microsoft's snapshot isolation documentation shows the settings and an ADO.NET example.

-- Run only after planning the database-level change and its impact.
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

-- For a session that deliberately needs transaction-level snapshot behavior:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- Perform the related reads and writes.
COMMIT TRANSACTION;

This is explanatory syntax, not an instruction to toggle production during an interview. Database-wide changes may require suitable permissions and coordination with active transactions. A session must choose SNAPSHOT to get that isolation level; merely allowing it at the database level does not convert every transaction. In a connection pool, verify isolation settings across reuse rather than assuming a previous request left a neutral session state.

The exact behavior of a query can also be changed by table hints and explicit locking choices. If an interviewer adds UPDLOCK to A's initial read, re-evaluate the schedule instead of repeating the original answer. Microsoft notes that such a hint can prevent a later update conflict by taking a lock earlier, but it also reintroduces blocking and should be justified by the workload.

What happens to old row versions?

When a row changes, a reader whose view predates the change may need the older version. The engine maintains version data long enough for eligible transactions to read the state they are entitled to see. This is work and storage, not a free shortcut around concurrency. Long-running readers can prevent cleanup and enlarge the version store.

The location is version-sensitive. Official fact: Microsoft's snapshot isolation guide states that without Accelerated Database Recovery (ADR), row versions are stored in tempdb; with ADR enabled, versions associated with snapshot isolation and ADR reside in the user database's Persistent Version Store (PVS). “Snapshot versions always live in tempdb” is therefore an outdated blanket answer. The actual SQL Server version and ADR configuration belong in the diagnosis.

In an interview, explain the consequence rather than only the location: monitor storage and long-lived transactions, establish a baseline, and investigate unusual growth against workload and cleanup activity. Do not delete version data or force-kill sessions simply to make a metric fall. Identify which transaction is retaining versions and what user work it represents before choosing an intervention.

Why does an update conflict occur?

SNAPSHOT uses optimistic concurrency for writes. A does not lock account 7 simply because it read the old version, so B can commit. At T4, A attempts to modify a row that changed after A's snapshot began. SQL Server rejects that stale update and rolls back the snapshot transaction. This is not a classic deadlock cycle and not a successful “last writer wins” overwrite. Microsoft's documentation describes the conflict and its transaction rollback.

The application response is to discard the failed transaction and retry the whole logical operation against a fresh snapshot when the business action is safe to retry. Replaying only the final UPDATE while keeping decisions derived from the old read can preserve the very inconsistency the conflict was meant to expose. A payment or inventory workflow also needs an idempotency strategy so a client timeout and retry cannot duplicate an external side effect.

Retries should be bounded and observable. If conflicts are frequent, investigate transaction length, write hotspot, and whether this isolation model fits the workload. An unlimited retry loop under a hot row can amplify contention. A targeted lock earlier in the transaction may be appropriate for a known write path, but the interview answer should explain why accepting some blocking is preferable there.

Under RCSI, writers still coordinate with other writers using locks. Do not say RCSI “turns off locking.” If the exercise changes from A reading twice to A and B updating concurrently, reason through the write-lock behavior separately from statement snapshot reads. A candidate who treats every blocked writer as proof that RCSI is misconfigured has conflated read and write paths.

Does snapshot isolation make a transaction serializable?

No. A transaction-level consistent view does not automatically make every multi-row business invariant safe under concurrent writes. Consider two doctors, each allowed to go off call only if another remains on call. Two snapshot transactions can each read that both are on call, then update different rows. Because they do not overwrite the same row, an update-conflict check on one row may not protect the cross-row invariant. This familiar write-skew shape is an inference-based illustration of why the application needs an explicit invariant strategy.

Possible strategies include an appropriate stronger isolation level, a constraint or single coordination row where the invariant can be enforced, or a carefully designed locking protocol. The right choice depends on the invariant and contention. Do not promise that changing to SERIALIZABLE is free; it may increase blocking or deadlocks. Conversely, do not choose a faster read mode without checking the correctness requirement.

Ask an interviewer whether the business needs a consistent report, a unique reservation, or an atomic transfer. Those are different problems. A reporting transaction may value a stable snapshot. A reservation may require a guarded write or uniqueness constraint. For money, define the ledger and side-effect boundary before choosing isolation by name.

A production diagnosis, not just a definition

Suppose the service team says: “After enabling RCSI, our report still sees different totals across its two queries.” Your first question is whether the report runs both queries inside one transaction and what level that transaction uses. RCSI only gives each READ COMMITTED statement a point-in-time view. The report can see different committed states across statements by design. If a single-statement aggregate changes between separate requests, that is also possible; it is not evidence of dirty reads.

Suppose the next complaint is “tempdb grew, so snapshot isolation is broken.” Check ADR status before concluding the version-store location. Then correlate version-store usage with long-running transactions, update rate, and configuration. Do not rely on one chart without time alignment. Finally, measure read blocking and write-conflict rate before and after the change. Improvement in average read latency can coexist with expensive version retention or retry storms.

Your remediation should have a rollback plan and an acceptance criterion: for example, stable report semantics with tolerable version-store growth and bounded write-conflict retries. “Enable snapshot isolation because it is faster” is neither a correctness argument nor a performance test.

Practice questions that expose the same boundaries

These links are related exercises, not assertions about a particular company's interview loop.

Complete practice question	What to rehearse
Explain ACID and isolation levels	Explain statement versus transaction visibility.
Design In-Memory Transactions with Snapshots and Savepoints	Track versions through a transaction timeline.
Explain database transactions and ACID	State the business guarantee before choosing an isolation level.
Design an In-Memory Transactional Key-Value Store	Describe conflict detection and retry boundaries.
Implement an Idempotent Versioned Database Update	Make a retry safe when an external client times out.

For a final rehearsal, draw T0–T4 from memory. State what A reads in RCSI and SNAPSHOT, where old versions can live with and without ADR, and why A's SNAPSHOT write fails. Then add one operational metric and one invariant that the isolation level alone does not solve. That is the answer an experienced interviewer can probe. Start with Explain ACID and isolation levels and explain this schedule aloud.

Sources and further reading

• Microsoft: Snapshot isolation in SQL Server
• Microsoft: SQL Server transaction locking and row versioning guide
• Microsoft: Accelerated Database Recovery