📋 Table of Contents
Introduction
Multi-Version Concurrency Control (MVCC) is the cornerstone of how modern databases handle concurrent transactions while maintaining ACID properties. Different databases implement MVCC in fundamentally different ways, each with its own trade-offs.
PostgreSQL: Heap Tuples with xmin/xmax
PostgreSQL stores each row version as a heap tuple with visibility metadata in the tuple header:
PostgreSQL MVCC Example:
BEGIN;
-- Transaction updates create new tuple
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Old tuple marked with xmax, new tuple with xmin
-- Another session reading sees old version
SELECT balance FROM accounts WHERE id = 1;
BEGIN;
-- Transaction updates create new tuple
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Old tuple marked with xmax, new tuple with xmin
-- Another session reading sees old version
SELECT balance FROM accounts WHERE id = 1;
Key PostgreSQL MVCC Features:
- HOT (Heap Only Tuples): Updates that don't change indexed columns reuse existing space
- Visibility Map: Tracks pages containing only visible tuples
- VACUUM: Reclaims space from dead tuples (manual or auto)
- Free Space Map: Tracks available space for new tuples
MySQL/InnoDB: Rollback Segments
InnoDB stores before-images in undo logs organized into rollback segments:
MySQL InnoDB MVCC:
START TRANSACTION;
-- Before-image stored in undo log
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Other transactions read from undo log
-- Each transaction has its own read view
START TRANSACTION;
-- Before-image stored in undo log
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Other transactions read from undo log
-- Each transaction has its own read view
Key InnoDB MVCC Features:
- Rollback Segments: Store before-images in undo tablespace
- Read View: Each transaction has its own consistent snapshot
- Purge Thread: Removes obsolete undo log entries
- Change Buffer: Caches changes to secondary indexes
Oracle: System Change Number (SCN)
Oracle uses System Change Number (SCN) and undo segments for consistent reads:
Oracle MVCC:
BEGIN
-- Oracle assigns SCN to each change
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Other transactions see consistent read using undo
-- Supports Flashback queries via undo retention
BEGIN
-- Oracle assigns SCN to each change
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Other transactions see consistent read using undo
-- Supports Flashback queries via undo retention
Key Oracle MVCC Features:
- SCN: System-wide logical timestamp
- Undo Segments: Store before-images in undo tablespace
- Flashback Query: Query historical data using undo
- Retention Guarantee: Configurable undo retention period
SQL Server: Version Store
SQL Server uses a version store in tempdb for row versioning:
SQL Server Version Store:
-- Enable snapshot isolation
ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON;
-- Row versions stored in tempdb
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT balance FROM accounts WHERE id = 1;
-- Enable snapshot isolation
ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON;
-- Row versions stored in tempdb
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT balance FROM accounts WHERE id = 1;
Feature Comparison
| Feature | PostgreSQL | MySQL/InnoDB | Oracle | SQL Server |
|---|---|---|---|---|
| Version Storage | Heap tuples | Undo logs | Undo segments | Version store (tempdb) |
| Visibility Info | xmin/xmax in tuple | Read view per transaction | SCN-based | Row versioning |
| Space Reclamation | VACUUM | Purge thread | Automatic (with retention) | Version cleanup |
| Hot Updates | HOT updates | Change buffer | In-place with undo | N/A |
| Performance Impact | VACUUM overhead | Undo log growth | Undo management | Tempdb contention |
Performance Implications
Memory Usage Comparison:
- PostgreSQL: shared_buffers + work_mem for sorting
- MySQL: innodb_buffer_pool + undo tablespace
- Oracle: SGA + PGA + undo tablespace
- SQL Server: Buffer pool + version store
Concurrency Patterns:
- Many concurrent readers: PostgreSQL (no readers block)
- Mixed read-write: InnoDB (good balance)
- Strict consistency: Oracle (best guarantees)
- Snapshot queries: SQL Server
When to Choose Which
Choose PostgreSQL when:
- You need predictable VACUUM scheduling
- Mostly read-heavy workloads
- Fine-grained control over bloat management
- HOT updates can optimize your workload
Choose MySQL/InnoDB when:
- Automatic undo management preferred
- Mixed read-write workloads
- Simple configuration needed
- Frequent secondary index changes
Choose Oracle when:
- Point-in-time querying (Flashback) needed
- Strict compliance/audit requirements
- Guaranteed read consistency crucial
- Complex mixed workloads
Interactive Demo
🐳 Docker MVCC Demo:
# Clone demo repository
git clone https://github.com/oracleai/mvcc-demo.git
cd mvcc-demo
# Start databases
docker-compose up -d postgres mysql oracle
# Run concurrent transaction test
./scripts/test_mvcc.sh
# View results
cat results/mvcc_comparison.json
# Clone demo repository
git clone https://github.com/oracleai/mvcc-demo.git
cd mvcc-demo
# Start databases
docker-compose up -d postgres mysql oracle
# Run concurrent transaction test
./scripts/test_mvcc.sh
# View results
cat results/mvcc_comparison.json
🔜 Coming Next: Foreign Keys
Next article: When to use foreign keys across databases, performance implications, and implementation differences.