Skip to content

MVCC (Multi-Version Concurrency Control)

Multi-Version Concurrency Control allows multiple transactions to access the same data simultaneously without blocking each other. Each transaction sees a consistent snapshot of the database at a specific point in time.

Overview

HeliosDB implements PostgreSQL-compatible MVCC with Serializable Snapshot Isolation (SSI). Key capabilities:

  • Isolation without locking: Readers don't block writers and vice versa
  • Snapshot consistency: Each transaction sees a consistent view of the database
  • Conflict detection: Automatic detection of write-write and read-write conflicts
  • PostgreSQL compatibility: Same isolation levels and behavior

Quick Start

1. Basic Transaction Usage

-- Start transaction (uses default isolation level)
BEGIN;
  SELECT * FROM accounts WHERE id = 1;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

2. Isolation Levels

-- READ COMMITTED (default) - Best performance
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ - Consistent snapshots
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE - Strictest, detects conflicts
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3. Serializable Conflict Detection

-- Transaction 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Don't commit yet...

-- Transaction 2 (concurrent)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
COMMIT;  -- Succeeds (first committer wins)

-- Transaction 1 continues
COMMIT;  -- Fails with serialization_failure

When to Use MVCC

Use Case Recommended Isolation
High-concurrency applications READ COMMITTED
Long-running reports REPEATABLE READ
Financial transactions SERIALIZABLE
Inventory management SERIALIZABLE
Analytics queries READ COMMITTED
Batch data exports REPEATABLE READ

Performance Characteristics

Isolation Level Overhead Use When
READ COMMITTED ~0% High throughput needed
REPEATABLE READ <1% Consistent reads required
SERIALIZABLE 2-5% Strict consistency critical

Maintenance

-- Clean up old row versions
VACUUM ANALYZE;

-- Monitor version chains
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables;

Documentation

Document Description
MVCC_CONFIGURATION_GUIDE.md Detailed configuration and setup
Quick Start Fast getting started guide
Deadlock Prevention Handling deadlocks
  • Transactions: /docs/features/transactions/
  • Distributed Transactions: Two-phase commit across nodes
  • Deadlock Detection: Automatic deadlock resolution

Implementation Details

For historical implementation documentation (development team reference only): - /docs/archive/historical/implementation-phases/mvcc/

Status: Production Ready Version: v7.0