DataTerminology.comNEW
Also known as: MVCC
A database concurrency technique that maintains multiple versions of data to allow non-blocking reads and consistent snapshots during concurrent writes.
DESCRIPTION
MVCC lets transactions read snapshot views of the database while writers create new versions, avoiding most read locks. It improves throughput and reduces contention, with garbage collection cleaning obsolete versions. Implementations vary, enabling isolation levels like snapshot isolation in PostgreSQL, MySQL/InnoDB, and Oracle. Careful tuning and indexing are required to manage bloat and ensure predictable transactional semantics.
ALTERNATIVE DEFINITIONS
PostgreSQL Documentation
PostgreSQL maintains transactional consistency through a method known as multiversion concurrency control (MVCC).
Highlights MVCC as PostgreSQL’s mechanism for transactional consistency.
MySQL Documentation
InnoDB implements multi-versioning by creating a new version of a row each time it is updated.
Explains row-versioning behavior underlying MVCC in InnoDB.
Oracle
Oracle Database uses multiversion read consistency to ensure that readers do not block writers and writers do not block readers.
States the non-blocking property enabled by multiversioning.
RELATED TERMS
Snapshot Isolation ACID Locking Serializability Write-Ahead Logging Transaction Isolation Levels Timestamp Ordering
COMPETING TERMS
Two-Phase Locking Optimistic Concurrency Control
KNOWLEDGE AREAS
Data Storage & Operations Data Engineering
TARGET AUDIENCE
Data Engineer Data Architect Software Developer
EXTERNAL SOURCES
- Wikipedia - Multiversion concurrency control
- PostgreSQL - Deep dive into MVCC implementation
- MySQL - InnoDB multi-versioning details
- Oracle - Multiversion read consistency explanation
TOP QUESTIONS
Why is Multi-Version Concurrency Control used?
It reduces read-write contention by allowing snapshot reads, improving throughput in concurrent workloads.
Category: Relevancy
How does MVCC differ from two-phase locking?
MVCC uses versioned snapshots to avoid read locks; 2PL relies on locks that can block reads and writes.
Category: Alternatives
What is snapshot isolation in MVCC systems?
An isolation level where transactions read a consistent snapshot, unaffected by concurrent writes.
Category: Definition
What are common MVCC pitfalls?
Version bloat, long-running transactions, and suboptimal vacuum/GC settings degrade performance.
Category: Mistakes
How do databases implement MVCC?
Row versioning with transaction IDs and visibility rules determines which version each transaction sees.
Category: Requirements
Does MVCC guarantee serializability?
Not by default. It supports various isolation levels; serializable often requires additional checks.
Category: Misunderstanding
How to tune MVCC performance?
Optimize vacuum/GC, index visibility patterns, and limit transaction duration to reduce bloat.
Category: Best practices
What is a write skew anomaly in MVCC?
A non-serializable anomaly where concurrent transactions lead to inconsistent combined outcomes without direct conflicts.
Category: Risks
Which databases use MVCC?
PostgreSQL, MySQL InnoDB, Oracle, and many modern OLTP systems implement MVCC or variants.
Category: Overview