HeliosDB Transactions User Guide

HeliosDB Transactions User Guide

Version: 1.0 Last Updated: January 4, 2026 Feature Status: Production Ready (100%)

---

Table of Contents

1. Overview
2. Getting Started
3. Transaction Basics
4. Isolation Levels
5. Savepoints
6. Distributed Transactions
7. Error Handling and Recovery
8. Deadlock Prevention
9. Performance Optimization
10. Best Practices
11. Troubleshooting
12. API Reference

---

Overview

HeliosDB provides full ACID-compliant transaction support designed for high-concurrency workloads. Transactions ensure data integrity even under concurrent access, system failures, or network partitions.

ACID Properties

Property	Description	HeliosDB Implementation
Atomicity	All operations succeed or all fail	WAL-based recovery
Consistency	Data always moves from valid state to valid state	Constraint enforcement
Isolation	Transactions don’t interfere with each other	MVCC with multiple isolation levels
Durability	Committed data survives system failures	WAL + checkpointing

Key Features

• Lock-Free MVCC: High concurrency without blocking
• Multiple Isolation Levels: From READ UNCOMMITTED to SERIALIZABLE
• Savepoints: Partial rollback within transactions
• Distributed 2PC: Two-phase commit across nodes
• XA Support: Integration with external transaction coordinators
• Automatic Deadlock Detection: With configurable resolution strategies

Performance Characteristics

Metric	Typical Value	Notes
Transaction Start	<1ms	Lock-free
Single Row Update	1-3ms	Including WAL write
Transaction Commit	3-7ms	With fsync
Deadlock Detection	<50ms	Immediate detection
Recovery Time	<30s	For 10GB database

---

Getting Started

Prerequisites

• HeliosDB v7.0 or later
• Understanding of SQL basics
• Configured database connection

Quick Start (5 minutes)

1. Basic Transaction

-- Start a transaction
BEGIN;

-- Perform operations
INSERT INTO accounts (id, balance) VALUES (1, 1000);
INSERT INTO accounts (id, balance) VALUES (2, 500);

-- Commit to make changes permanent
COMMIT;

2. Transaction with Rollback

-- Start a transaction
BEGIN;

-- Perform operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Oops, wrong amount! Roll back all changes
ROLLBACK;

3. Safe Money Transfer

-- Atomic money transfer
BEGIN;

-- Check balance first
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- If balance >= 100, proceed
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If all succeeded, commit
COMMIT;

---

Transaction Basics

Starting Transactions

-- Standard BEGIN
BEGIN;

-- With explicit transaction keyword
BEGIN TRANSACTION;

-- With isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- With read-only mode
BEGIN TRANSACTION READ ONLY;

-- Combined options
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;

Committing Transactions

-- Standard commit
COMMIT;

-- Alternative syntax
END;

-- With options (rarely needed)
COMMIT AND CHAIN;  -- Immediately starts new transaction

Rolling Back Transactions

-- Full rollback
ROLLBACK;

-- Alternative syntax
ABORT;

-- With chain (starts new transaction)
ROLLBACK AND CHAIN;

Implicit vs Explicit Transactions

-- Implicit: Each statement is its own transaction
INSERT INTO users (name) VALUES ('Alice');  -- Auto-commits

-- Explicit: Multiple statements in one transaction
BEGIN;
    INSERT INTO users (name) VALUES ('Bob');
    INSERT INTO users (name) VALUES ('Charlie');
COMMIT;  -- Both inserts committed together

Read-Only Transactions

-- Read-only transaction (better performance for reads)
BEGIN TRANSACTION READ ONLY;

SELECT * FROM accounts WHERE balance > 1000;
SELECT * FROM transactions WHERE account_id = 1;

COMMIT;

Benefits of read-only transactions:

• No write locks acquired
• Can use read replicas
• Better snapshot consistency
• Slightly lower overhead

---

Isolation Levels

