---
title: "HeliosDB MVCC Configuration Guide"
---

# HeliosDB MVCC Configuration Guide
## Multi-Version Concurrency Control with Serializable Snapshot Isolation

## Overview

HeliosDB implements PostgreSQL-compatible MVCC (Multi-Version Concurrency Control) with Serializable Snapshot Isolation (SSI). This guide explains how to correctly configure and use MVCC features, especially for SERIALIZABLE isolation level.

## Transaction Isolation Levels

HeliosDB supports four PostgreSQL-compatible isolation levels:

| Isolation Level | Snapshot Created? | Reads Tracked? | Writes Tracked? | Conflict Detection | Overhead |
|--------------------|-------------------|----------------|-----------------|-------------------|----------|
| READ UNCOMMITTED | No | No | No | None | ~0% |
| READ COMMITTED | No | No | No | None | ~0% |
| REPEATABLE READ | Yes | No | No | None | <1% |
| SERIALIZABLE | Yes | Partial | Yes | Full | 2-5% |

### Usage Examples

#### READ COMMITTED (Default)

```sql
-- Default isolation level
BEGIN TRANSACTION;
-- or
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Non-repeatable reads possible
SELECT balance FROM accounts WHERE id = 1;  -- Returns 100
-- (Another transaction updates balance to 200)
SELECT balance FROM accounts WHERE id = 1;  -- May return 200

COMMIT;
```

#### REPEATABLE READ

```sql
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Repeatable reads guaranteed
SELECT balance FROM accounts WHERE id = 1;  -- Returns 100
-- (Another transaction updates balance to 200)
SELECT balance FROM accounts WHERE id = 1;  -- Still returns 100 (snapshot)

-- Write conflicts NOT detected (both can commit)
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

COMMIT;
```

#### SERIALIZABLE

```sql
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Strict serializability
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

-- If another transaction modifies id=1 concurrently:
COMMIT;  -- May fail with "serialization_failure"
```

## Known Limitations

### SELECT Queries Don't Track Reads

**Current Limitation**: `SELECT` statements do NOT track reads in the transaction read set, which means read-write conflicts involving SELECT are NOT detected in SERIALIZABLE transactions.

#### Problem Scenario

```sql
-- Transaction 1 (Connection 1)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 100
-- WARNING: READ NOT TRACKED!

-- Transaction 2 (Connection 2)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;  --  Succeeds

-- Transaction 1 continues
INSERT INTO audit_log (message) VALUES ('balance was 100');
COMMIT;  -- BUG: Should fail but succeeds!
```

**Expected Behavior**: Transaction 1 should abort with `serialization_failure` because it read data that was later modified.

**Actual Behavior**: Both transactions commit successfully (INCORRECT).

### Workarounds

#### Option 1: Use SELECT FOR UPDATE (Recommended)

```sql
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--  SELECT FOR UPDATE tracks the read as a write
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- Now conflicts will be detected
-- If another transaction updates id=1, this will fail at COMMIT
```

#### Option 2: Use REPEATABLE READ Instead

```sql
-- If full serializability isn't required, use REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- Snapshot-consistent

-- You get consistent reads but no conflict detection
```

#### Option 3: Use UPDATE to Force Tracking

```sql
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Dummy UPDATE to force tracking
UPDATE accounts SET balance = balance WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;

-- Now the row is tracked in the write set
```

## Conflict Detection

### What Gets Detected

HeliosDB's SSI implementation detects:

**Write-Write Conflicts** (UPDATE/DELETE):
```sql
-- T1: UPDATE accounts SET balance = balance + 10 WHERE id = 1;
-- T2: UPDATE accounts SET balance = balance - 5 WHERE id = 1;
-- Result: Second COMMIT fails with serialization_failure
```

**Read-Write Conflicts** (UPDATE/DELETE):
```sql
-- T1: UPDATE accounts SET balance = balance + 10 WHERE id = 1; (reads then writes)
-- T2: UPDATE accounts SET balance = balance - 5 WHERE id = 1;
-- Result: Second COMMIT fails with serialization_failure
```

**Read-Write Conflicts** (SELECT + UPDATE): NOT DETECTED
```sql
-- T1: SELECT balance FROM accounts WHERE id = 1;
-- T2: UPDATE accounts SET balance = 200 WHERE id = 1; COMMIT;
-- T1: COMMIT;
-- Result: Both succeed (INCORRECT!)
```

### Conflict Resolution Strategy

