MVCC (Multi-Version Concurrency Control)

Overview

MVCC is a concurrency control method that allows multiple versions of data to exist simultaneously, enabling readers and writers to operate without blocking each other. This approach provides high concurrency while maintaining transaction isolation.

Core Concepts

Multiple Data Versions

• Each transaction sees a consistent snapshot of data
• Old versions kept until no longer needed
• Writers create new versions instead of modifying existing data
• Readers access appropriate version based on transaction start time

Transaction Snapshots

• Each transaction gets a snapshot of data at start time
• Snapshot determines which data versions are visible
• Provides consistent read view throughout transaction
• Implements repeatable read isolation

Version Visibility

• Transaction ID (XID) determines version visibility
• Committed transactions' changes become visible to new transactions
• Uncommitted changes remain invisible to other transactions
• Rollback simply marks versions as invalid

Implementation Details

PostgreSQL MVCC

• Uses transaction IDs (32-bit integers)
• Each row has xmin (created by) and xmax (deleted by) fields
• Vacuum process removes old, unused versions
• Prevents most locking for read operations

Oracle MVCC

• Uses System Change Number (SCN)
• Undo segments store old versions
• Flashback queries can access historical data
• Read consistency guaranteed across long-running queries

Benefits and Trade-offs

Advantages:

• Readers don't block writers
• Writers don't block readers
• High concurrency
• Consistent read views
• No deadlocks between readers and writers

Disadvantages:

• Storage overhead for multiple versions
• Vacuum/cleanup processes needed
• Complex implementation
• Potential for transaction conflicts

MVCC enables high-performance concurrent access while maintaining strong consistency guarantees.