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.

⚠ Critical Configuration Requirements

1. Shared GlobalTransactionCoordinator (REQUIRED)

For SERIALIZABLE isolation to work correctly across connections, you MUST share a single GlobalTransactionCoordinator instance among all PostgresHandler instances.

❌ INCORRECT (Per-Connection Coordinator)

use heliosdb_protocols::postgres::PostgresHandler;
use tokio::net::TcpListener;

#[tokio::main]
async fn main() {
    let listener = TcpListener::bind("127.0.0.1:5432").await.unwrap();

    loop {
        let (stream, _) = listener.accept().await.unwrap();

        // ❌ WRONG: Each handler gets its own coordinator!
        let handler = PostgresHandler::new(stream);
        // This will show a WARNING log

        tokio::spawn(async move {
            handler.run().await.unwrap();
        });
    }
}

Problem: Each connection has an isolated coordinator, so cross-connection conflicts are never detected. Two transactions on different connections can both modify the same row and both commit successfully (violating serializability).

CORRECT (Shared Coordinator)

use heliosdb_protocols::postgres::{PostgresHandler, GlobalTransactionCoordinator};
use std::sync::Arc;
use tokio::net::TcpListener;

#[tokio::main]
async fn main() {
    let listener = TcpListener::bind("127.0.0.1:5432").await.unwrap();

    //  Create ONE coordinator for the entire server
    let coordinator = Arc::new(GlobalTransactionCoordinator::new());

    loop {
        let (stream, _) = listener.accept().await.unwrap();

        //  CORRECT: Share the coordinator with all handlers
        let handler = PostgresHandler::new(stream)
            .with_global_txn_coordinator(Arc::clone(&coordinator));
        // This will show a SUCCESS log

        tokio::spawn(async move {
            handler.run().await.unwrap();
        });
    }
}

Result: All connections share the same coordinator, enabling proper cross-connection conflict detection.

2. Complete Example with Storage

use heliosdb_protocols::postgres::{PostgresHandler, GlobalTransactionCoordinator};
use heliosdb_storage::LsmStorageEngine;
use std::sync::Arc;
use tokio::net::TcpListener;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Initialize storage engine
    let storage = Arc::new(LsmStorageEngine::new("/tmp/heliosdb_data")?);

    // Create shared global transaction coordinator (CRITICAL!)
    let coordinator = Arc::new(GlobalTransactionCoordinator::new());

    // Bind server
    let listener = TcpListener::bind("127.0.0.1:5432").await?;
    println!("HeliosDB listening on 127.0.0.1:5432");

    // Accept connections
    loop {
        let (stream, addr) = listener.accept().await?;
        println!("New connection from {}", addr);

        // Clone Arc references for this connection
        let storage_clone = Arc::clone(&storage);
        let coordinator_clone = Arc::clone(&coordinator);

        // Create handler with shared components
        let handler = PostgresHandler::new(stream)
            .with_storage(storage_clone)
            .with_global_txn_coordinator(coordinator_clone);

        // Spawn connection handler
        tokio::spawn(async move {
            if let Err(e) = handler.run().await {
                eprintln!("Connection error: {}", e);
            }
        });
    }
}

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)

