Skip to content
HeliosDB HeliosDB Full

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 LevelSnapshot Created?Reads Tracked?Writes Tracked?Conflict DetectionOverhead
READ UNCOMMITTED❌ No❌ No❌ NoNone~0%
READ COMMITTED❌ No❌ No❌ NoNone~0%
REPEATABLE READYes❌ No❌ NoNone<1%
SERIALIZABLEYes⚠ PartialYesFull2-5%

Usage Examples

READ COMMITTED (Default)

-- 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

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

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

-- Transaction 1 (Connection 1)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 100
-- ⚠ 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

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

-- 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

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):

-- 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):

-- 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

-- 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

-- 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

OperationREAD COMMITTEDREPEATABLE READSERIALIZABLE
BEGIN<1μs+50μs (snapshot)+50μs
SELECT0% overhead0% overhead⚠ 0%
INSERT0% overhead0% overhead+100ns/row
UPDATE0% overhead0% overhead+150ns/row
DELETE0% overhead0% 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

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

Version History

  • v5.4.0: Initial MVCC SSI implementation
  • v5.4.1: Runtime warnings for known limitations added