A transaction is guaranteed that any row it re-reads still shows the values it saw earlier. Why is that guarantee still not enough for a query that aggregates a range of rows, and what stronger guarantee would be needed?

## Two different questions a transaction can ask 1. **"What is the value of *this* row?"** — identified by primary key or already fetched. Stability here means: re-reading that row returns the same values. 2. **"Which rows match *this predicate*, and what do they sum to?"** — a range, a filter, an existence check, a `COUNT`. The answer is a *set*, and the set's membership is not pinned by any statement about the rows you have already seen. A guarantee that re-reads of known rows are stable answers only the first question. That is the boundary this question is about. ## Why the range query still moves Consider a transaction that does: ```sql SELECT SUM(amount) FROM orders WHERE region = 'EU'; -- 10,000 -- ... other work ... SELECT SUM(amount) FROM orders WHERE region = 'EU'; -- 10,400 ``` Between the two, another transaction inserted an EU order of 400 and committed. Every row the first query read is unchanged — the per-row guarantee is fully honoured. The *new* row was never read, so nothing protected it from appearing. Deletes do the same in reverse, and an update that moves a row *into* the predicate (`region` changed from `US` to `EU`) has the same effect: it is a row that was not in your result set and now is. The general statement: per-row stability constrains **existing, already-read rows**. Set membership is constrained by nothing unless the engine takes a guarantee over the predicate itself. ## Where this bites in real code - **Aggregates that must tie out**: a report whose subtotals and grand total are computed by separate queries. - **Existence checks used to authorize an action**: "there is no overlapping booking for this room, therefore I may insert one." Two transactions each check, each find nothing, and each insert. The check was stable; the world was not. - **Capacity checks**: "count of active sessions is 9, limit is 10, so I may add one." - **Re-scanning a range in a loop** while other writers are active. Note the pattern in the last three: the transaction reads a predicate and then *writes* something that would have changed the predicate's answer. That is what makes it dangerous rather than merely surprising. ## What a stronger guarantee looks like Three mechanisms, with different costs: **Predicate / range locking.** A lock-based engine can lock not just the rows that exist but the *gaps between them* in an index range, so any insert that would fall inside the range must wait. This makes the predicate's answer stable. It is precise but expensive: it blocks writers whose rows you never see, and it needs a suitable index or it degrades toward locking far more than intended. **A transaction-wide snapshot.** If every read is answered from one fixed instant, then a row committed after that instant is simply invisible, whether it is a row you read before or a brand-new one. Repeating the aggregate returns the same total. This makes *reads* stable at no blocking cost — which is why many multiversion engines' REPEATABLE READ is stronger than the standard requires. But it does not make *writes* safe: your snapshot hides the concurrent insert, so a decision made from the snapshot can still be wrong at commit time. Two transactions can each see "no overlapping booking" in their own snapshot and both insert. **Serializable.** The engine guarantees the outcome equals *some* serial order, by locking predicates or by tracking read–write dependencies and aborting a transaction whose reads have been invalidated. This is the only level that makes the read-then-write pattern safe without you writing extra code — at the cost of aborts to retry and bookkeeping overhead. ## The application-level alternatives Often cheaper than raising the level: - **Do it in one statement.** A single query is evaluated against one consistent view, so a single `INSERT ... SELECT`, or an aggregate computed once with a common table expression, has no gap to exploit. - **Make the database enforce the invariant.** A unique constraint or an exclusion-style constraint on the range turns the race into a constraint violation you can catch — far more robust than any read-based check, because it does not depend on isolation levels or on every code path behaving. - **Lock a parent row.** Serialize all writers of the predicate through an exclusive lock on the owning entity (the room, the account, the tenant). Simple, correct, and costs throughput only within that entity. ## The interview-ready summary "Stability of a re-read is a promise about rows I already read. Aggregates and existence checks ask about a predicate, and a concurrent insert or delete can change the answer without touching any row I read. To pin the answer I need something that covers the predicate: range locking, a transaction-wide snapshot, or serializable isolation — and if I'm going to *write* based on the check, I'd rather use a constraint or lock the parent row than rely on the isolation level."