-- 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 (Gap #1)

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

Option 1: Use SELECT FOR UPDATE (Recommended)

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 write_set

Why This Limitation Exists

The execute_select method uses a high-level query executor abstraction (heliosdb_compute::QueryExecutor) that doesn’t provide key-level visibility. Fixing this requires:

1. Parser changes: Expose predicates for SELECT queries (like UPDATE/DELETE)
2. Storage-backed execution: Direct storage access with key tracking
3. Query executor instrumentation: Add read tracking callbacks to compute layer

Status: Mitigation (runtime warnings) implemented. Full fix planned for Phase 2.

See: docs/MVCC_SSI_GAP_ANALYSIS.md for detailed analysis.

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 (Gap #1)

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

Operation	READ COMMITTED	REPEATABLE READ	SERIALIZABLE
BEGIN	<1μs	+50μs (snapshot)	+50μs
SELECT	0% overhead	0% overhead	⚠ 0% (Gap #1)
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 (Gap #1)

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)

Recommended Handling:

use heliosdb_protocols::postgres::PgError;

async fn transfer_money(from: i64, to: i64, amount: i64) -> Result<(), Box<dyn std::error::Error>> {
    let mut retries = 0;
    const MAX_RETRIES: u32 = 3;

    loop {
        match attempt_transfer(from, to, amount).await {
            Ok(_) => return Ok(()),
            Err(e) if is_serialization_failure(&e) && retries < MAX_RETRIES => {
                retries += 1;
                // Exponential backoff
                tokio::time::sleep(Duration::from_millis(10 * 2_u64.pow(retries))).await;
                continue;
            }
            Err(e) => return Err(e),
        }
    }
}

fn is_serialization_failure(error: &PgError) -> bool {
    error.sql_state() == SqlState::SerializationFailure
}

Monitoring and Debugging

Log Messages

When properly configured, you’ll see:

[INFO]  Using shared GlobalTransactionCoordinator (correct setup for cross-connection serializability)
[INFO] Registered transaction 12345 with global coordinator (start_ts: 1000)
[INFO] Registered commit for transaction 12345 with global coordinator (commit_ts: 1005)

When misconfigured:

[WARN] ⚠ CRITICAL: Creating per-connection GlobalTransactionCoordinator.
       This BREAKS cross-connection serializability! For SERIALIZABLE isolation to work
       correctly across connections, you MUST create a shared coordinator...

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:

use tracing_subscriber;

tracing_subscriber::fmt()
    .with_max_level(tracing::Level::DEBUG)
    .init();

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)

Testing

Unit Test Example

#[tokio::test]
async fn test_cross_connection_conflict_detection() {
    use heliosdb_protocols::postgres::{PostgresHandler, GlobalTransactionCoordinator};
    use std::sync::Arc;

    // Create shared coordinator
    let coordinator = Arc::new(GlobalTransactionCoordinator::new());

    // Create two handlers sharing the coordinator
    let handler1 = PostgresHandler::new(stream1)
        .with_global_txn_coordinator(Arc::clone(&coordinator));
    let handler2 = PostgresHandler::new(stream2)
        .with_global_txn_coordinator(Arc::clone(&coordinator));

    // Transaction 1: Update account 123
    handler1.execute("BEGIN SERIALIZABLE").await?;
    handler1.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 123").await?;

    // Transaction 2: Also update account 123
    handler2.execute("BEGIN SERIALIZABLE").await?;
    handler2.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 123").await?;
    handler2.execute("COMMIT").await?;  //  First commit succeeds

    // Transaction 1: Try to commit
    let result = handler1.execute("COMMIT").await;
    assert!(result.is_err());  //  Second commit fails
    assert_eq!(result.unwrap_err().sql_state(), SqlState::SerializationFailure);
}

Migration from PostgreSQL

If migrating from PostgreSQL, HeliosDB’s MVCC behavior is compatible with one exception (Gap #1):

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 (Gap #1)

• PostgreSQL: Full SSI including SELECT
• HeliosDB: SELECT not tracked (partial SSI)
• Workaround: Use SELECT FOR UPDATE

References

• Gap Analysis: docs/MVCC_SSI_GAP_ANALYSIS.md - Detailed analysis of known limitations
• Implementation Details:
  • docs/TRANSACTION_SNAPSHOT_LIFECYCLE.md - Week 3 implementation
  • docs/WRITE_CONFLICT_DETECTION.md - Week 4 implementation
  • docs/WEEK6_GRANULAR_TRACKING_INTEGRATION.md - Week 6 implementation
• 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. Check the gap analysis document for known limitations
2. Review log messages for configuration warnings
3. Enable DEBUG logging for conflict detection details
4. File issues at: https://github.com/heliosdb/heliosdb/issues

Version History

• v5.4.0: Initial MVCC SSI implementation (Weeks 1-6)
• v5.4.1: Gap mitigations added (runtime warnings)
• v5.5.0: (Planned) Full SELECT tracking support