In a multiversion engine, what is the difference between taking a read snapshot once per statement and taking one per transaction, and how does that map to READ COMMITTED versus REPEATABLE READ?

## What a snapshot is In a multiversion engine, an update does not overwrite a row — it stores a **new version** and leaves the old one until nothing can still need it. Each version is stamped with the transaction that created it (and the one that removed it). A **snapshot** is the visibility rule the reader carries: roughly, the set of transactions considered already committed. Given a snapshot, the engine walks the versions of a row and returns the one that was committed and current as of that snapshot. The interesting question is therefore not *how* versions are chosen but **how often a new snapshot is taken**. That single decision is what separates the two isolation levels people ask about. ## Per-statement snapshots — READ COMMITTED At READ COMMITTED, the engine takes a **new snapshot at the beginning of each statement**. Consequences: - Each statement sees everything committed before that statement started. A transaction that runs for a minute sees the world move under it, one statement at a time. - A single statement is still internally consistent: it uses one snapshot from start to finish, so a long-running `SELECT` never sees half of a concurrent transaction's work. - Re-reading a row in a later statement can return a different value: **non-repeatable read permitted**. - Writes are easy: when an `UPDATE` blocks on another transaction's row lock and that transaction commits, the waiting statement can simply take a newer snapshot for that row, re-evaluate its `WHERE` clause, and proceed. This is why write conflicts rarely surface as errors at this level. - Old row versions become collectable quickly, because no long-lived snapshot pins them. ## Per-transaction snapshots — REPEATABLE READ / snapshot isolation Here the engine takes **one snapshot** and uses it for every read until the transaction ends. Two important details: 1. The snapshot is normally taken at the **first statement**, not at `BEGIN`. A transaction that opens and then sits idle for a minute still sees a view from when it first read something. (Engines usually offer a way to fix the snapshot at transaction start explicitly, which matters for coordinating a consistent dump across sessions.) 2. The transaction **can see its own writes**, which sit outside the snapshot rule — you always read your own uncommitted changes. Consequences: - Re-reading a row always returns the same value: **non-repeatable reads prevented**. Every query in the transaction reflects the same instant, so multi-query reports tie out. - Reads are **stale** by design. A transaction running for a minute reports data as of a minute ago. For a report this is usually right; for a check-then-act decision it can be dangerously wrong, because you are validating against a world that has moved on. - Reads still **never block writers**, and writers never block readers. The concurrency benefit of multiversioning is preserved at both levels. - **Writes get harder.** If your transaction updates a row that has changed since your snapshot, the engine cannot let you silently re-read the newer version — that would contradict everything else you have seen. It aborts your transaction with a **serialization failure**, and the application must retry the transaction from the beginning. Code running at this level needs a retry loop; code at READ COMMITTED usually does not. - **Version retention cost.** The engine must keep every row version that your snapshot might still need. A long-running transaction at this level therefore holds back cleanup of old versions across the whole database, causing table/undo growth and, in some engines, errors when the retained history is exceeded. Long analytical transactions at REPEATABLE READ are a classic cause of storage bloat. ## Same name, different mechanism Be careful with the level *names*. In a lock-based engine, REPEATABLE READ means "shared read locks are held until commit" — readers block writers, and the data you see is always the latest committed rather than a snapshot. The observable guarantee about re-reads is the same; the performance profile is the opposite. When someone asks "does REPEATABLE READ prevent it", the complete answer names the mechanism, because that determines whether you should expect blocking or staleness plus retries. Also note that engines differ in what their REPEATABLE READ additionally forbids beyond the standard's minimum — some snapshot implementations also stop repeated range queries from changing, which the standard does not require. Do not assume; that behaviour is engine-specific. ## Choosing Use per-statement (READ COMMITTED) when statements are independent, freshness matters, and the transaction acts on what it just read. It is the right default for typical transactional workloads and keeps version churn low. Use a transaction-wide snapshot when several reads must be mutually consistent — a multi-query report, an export, a consistency check, a computation that reads the same data twice — and accept the staleness, the retention cost, and the retry requirement for any writes. And remember the third option: collapse the reads into **one statement**. A single statement gets one snapshot at any isolation level, so a join or a common table expression that answers the whole question at once is often the cheapest way to get consistency without holding a long snapshot at all.