Under the READ COMMITTED isolation level, two identical queries inside one transaction can return different results. Explain the visibility rule that causes this, and contrast it with taking one view for the whole transaction.

## The rule in one sentence Under READ COMMITTED, the unit of visibility is the **statement**, not the transaction. ## How the snapshot works in an MVCC engine Multi-version concurrency control means a row is not overwritten in place. An update writes a *new version* of the row and marks the old one as superseded, each version tagged with the identity of the transaction that created it (and, for a delete or update, the one that ended it). A read therefore has to decide which version it is allowed to see. That decision is made against a **snapshot**: a record of which transactions had committed at a particular instant. A row version is visible if the transaction that created it had committed by the snapshot instant and the transaction that removed it had not. READ COMMITTED takes a new snapshot **at the start of each statement**. If you run 1. `SELECT balance FROM account WHERE id = 1;` 2. (another transaction updates and commits) 3. `SELECT balance FROM account WHERE id = 1;` then statement 1 read against a snapshot in which the other transaction was still open, and statement 3 read against a snapshot in which it was committed. Both statements obeyed the guarantee — neither returned uncommitted data — and yet the results differ. This is the non-repeatable read, and it is legal by construction. The same mechanic produces phantoms: an insert that commits between your two `COUNT(*)` calls is invisible to the first snapshot and visible to the second. ## How a lock-based engine reaches the same behaviour Without versions, an engine achieves READ COMMITTED by taking a **shared lock** on each row as it reads it and releasing that lock immediately, rather than holding it until the transaction commits. Holding it during the read is sufficient to keep you off any row currently being modified by an uncommitted writer, which is the dirty-read guarantee. Releasing it right away is what leaves the row free to change under you the moment your statement finishes. Stronger levels are, in this family, exactly the levels that hold read locks longer. The two families feel very different operationally — MVCC readers never block, lock-based readers can — but they land on the same anomaly set for READ COMMITTED. ## What is still guaranteed: intra-statement consistency This is the part candidates most often miss. Because the snapshot is fixed for the *duration* of the statement, a single statement never sees a torn view: - A join across three tables sees all three as of the same instant. - An aggregate over ten million rows reflects one coherent state, even if it runs for a minute and thousands of commits land meanwhile. - A single `UPDATE ... WHERE` sees a consistent set of candidate rows. So the fix for most READ COMMITTED surprises is to make the unit of work a statement rather than a sequence of round trips. ## Contrast with a transaction-wide view A transaction-scoped snapshot (the model behind stronger MVCC levels) is taken once — typically at the transaction's first data-touching statement — and reused for every subsequent read. That makes reads repeatable and makes multi-statement reports internally consistent, at the price of two things: your transaction stops seeing the world advance, and the engine must retain old row versions for as long as your transaction lives, so long-running transactions accumulate version bloat and hold back cleanup of dead rows. READ COMMITTED's per-statement snapshot is the deliberate opposite trade: maximum freshness and minimum version retention, at the cost of read stability. ## A wrinkle worth knowing The per-statement snapshot governs *reads*. Writes have a second rule layered on top: when an `UPDATE` or `DELETE` reaches a row that a concurrent uncommitted transaction has already modified, it blocks, and after that transaction commits it re-examines the *newest* committed version rather than the one in its own snapshot. That means a writing statement can act on a row version newer than anything its snapshot could have returned. It is a deliberate concession so that concurrent updates do not silently no-op. ## Practical implications - Do not compute a total in one query and a detail listing in another and expect them to reconcile — they straddle commits. - Do not build "read then decide then write" logic across round trips without an explicit lock or a version check. - Do wrap genuinely multi-statement reporting in a stronger, transaction-scoped view when the numbers must tie out. - Do not assume opening a transaction alone freezes anything; under READ COMMITTED, `BEGIN` gives you atomicity of writes, not a stable read view.