Many multi-version engines implement their REPEATABLE READ level as snapshot isolation. What does snapshot isolation actually guarantee, and why does that make the SQL standard's level names an unreliable guide?

## What snapshot isolation is Multi-version concurrency control keeps several committed versions of each row. When a transaction starts (or issues its first statement, depending on the engine), it takes a **snapshot**: a marker identifying exactly which transactions had committed at that instant. From then on, every read in that transaction is served from the versions visible at that marker, plus the transaction's own uncommitted writes. Nothing another transaction commits afterwards becomes visible. That gives three properties at once: - Readers never block writers, and writers never block readers — there are no shared read locks at all. - The whole transaction sees one internally consistent state of the database, so multiple queries across multiple tables agree with each other. - Write-write conflicts are still policed: if two concurrent transactions update the same row, one commits and the other aborts (commonly described as first-committer-wins) rather than silently overwriting. ## Why it gets labelled REPEATABLE READ Run snapshot isolation against the SQL standard's three phenomena: - *Dirty read* — impossible; only committed versions are ever visible. - *Non-repeatable read* — impossible; a re-read of the same row returns the same snapshot version. - *Phantom read* — impossible for the reader; re-running a range query against a fixed snapshot returns the same rows. It clears the whole table, so it is at least as strong as the standard's REPEATABLE READ, and by the letter of the table it even satisfies SERIALIZABLE's phenomena column. Vendors therefore map it onto REPEATABLE READ (or ship it as an extra SNAPSHOT level). That is conforming behaviour, because levels are ceilings on permitted phenomena, not exact behaviour specs. ## Where the naming breaks down Snapshot isolation is **not serializable**. The gap is structural, and the phenomena list simply never described it, because the list was catalogued from lock-based implementations. The general shape: two concurrent transactions each read an overlapping set of rows, each decides something based on what it read, and each then writes a *different* row. No two writes touch the same row, so the write-conflict check never fires, and both commit. Yet each made its decision on a state that the other's commit invalidated — an outcome no serial execution could produce, because in either serial order the second transaction would have seen the first one's write. A second, subtler gap: the read-only anomaly, where a transaction that writes nothing at all observes a state inconsistent with any serial ordering of the writers around it. That is impossible under serializability and possible under SI. So 'passes the three-phenomenon checklist' and 'serializable' are different claims, and an engine can honestly advertise the former while not providing the latter. ## Practical consequences **Portability is not free.** Application code that was correct against one engine's REPEATABLE READ can be wrong against another's, because one may take a transaction-wide snapshot, another may hold shared locks to commit, and a third may add next-key locking so writers block. Migrating between engines requires re-examining every transaction whose correctness depends on isolation rather than on constraints. **Snapshot age matters.** Because a long transaction pins a snapshot, old row versions cannot be reclaimed while it runs. Long-running readers therefore cause version accumulation and storage/cleanup pressure, and can slow scans that must skip many dead versions. This is an operational property of SI that no isolation-level name conveys. **Statement-level vs transaction-level snapshots.** At READ COMMITTED, multi-version engines typically take a *new* snapshot per statement; at REPEATABLE READ/SI, one snapshot for the whole transaction. That single difference explains most of the behaviour change teams see when they raise the level, including why long transactions suddenly return 'stale' data. **How to protect an invariant under SI.** Do not rely on the level name. Options, cheapest first: express the invariant as a database constraint (unique, check, exclusion) so the engine enforces it regardless of level; perform the decision and the write in one atomic conditional statement so the write-conflict check applies; explicitly lock the rows the decision depends on so the read becomes a write-intent; or move that transaction to a true SERIALIZABLE level and handle retries. **How to talk about it in an interview.** Say what the guarantee *is* (consistent snapshot + write-conflict detection), say what it is *not* (serializable), and say why the standard's names cannot settle the question. That is the answer that distinguishes someone who has operated these systems from someone who memorized the table.