Overview

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Serialization Anomaly
READ UNCOMMITTED	Possible	Possible	Possible	Possible
READ COMMITTED	No	Possible	Possible	Possible
REPEATABLE READ	No	No	Possible*	Possible
SERIALIZABLE	No	No	No	No

*In HeliosDB’s implementation, REPEATABLE READ uses snapshot isolation which prevents phantoms in most cases.

READ UNCOMMITTED

Allows reading uncommitted data (dirty reads). Rarely used.

BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Can see uncommitted changes from other transactions
SELECT * FROM accounts;

COMMIT;

READ COMMITTED (Default)

Each statement sees only committed data. The default and most commonly used level.

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- First query sees committed data as of query start
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000

-- If another transaction commits a change here...

-- Second query sees the new committed data
SELECT balance FROM accounts WHERE id = 1;  -- Might return 900

COMMIT;

Best for: General applications, OLTP workloads, high concurrency

REPEATABLE READ

Transaction sees a consistent snapshot from the first query.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- First query establishes the snapshot
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000

-- Even if another transaction commits a change...

-- This query sees the same snapshot
SELECT balance FROM accounts WHERE id = 1;  -- Still returns 1000

COMMIT;

Best for: Reports, analytics, long-running reads

SERIALIZABLE

Strictest level. Transactions execute as if they were serial.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- All operations checked for conflicts
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- If a conflict is detected...
COMMIT;  -- May fail with serialization_failure

Best for: Financial transactions, inventory management, critical operations

Handling Serialization Failures

-- Retry loop pattern
DO $$
DECLARE
    retry_count INT := 0;
    max_retries INT := 3;
BEGIN
    LOOP
        BEGIN
            -- Start serializable transaction
            BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

            -- Perform operations
            UPDATE accounts SET balance = balance - 100 WHERE id = 1;
            UPDATE accounts SET balance = balance + 100 WHERE id = 2;

            COMMIT;
            EXIT;  -- Success, exit loop

        EXCEPTION
            WHEN serialization_failure THEN
                retry_count := retry_count + 1;
                IF retry_count >= max_retries THEN
                    RAISE EXCEPTION 'Max retries exceeded';
                END IF;
                -- Small delay before retry
                PERFORM pg_sleep(0.1 * retry_count);
        END;
    END LOOP;
END $$;

---

Savepoints

Savepoints allow partial rollback within a transaction.

Creating Savepoints

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 500);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
SAVEPOINT after_first_item;

INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
-- Oops, wrong product!

ROLLBACK TO SAVEPOINT after_first_item;
-- order and first item still there, second item rolled back

INSERT INTO order_items (order_id, product_id) VALUES (1, 201);
-- Correct product added

COMMIT;

Releasing Savepoints

BEGIN;

SAVEPOINT sp1;
INSERT INTO logs (message) VALUES ('Step 1');

SAVEPOINT sp2;
INSERT INTO logs (message) VALUES ('Step 2');

-- Release sp1, merge its changes into parent
RELEASE SAVEPOINT sp1;

-- Now only sp2 can be rolled back to
ROLLBACK TO SAVEPOINT sp2;  -- OK
ROLLBACK TO SAVEPOINT sp1;  -- ERROR: savepoint does not exist

COMMIT;

Nested Savepoints

BEGIN;

SAVEPOINT outer;
    INSERT INTO accounts (id, balance) VALUES (1, 1000);

    SAVEPOINT inner;
        UPDATE accounts SET balance = 900 WHERE id = 1;

        -- Decide to roll back the update only
        ROLLBACK TO SAVEPOINT inner;

    -- Insert still preserved
    SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000

COMMIT;

Savepoint Best Practices

-- Pattern: Try operation, rollback on error
BEGIN;

SAVEPOINT before_risky_operation;

BEGIN
    -- Risky operation
    CALL external_api_update();
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT before_risky_operation;
        RAISE NOTICE 'External update failed, continuing without it';
END;

