A read snapshot can be taken once per statement or once for an entire transaction. What is the practical difference in what a long-running transaction reads, and what problems does each choice create?
answer
- fresh snapshot per statement vs one per transaction
- per-statement: statements consistent, transaction is not
- per-transaction: stable but monotonically stale
- snapshot usually taken at first read, not at BEGIN
- writer walks version chain forward and re-checks predicate
basics
~20 sA per-statement snapshot makes each statement see everything committed up to its own start, so repeated reads inside one transaction can change. A transaction-lifetime snapshot freezes one instant for all statements, giving stable, mutually consistent reads but an increasingly stale view.
solid answer
~60 s**Per-statement snapshots** (the READ COMMITTED behaviour of most engines) re-derive the visible set at the start of every statement. Each statement sees a fresh, consistent view, but two identical SELECTs in one transaction can return different rows or different values, and two statements reading related tables may see them at different instants — so a report assembled from several queries can be internally inconsistent. **One snapshot per transaction** (snapshot-isolation styles such as REPEATABLE READ) takes the snapshot at the transaction's first read and holds it. Every statement then reads the same instant: repeated reads are stable and multi-table reports are mutually consistent. The costs are staleness — the transaction never sees anything committed after it began, however long it runs — and write conflicts, since an update landing on a row changed since the snapshot must be resolved by aborting or by re-reading. A subtlety in per-statement mode: an UPDATE that finds its target already modified by a just-committed transaction typically walks forward to the newest committed version and re-evaluates its predicate, so a writing statement can touch a version its own read snapshot could not see.
code
sql · 13 lines-- per-statement snapshots: the two counts may differ
BEGIN;
SELECT count(*) FROM orders WHERE status = 'NEW';
-- another transaction commits three new orders here
SELECT count(*) FROM orders WHERE status = 'NEW';
COMMIT;
-- transaction-lifetime snapshot: both counts are identical,
-- and both reflect the instant of the first read
BEGIN;
SELECT count(*) FROM orders WHERE status = 'NEW';
SELECT count(*) FROM orders WHERE status = 'NEW';
COMMIT;go deeper
State plainly that a fresh snapshot per statement means repeated reads can change, while one snapshot per transaction keeps them stable.
Give a concrete anomaly for each mode — the inconsistent two-table report versus staleness plus serialization aborts — and note the snapshot is taken at first read.
Bring in the writer walking the version chain forward under per-statement snapshots, the retry discipline required under a held snapshot, and the retention pressure a long-open snapshot creates.
Frame it as a system-wide policy: which workloads get held snapshots, what retry and idempotency contract the application must honour, and what bound you put on transaction duration to keep the tradeoff affordable.
## Two moments a snapshot can be taken Snapshots are cheap enough to take often, so an engine has a genuine choice about *when*: once for the whole transaction, or fresh for each statement. Both are implemented with the identical machinery from the previous topic — bounds plus an in-flight set — the only difference is the instant it captures and how long it lives. ## Per-statement snapshots Every statement asks for a new snapshot when it begins. Consequences: - **Each statement individually is consistent.** A single SELECT never sees half of another transaction's commit, no matter how many rows or tables it scans, because it holds one snapshot for its whole execution. - **Across statements, the view moves.** Run the same SELECT twice inside one transaction and rows can appear, disappear, or change value — the *non-repeatable read* and *phantom read* anomalies. This is not a bug; it is the point of the mode. - **Multi-statement reports can be internally inconsistent.** Query the orders table, then the payments table; if a transaction commits between them you may report an order with no payment or a payment with no order, even though no such state ever existed at any single instant. - **Long transactions stay fresh.** A transaction running for an hour still sees data committed a second ago on its next statement. ## Transaction-lifetime snapshots One snapshot, usually taken lazily at the first read (so `BEGIN` alone does not freeze anything). Consequences: - **Repeatable reads.** The same query returns the same result all transaction long. - **Cross-statement consistency.** Ten queries across ten tables all reflect one instant — the correct choice for reports, exports, consistency checks, backups, and anything that reconciles two datasets. - **Monotonic staleness.** The view is exactly as old as the transaction. A four-hour job reads four-hour-old data by the end, and cannot be made fresher without ending the transaction. - **Write conflicts become visible to the application.** If the transaction tries to update a row that some other transaction modified and committed after the snapshot, the engine cannot silently apply the write on a stale image — under snapshot isolation the usual answer is first-updater-wins, and the loser is aborted with a serialization error. Applications must be prepared to retry. - **Downstream cost.** An open snapshot pins the version horizon: the engine must retain the old versions that snapshot might still need. ## The writer's exception in per-statement mode A detail that separates a rehearsed answer from an understood one. Under per-statement snapshots, consider `UPDATE accounts SET balance = balance - 10 WHERE id = 7 AND balance >= 10` running concurrently with another transaction that just committed a change to row 7. The updating statement's snapshot predates that commit, so strictly it should see the old version — but writing to a stale image would lose the other transaction's update. Instead the engine blocks on the row lock, and once the other transaction commits, it **follows the version chain forward to the newest committed version and re-evaluates the predicate** against it. If the row still qualifies, the update proceeds on the new version; if not, it is skipped. So a writing statement can act on data its own read snapshot could not see — which is exactly why `balance = balance - 10` is safe under per-statement snapshots while read-then-write in application code is not. Under a transaction-lifetime snapshot the engine does not do this silently: re-reading forward would break the isolation guarantee, so the conflict surfaces as an error the application must handle. ## Choosing between them - Short OLTP transactions that read-modify-write single rows: per-statement snapshots plus predicate-carrying updates or explicit row locking are the pragmatic default, with the least abort noise. - Anything that must reconcile multiple queries — financial reports, invariant checks, consistent exports, data comparisons across tables: a transaction-lifetime snapshot, deliberately opened and deliberately kept short. - Long analytical transactions: recognize the tradeoff explicitly. You are buying consistency with staleness and with retention pressure on the storage engine. ## What interviewers listen for The candidate should articulate that the same visibility machinery is used in both cases, that the difference is purely *when* the instant is captured and how long it is held, and should give one concrete failure of each mode: the inconsistent two-query report under per-statement snapshots, and the serialization abort plus staleness under a transaction-lifetime snapshot.
- Is the transaction-lifetime snapshot taken when the transaction begins, or later?In practice it is taken lazily at the first statement that actually reads data, not at BEGIN. That matters because a connection that opens a transaction and then sits idle has not yet frozen a view — but once the first read happens, the instant is fixed and the engine must retain everything that view might need.
- Under per-statement snapshots, how can 'UPDATE t SET n = n - 1 WHERE id = 5' be safe if a concurrent transaction just changed that row?The update blocks on the row lock, and when the other transaction commits it follows the version chain forward to the newest committed version, re-evaluates the WHERE clause against it, and applies the arithmetic to that fresh value. The decrement is therefore computed from current data, not from the statement's older read snapshot. The same safety does not exist if the application reads the value into a variable and writes it back.
saying these in an interview costs you the question
- Believing the transaction snapshot is taken at BEGIN in all engines.
- Thinking a per-statement snapshot means a single SELECT can see a partially committed transaction.
- Assuming a transaction-lifetime snapshot means later writes cannot conflict — the conflict surfaces as an abort instead.
- Claiming a longer transaction eventually 'catches up' and sees newer commits under a held snapshot.