MVCC Snapshot Isolation
MVCC gives each transaction its own consistent snapshot of the database at a timestamp, so readers never block writers and writers never block readers, at the cost of handling write skew.
Definition
Multi-version concurrency control (MVCC) keeps multiple versions of each row so that a transaction reads a consistent snapshot as of its start timestamp without taking read locks, and writers create new versions, so that readers and writers do not block each other, with snapshot isolation as the default and serializable as an optional stronger level.
Context and scope
Applied in PostgreSQL, Oracle, MySQL InnoDB, and most modern RDBMS where read concurrency without blocking is a baseline requirement.
Common misconceptions
- MVCC means serializable — MVCC provides snapshot isolation; serializable requires additional conflict detection (SI predicates) that not all engines implement
- MVCC has no overhead — each write creates a version and each read selects the visible version; the overhead is in storage and vacuum, not zero
Anti-patterns
- Assuming snapshot isolation prevents all anomalies and missing the write-skew that two transactions can both read the same stale state and write conflicting updates
- Running without vacuuming dead versions, so the table grows without bound as every update leaves a row version
Related concepts
Authoritative source
What it is
Multi-version concurrency control (MVCC) keeps multiple versions of each row so that a transaction reads a consistent snapshot as of its start timestamp without taking read locks, and writers create new versions, so that readers and writers do not block each other, with snapshot isolation as the default and serializable as an optional stronger level. Applied in PostgreSQL, Oracle, MySQL InnoDB, and most modern RDBMS where read concurrency without blocking is a baseline requirement. The data model and the access patterns are co-designed: a schema that is correct in the relational sense can be catastrophic for the queries the workload actually issues.
How it works
Indexing decisions follow the query shapes, not the column importance: a covering index turns a table scan into an index-only scan, but every index adds write amplification and storage cost. Partitioning aligns with the access dimension so a query touches one partition, not all. Transaction scope is minimized to hold locks briefly; long transactions are the root of contention and deadlock.
When to apply and pitfalls
Apply when data volume or query latency has a concrete business cost. The common pitfall is indexing for the current workload and ignoring the access patterns that growth will introduce — the index that helps today can serialize writes tomorrow. Another is treating the database as a black box, missing that the optimizer’s plan choice depends on statistics that drift as data changes.