-- Continue with rest of transaction
UPDATE local_table SET synced = false;

COMMIT;

---

Distributed Transactions

Two-Phase Commit (2PC)

For transactions spanning multiple HeliosDB nodes:

-- Phase 1: Prepare
BEGIN;
UPDATE accounts@node1 SET balance = balance - 100 WHERE id = 1;
UPDATE accounts@node2 SET balance = balance + 100 WHERE id = 2;
PREPARE TRANSACTION 'transfer_001';

-- Phase 2: Commit (on all nodes)
COMMIT PREPARED 'transfer_001';

-- Or rollback if any node fails
ROLLBACK PREPARED 'transfer_001';

XA Transactions

For integration with external transaction coordinators:

-- Start XA transaction
XA START 'xid_12345';

-- Perform operations
INSERT INTO orders (id, total) VALUES (1, 500);

-- End work phase
XA END 'xid_12345';

-- Prepare for commit
XA PREPARE 'xid_12345';

-- Commit (from coordinator)
XA COMMIT 'xid_12345';

-- Or rollback
XA ROLLBACK 'xid_12345';

Monitoring Distributed Transactions

-- List prepared transactions
SELECT * FROM pg_prepared_xacts;

-- Check for stuck transactions
SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
WHERE prepared < NOW() - INTERVAL '5 minutes';

-- Recovery of stuck transactions (admin only)
ROLLBACK PREPARED 'stuck_transaction_id';

---

Error Handling and Recovery

Transaction States

START -> ACTIVE -> FAILED (on error)
                -> COMMITTED (on commit)
                -> ABORTED (on rollback)

Error Handling Patterns

Basic Try-Catch

DO $$
BEGIN
    BEGIN TRANSACTION;

    -- Operations that might fail
    INSERT INTO orders VALUES (...);
    UPDATE inventory SET qty = qty - 1 WHERE ...;

    COMMIT;

EXCEPTION
    WHEN unique_violation THEN
        ROLLBACK;
        RAISE NOTICE 'Duplicate order detected';
    WHEN check_violation THEN
        ROLLBACK;
        RAISE NOTICE 'Invalid data';
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END $$;

Automatic Retry