HeliosDB uses **First-Committer-Wins**:
- The first transaction to COMMIT succeeds
- Later transactions detect conflicts and abort with `serialization_failure`
- Matches PostgreSQL behavior

### Example: Preventing Lost Updates

```sql
-- Account starts with balance = 100

-- Transaction 1 (starts at timestamp 100)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Read set: {accounts:1}
-- Write set: {accounts:1}

-- Transaction 2 (starts at timestamp 101)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
-- Read set: {accounts:1}
-- Write set: {accounts:1}
COMMIT;  --  Succeeds (first committer, timestamp 102)

-- Transaction 1 tries to commit
COMMIT;
-- Fails with:
-- ERROR: could not serialize access due to concurrent update on key: accounts:1
-- DETAIL: Transaction 2 modified accounts:1 (commit_ts: 102) after our snapshot (ts: 100)

-- Final balance = 70 (100 - 30)
-- Transaction 1's +50 update is rejected
```

## Performance Characteristics

### Overhead by Isolation Level

| Operation | READ COMMITTED | REPEATABLE READ | SERIALIZABLE |
|-----------------|----------------|-----------------|--------------|
| BEGIN | <1μs | +50μs (snapshot)| +50μs |
| SELECT | 0% overhead | 0% overhead | 0% (reads not tracked) |
| INSERT | 0% overhead | 0% overhead | +100ns/row |
| UPDATE | 0% overhead | 0% overhead | +150ns/row |
| DELETE | 0% overhead | 0% overhead | +150ns/row |
| COMMIT | <1μs | +10μs | +1-5ms |

### Memory Usage (SERIALIZABLE)

- **Per transaction**: O(R + W) where R = keys read, W = keys written
- **Typical small transaction**: 10-100 keys = ~1-10 KB
- **Global coordinator**: ~10,000 recent commits = ~500 KB - 5 MB

### When to Use Each Isolation Level

#### READ COMMITTED (Default)
- **Use when**: High throughput required, occasional non-repeatable reads acceptable
- **Examples**: Analytics queries, reporting, read-heavy workloads
- **Performance**: Best (0% overhead)

#### REPEATABLE READ
- **Use when**: Consistent snapshot needed, but write conflicts acceptable
- **Examples**: Report generation, data exports, long-running reads
- **Performance**: Near-zero overhead (<1%)

#### SERIALIZABLE
- **Use when**: Strict consistency required, lost updates unacceptable
- **Examples**: Financial transactions, inventory management, concurrent updates
- **Performance**: 2-5% overhead, potential for serialization_failure retries
- **Limitation**: SELECT conflicts not detected

## Error Handling

### Serialization Failures

When a conflict is detected, you'll receive:

```
ERROR: could not serialize access due to concurrent update on key: accounts:1
SQLSTATE: 40001 (serialization_failure)
```

## Monitoring and Debugging

### Log Messages

When using SELECT in SERIALIZABLE:

```
[WARN] CRITICAL GAP: SELECT query in SERIALIZABLE transaction does NOT track reads!
       Read-write conflicts will NOT be detected. This violates serializability.
       Use SELECT FOR UPDATE as a workaround...
```

### Debugging Conflicts

Enable debug logging to see conflict detection.

You'll see detailed logs:

```
[DEBUG] Checking for write conflicts (Serializable isolation)
[DEBUG] Read set size: 3, Write set size: 2
[DEBUG] Checking conflicts for snapshot timestamp 1000
[DEBUG] Found 2 transactions committed since our snapshot
[WARN]  Read-write conflict detected: txn 12345 read key 'accounts:1', but txn 12346 modified it (commit_ts: 1002)
```

## Migration from PostgreSQL

If migrating from PostgreSQL, HeliosDB's MVCC behavior is compatible with one exception:

### Compatible Behavior

- Same isolation levels
- Same conflict detection for UPDATE/DELETE
- Same first-committer-wins semantics
- Same error codes (40001 for serialization_failure)

### Differences

SELECT in SERIALIZABLE doesn't track reads:
- **PostgreSQL**: Full SSI including SELECT
- **HeliosDB**: SELECT not tracked (partial SSI)
- **Workaround**: Use SELECT FOR UPDATE

## References

- **PostgreSQL SSI**: https://www.postgresql.org/docs/current/transaction-iso.html
- **Academic Paper**: "Serializable Snapshot Isolation in PostgreSQL" (Ports & Grittner, 2012)

## Support

For questions or issues related to MVCC:
1. Review log messages for configuration warnings
2. Enable DEBUG logging for conflict detection details
3. Support: [support@heliosdb.com](mailto:support@heliosdb.com)