In a database that uses multi-version concurrency control (MVCC), why can one transaction that stays open for hours cause tables and undo/version storage to keep growing, even when that transaction only reads and never writes?
answer
- oldest snapshot sets the cleanup horizon
- update = new version, old one survives
- dead versions / undo history list grow
- cleaner runs but is not allowed to reclaim
- read-only is not free: the snapshot is the cost
basics
~20 sAn open transaction pins a snapshot, and the engine may not reclaim any row version that the oldest live snapshot could still need. So while it sits there, superseded versions from every other transaction accumulate: table and index bloat, growing undo storage, slower scans.
solid answer
~50 sUnder MVCC an UPDATE does not overwrite a row: it produces a new version and leaves the old one, either in the table (PostgreSQL) or in undo/rollback storage (Oracle, InnoDB). A background cleaner — vacuum, undo purge, the InnoDB purge thread — later reclaims versions no one can still see. "No one can still see" is computed from the **oldest active snapshot in the system**. A read-only transaction that began at 09:00 and is still open at 12:00 pins that horizon at 09:00, so three hours of superseded versions from *all other* transactions are ineligible for cleanup, even though the idle reader will never look at 99% of them. The symptoms are dead-version accumulation and table/index bloat, an undo tablespace or history list that grows without bound, sequential scans reading mostly invisible rows, worse cache hit rates, and version chains that make point lookups walk many versions. The cure is bounding transaction age, not tuning the cleaner harder.
go deeper
Say that updates create new versions, that old versions can only be removed once no open transaction could still need them, and that an old open transaction therefore blocks cleanup.
Name the mechanism per engine family (dead tuples plus vacuum versus undo plus purge), explain the oldest-snapshot horizon, and list the visible symptoms of bloat.
Show how you would diagnose it: graph oldest-transaction age, check prepared transactions and replication slots, then fix by ending the holder and bounding duration with timeouts.
Discuss it as a shared-resource commons problem: one team's reporting session degrades everyone, so transaction-age limits, workload isolation onto replicas, and enforced timeouts are platform policy rather than per-service choices.
## MVCC in one paragraph Multi-version concurrency control lets readers avoid blocking writers by keeping more than one version of a row. When a transaction updates a row, the engine writes a **new version** and keeps the **old version** reachable, tagged with the transaction that created and superseded it. A reader gets a **snapshot** — effectively a record of which transactions were committed at the moment it started (or at the moment of its statement, depending on isolation level) — and for each row it walks the versions and shows the one visible to that snapshot. A DELETE is the same thing: it marks the version dead rather than erasing it immediately. Where old versions live differs by engine. PostgreSQL keeps them **in the table heap** as dead tuples, cleaned by vacuum. Oracle and MySQL/InnoDB keep before-images in **undo/rollback segments**, cleaned by undo purge (InnoDB's *history list*). The concept is identical: old versions cost storage and must be garbage-collected. ## The horizon rule Garbage collection cannot be per-transaction; it must be safe for every reader. So the engine computes a cutoff — call it the **cleanup horizon** — from the *oldest snapshot still in use anywhere in the instance*. Any row version that became obsolete **after** that point may still be needed by that snapshot, so it must be kept. This is why one idle transaction is so damaging. It does not need to touch the hot table. It does not need to write anything. Merely by existing with a snapshot, it says "the database as of 09:00 must remain reconstructable". Every UPDATE and DELETE performed anywhere in the system after 09:00 leaves garbage that cannot be reclaimed until that transaction ends. A single forgotten session — an interactive SQL window, a stuck worker, a BI tool with autocommit off, a leaked ORM session — can hold the horizon for the entire cluster. ## What growth actually looks like - **Table bloat.** A 2 GB table under heavy update churn grows to 20 GB of mostly dead versions. Sequential scans read all of it and throw most away, so scan time rises even though the live row count is flat. - **Index bloat.** Each new version needs index entries; dead entries stay until cleanup, so indexes grow and lookups touch more pages. - **Undo growth.** In undo-based engines the undo tablespace or history list grows continuously; long readers eventually hit errors when the required before-image is unavailable or the undo space fills. - **Version-chain walks.** A point lookup on a hot row updated a million times may traverse a long chain of versions to find the visible one, turning a fast read into a slow one. - **Cache pollution.** Buffer pool and page cache fill with dead data, so the working set no longer fits and physical I/O rises. - **Cleaner churn.** The cleaner keeps running and keeps finding nothing it is allowed to remove — CPU and I/O spent for no reclaim. Protective maintenance such as transaction-id wraparound handling can also be blocked, which in the extreme forces read-only behaviour. ## Why "it is only reading" is not a defence Candidates often assume read-only transactions are free. Under MVCC the snapshot *is* the cost. A repeatable-read or serializable transaction holds one snapshot for its entire lifetime; a read-committed transaction takes a fresh snapshot per statement, so an idle read-committed session between statements is usually less harmful — but it still holds every lock it has taken and, in several engines, still contributes to the horizon while the transaction is open. Do not rely on isolation level to make long transactions safe. ## What actually fixes it 1. **Bound transaction duration.** An idle-in-transaction timeout that terminates sessions holding an open transaction while sending nothing, plus statement timeouts, plus alerting on the age of the oldest transaction (age in seconds is the metric to graph, not the count of connections). 2. **Split long work.** Chunked commits for batch jobs, so no single snapshot spans hours. 3. **Isolate genuinely long readers.** Run hours-long analytics on a replica or an extracted copy rather than on the primary — accepting that on a physical replica the same horizon problem can be pushed back to the primary if standby feedback is enabled. 4. **Do not fight it with cleaner tuning.** Making vacuum or purge more aggressive cannot reclaim versions the horizon forbids reclaiming. Tuning helps only once the horizon moves. 5. **Watch the usual sources.** Interactive sessions with autocommit off, pool sessions that begin a transaction on checkout, failed jobs that never rolled back, and orphaned prepared (two-phase) transactions that pin the horizon indefinitely. The sentence to leave the interviewer with: **in MVCC, cleanup is gated by the oldest live snapshot, so the age of your longest transaction is a global cost paid by every other transaction in the system.**
- Vacuum or purge is running constantly but the table keeps growing. What is your first diagnostic?Find the age of the oldest running transaction, of the oldest prepared two-phase transaction, and of any replication slot in the instance. If cleanup runs and reclaims nothing, the horizon is pinned by something older than the garbage, so the answer is to identify and end that holder, not to tune the cleaner. Only after the horizon moves will another cleanup pass actually free space.
- Does using read committed instead of repeatable read make long transactions safe?It helps but does not make them safe. Read committed takes a new snapshot per statement, so a session between statements may hold a much younger horizon than a repeatable-read session. But it still holds locks on everything it has written and, in several engines, still contributes to the horizon while the transaction is open. Duration remains the problem.
It is like one person refusing to leave a photo exhibition that must stay exactly as it was when they walked in. The gallery keeps hanging new pictures but is not allowed to take down a single old one until they leave.
saying these in an interview costs you the question
- Claiming a read-only transaction costs nothing because it takes no locks
- Blaming the vacuum/purge settings when the real cause is a pinned horizon
- Thinking an UPDATE overwrites the row in place under MVCC
- Believing only the long transaction's own tables are affected rather than the whole instance
- Proposing to reclaim space by rebuilding the table while the long transaction is still open