Transactions & Concurrency
Multiversion Concurrency Control
MVCC retains multiple logical versions so readers can choose a version visible to their snapshot while writers create successors. Visibility metadata, update conflict rules, and garbage collection—not “MVCC” alone—determine isolation and cost.
- Updates create version chains.
- A snapshot defines visibility.
- Readers and writers often avoid blocking each other.
- Garbage collection follows the oldest needed view.
- Long transactions create retention debt.
- MVCC is not an isolation level.
Start versions: v1 stock=10 committed at 10; v2 stock=8 committed at 20.
T1 snapshot time=15 reads v1=10. T2 snapshot time=25 reads v2=8.
A new v3 committed at 30 is invisible to both snapshots.
Cleanup cannot remove v1 while T1 may still read it.| Strategy | Version location | Cleanup concern |
|---|---|---|
| Append/new tuple | Heap/index-visible chain | Dead tuples and index cleanup |
| Undo-based | Current row plus undo records | Purge history after old snapshots |
| Separate version store | External store (for example tempdb-based) | Store growth and long readers |
The visibility algorithm must also account for aborted and in-progress creators. A timestamp alone is insufficient unless the system’s commit protocol makes that timestamp authoritative.