Some engines abort a long-running read with an error saying the row version it needed is no longer available; Oracle's ORA-01555 'snapshot too old' is the classic example. Explain what causes this class of failure and what tradeoff the database is making.

## The mechanism A snapshot-based reader is entitled to see the database as of a point in time. To serve it, the engine needs the row images that were current at that point. In designs that store superseded images in a separate retention area, those images are subject to reclamation once the engine believes nobody needs them, or, in older or misconfigured systems, once the space is needed for something else. If the reader then asks for an image that is gone, there is no correct answer available. Returning the current row would silently break consistency, so the engine aborts the statement with an error naming the missing version. Oracle's ORA-01555 is the canonical example; other systems raise equivalent errors when a configured maximum snapshot age is exceeded or the retention area is exhausted. ## Why an engine would ever do this Because the alternative also fails, just later and less legibly. Version retention is unbounded in principle: a transaction that stays open long enough forces the engine to preserve every version created since it started. An engine can respond in one of two ways: - **Preserve at all costs.** Nobody's query is ever aborted for lack of history. Space grows until the disk fills or the table becomes too bloated to scan. The failure mode is a slow, system-wide degradation followed by an outage that affects everyone. - **Bound the space.** History older than a configured window may be reclaimed. Readers that outlive the window die with a clear, targeted error. The failure mode is loud, local and attributable to a specific query. The second is a legitimate engineering choice: it converts an unbounded, shared, whole-system risk into a bounded, per-query one. Notably, some engines that traditionally take the first approach have added an optional maximum-snapshot-age setting precisely so operators can opt into the second. ## What makes it happen in practice - **Genuinely long reads on hot tables.** The classic: a multi-hour report over tables receiving heavy updates. The report's history requirement grows with the write rate, not with its own size. - **Under-sized retention.** Retention configured for a normal day, then a batch job triples the write rate. - **Fetch across commit.** An application opens a cursor, then loops fetching rows while committing work in the same session. Each commit can release the snapshot the cursor depends on, so the cursor asks for history the engine no longer guarantees. This is a code bug, not a capacity problem, and adding retention space only postpones it. - **A read that pauses.** A client that fetches slowly, or blocks on an external call between fetches, stretches its snapshot lifetime far beyond the query's actual work. - **Retention space consumed by a huge transaction.** One enormous write burst can fill the retention area and force premature reuse for everyone. ## Diagnosing it The error names a victim, not a cause: the query that dies is usually blameless. Ask three questions: how long did the failing read live; what was the write volume against the tables it read during that window; and how large is retention relative to that volume. If the read is short and still failing, suspect fetch-across-commit or a paused client rather than sizing. Repeated failures at the same time of day usually point at a batch job whose churn crowds out retention. ## Fixing it, in order of preference 1. **Make the read shorter or chunked.** Split by key range or time window into several short transactions, accepting that the chunks are not mutually consistent; often they do not need to be. 2. **Move it off the writer.** Run reports on a replica with its own retention budget. Be explicit about whether the replica reports its oldest reader back to the primary: if it does, you have moved the pin, not removed it, and the primary bloats instead. 3. **Size retention for the longest legitimate read, with headroom for peak write bursts,** and monitor consumption rather than waiting for the error. 4. **Fix fetch-across-commit patterns** by materialising the result set, or by holding a proper read-only transaction for the whole loop and doing the writes on a second connection. 5. **Schedule long reads away from heavy write windows,** which reduces their history requirement directly. ## The judgement to show A strong answer resists the reflex "add more retention space". Retention is bounded on purpose; enlarging it buys time and shifts the failure to disk pressure. The durable fix is to make long readers rarer, shorter, or somebody else's problem, and to decide consciously which failure you prefer, because you are always choosing between aborted readers and unbounded history.