MVCC
Serve consistent snapshots by retaining multiple committed versions of data.
2–5 minute refresher
Readers choose a snapshot; writers create a new version
30second
refresher
refresher
Multi-version concurrency control stores multiple committed versions so readers can observe a stable snapshot while writers create newer versions. Visibility rules and validation determine the isolation level.
What problem does it solve?
Locking readers and writers against each other limits concurrency and makes long queries disruptive. Snapshots let them overlap while preserving a coherent view.
How it works
- Assign the transaction a read timestamp or snapshot.
- Return the newest version visible at that snapshot.
- Buffer writes as new versions rather than overwriting in place.
- Validate write conflicts and commit with a later timestamp.
- Garbage-collect versions older than every active snapshot and recovery requirement.
Decision guide
Key trade-offs
| Choice | What you gain | What it costs |
|---|---|---|
| Long snapshots | Stable analytical reads | Version retention and cleanup pressure |
| Snapshot isolation | High concurrency | Write-skew anomalies |
| Serializable validation | Strong invariants | Retries and coordination |
What happens if?
A reader holds a snapshot for hours
Old versions cannot be reclaimed, increasing storage and compaction work. Bound snapshot age, isolate analytics, or export a durable snapshot outside the transactional storage path.
Where it appears
- PostgreSQL
- Spanner
- CockroachDB
- FoundationDB
Senior interview modeGive an anomaly allowed by snapshot isolation.Show answer
Write skew: two transactions read the same invariant, each updates a different row, and both commit because their write sets do not conflict—even though the combined result violates the invariant.