CREATE OR REPLACE FUNCTION safe_transfer(
    from_account INT,
    to_account INT,
    amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INT := 0;
    max_retries INT := 3;
BEGIN
    LOOP
        BEGIN
            -- Serializable for safety
            SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

            -- Check and transfer
            IF (SELECT balance FROM accounts WHERE id = from_account) >= amount THEN
                UPDATE accounts SET balance = balance - amount WHERE id = from_account;
                UPDATE accounts SET balance = balance + amount WHERE id = to_account;
                RETURN TRUE;
            ELSE
                RETURN FALSE;  -- Insufficient funds
            END IF;

        EXCEPTION
            WHEN serialization_failure OR deadlock_detected THEN
                retry_count := retry_count + 1;
                IF retry_count >= max_retries THEN
                    RAISE EXCEPTION 'Transfer failed after % retries', max_retries;
                END IF;
                -- Wait before retry
                PERFORM pg_sleep(0.1 * random());
        END;
    END LOOP;
END $$ LANGUAGE plpgsql;

Recovery After Crash

HeliosDB automatically recovers from crashes using WAL:

-- Check recovery status after restart
SELECT * FROM pg_stat_recovery;

-- Verify data consistency
SELECT COUNT(*) FROM accounts;

-- Check for incomplete transactions
SELECT * FROM pg_stat_activity
WHERE state = 'idle in transaction';

---

Deadlock Prevention

How Deadlocks Occur

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks row 1
-- Waits for row 2...

-- Transaction B (concurrent)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- Locks row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- Waits for row 1
-- DEADLOCK!

Prevention Strategies

1. Consistent Lock Ordering

-- Always lock in the same order (e.g., by ID ascending)
BEGIN;

-- Lock both accounts in consistent order
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

-- Now safe to update in any order
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

2. Use NOWAIT

BEGIN;

-- Fail immediately if can't acquire lock
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- If we get here, we have the lock
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

3. Use SKIP LOCKED

-- Process available rows, skip locked ones
BEGIN;

-- Get unlocked work items
SELECT * FROM work_queue
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- Process them...

COMMIT;

4. Set Lock Timeout

-- Fail if can't acquire lock within timeout
SET lock_timeout = '5s';

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Deadlock Detection

HeliosDB automatically detects deadlocks and aborts one transaction:

-- View deadlock events
SELECT * FROM pg_stat_database
WHERE deadlocks > 0;

-- Enable detailed deadlock logging
SET log_lock_waits = ON;
SET deadlock_timeout = '1s';

---

Performance Optimization

Reducing Lock Contention

-- BAD: Long transaction with many locks
BEGIN;
SELECT * FROM large_table FOR UPDATE;  -- Locks all rows
-- ... long processing ...
COMMIT;

-- GOOD: Short transactions with minimal locks
BEGIN;
SELECT id FROM large_table WHERE status = 'pending' LIMIT 100 FOR UPDATE;
-- Quick processing
UPDATE large_table SET status = 'processing' WHERE id IN (...);
COMMIT;

Batch Operations

-- BAD: Many small transactions
FOR i IN 1..10000 LOOP
    BEGIN;
    INSERT INTO events VALUES (i, ...);
    COMMIT;
END LOOP;

-- GOOD: Batch in single transaction
BEGIN;
INSERT INTO events
SELECT generate_series(1, 10000), ...;
COMMIT;

Advisory Locks

For application-level locking without row locks:

-- Acquire advisory lock (doesn't lock any rows)
SELECT pg_advisory_lock(12345);

-- Do work...

-- Release lock
SELECT pg_advisory_unlock(12345);

-- Or use try variant (non-blocking)
SELECT pg_try_advisory_lock(12345);  -- Returns true/false

Connection Pooling Considerations

-- Reset session state after returning to pool
RESET ALL;
DISCARD ALL;

-- Or use RESET for specific settings
RESET transaction_isolation;
RESET lock_timeout;

---

Best Practices

1. Keep Transactions Short

-- BAD: Long-running transaction
BEGIN;
SELECT * FROM orders WHERE date > '2020-01-01';  -- 1M rows
-- ... process in application for 30 seconds ...
COMMIT;

-- GOOD: Process in batches
DO $$
DECLARE
    batch_size INT := 1000;
    offset_val INT := 0;
BEGIN
    LOOP
        -- Short transaction per batch
        BEGIN;
        UPDATE orders
        SET processed = true
        WHERE id IN (
            SELECT id FROM orders
            WHERE processed = false
            ORDER BY id
            LIMIT batch_size
        );
        COMMIT;

        offset_val := offset_val + batch_size;
        EXIT WHEN NOT FOUND;
    END LOOP;
END $$;

2. Use Appropriate Isolation Level

-- For most queries: READ COMMITTED (default)
SELECT * FROM products WHERE category = 'electronics';

-- For reports: REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders WHERE date = CURRENT_DATE;
SELECT SUM(total) FROM orders WHERE date = CURRENT_DATE;
COMMIT;

-- For critical operations: SERIALIZABLE
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Check inventory, create order, decrement inventory
COMMIT;

3. Handle Errors Properly

-- Always handle potential errors
BEGIN;
    -- Your operations
    INSERT INTO orders VALUES (...);

    -- Explicit commit
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- Log the error
        RAISE NOTICE 'Error: %', SQLERRM;
        -- Clean up
        ROLLBACK;
        -- Re-raise or handle
        RAISE;
END;

4. Monitor Transaction Health

-- Check for long-running transactions
SELECT
    pid,
    usename,
    state,
    query,
    age(now(), xact_start) AS transaction_age
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start < NOW() - INTERVAL '5 minutes'
ORDER BY xact_start;

-- Check lock contention
SELECT
    blocked.pid AS blocked_pid,
    blocking.pid AS blocking_pid,
    blocked.query AS blocked_query
FROM pg_locks blocked
JOIN pg_locks blocking ON blocking.locktype = blocked.locktype
    AND blocking.relation = blocked.relation
    AND blocking.pid != blocked.pid
WHERE NOT blocked.granted;

5. Use Explicit Locking Wisely

-- FOR UPDATE: Write lock (blocks other FOR UPDATE and writes)
SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE;

-- FOR SHARE: Read lock (blocks writes but not other reads)
SELECT * FROM inventory WHERE product_id = 1 FOR SHARE;

-- FOR KEY SHARE: Minimal lock (blocks DELETEs but not updates)
SELECT * FROM orders WHERE customer_id = 1 FOR KEY SHARE;

-- FOR NO KEY UPDATE: Allows key reads (doesn't block FOR KEY SHARE)
SELECT * FROM orders WHERE id = 1 FOR NO KEY UPDATE;

---

Troubleshooting

Common Issues

1. “idle in transaction” Sessions

-- Find idle transactions
SELECT pid, usename, state, query, age(now(), xact_start)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

-- Terminate if stuck
SELECT pg_terminate_backend(pid);

2. Lock Timeout Errors

-- Increase timeout if needed
SET lock_timeout = '30s';

-- Or check what's blocking
SELECT blocked.pid, blocked.query, blocking.pid AS blocking_pid
FROM pg_locks blocked
JOIN pg_locks blocking ON blocking.relation = blocked.relation
WHERE NOT blocked.granted;

3. Serialization Failures

-- Add retry logic (see Error Handling section)
-- Or consider lowering isolation level if appropriate

4. Too Many Connections

-- Check connection count
SELECT count(*) FROM pg_stat_activity;

-- Use connection pooling (PgBouncer, etc.)
-- Set per-connection limits
ALTER USER app_user CONNECTION LIMIT 50;

Diagnostic Queries

-- Current transactions
SELECT pid, usename, state, query, xact_start
FROM pg_stat_activity
WHERE state != 'idle';

-- Lock statistics
SELECT * FROM pg_stat_database;

-- Current locks
SELECT relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation IS NOT NULL;

-- Transaction statistics
SELECT xact_commit, xact_rollback, deadlocks
FROM pg_stat_database
WHERE datname = current_database();

---

API Reference

Transaction Control Statements

Statement	Description
BEGIN / START TRANSACTION	Start a new transaction
COMMIT / END	Commit current transaction
ROLLBACK / ABORT	Roll back current transaction
SAVEPOINT name	Create a savepoint
ROLLBACK TO SAVEPOINT name	Roll back to savepoint
RELEASE SAVEPOINT name	Release (remove) savepoint

Transaction Options

BEGIN TRANSACTION
    [ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}]
    [READ WRITE | READ ONLY]
    [DEFERRABLE | NOT DEFERRABLE];

Lock Modes

Mode	Description	Conflicts With
FOR UPDATE	Exclusive write lock	All
FOR NO KEY UPDATE	Write lock, allows key reads	FOR UPDATE, FOR SHARE
FOR SHARE	Shared read lock	FOR UPDATE, FOR NO KEY UPDATE
FOR KEY SHARE	Minimal shared lock	FOR UPDATE

Session Variables

Variable	Description	Default
transaction_isolation	Default isolation level	read committed
lock_timeout	Max wait for locks	0 (infinite)
statement_timeout	Max statement runtime	0 (infinite)
idle_in_transaction_session_timeout	Max idle time	0 (infinite)

---

Support: For issues or questions, contact transactions-support@heliosdb.com

Related Documentation:

• MVCC Configuration Guide
• Deadlock Prevention Operations Guide
• Time-Travel Debugging Guide

License: Included with all HeliosDB editions.

Version: HeliosDB v7.0+