MVCC (Multi-Version Concurrency Control)

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

Related Features

• Transactions: /docs/features/transactions/
• Distributed Transactions: Two-phase commit across nodes
• Deadlock Detection: Automatic deadlock resolution