---
title: "HeliosDB Transactions User Guide"
---

# HeliosDB Transactions User Guide

---

## Table of Contents

1. [Overview](#overview)
2. [Getting Started](#getting-started)
3. [Transaction Basics](#transaction-basics)
4. [Isolation Levels](#isolation-levels)
5. [Savepoints](#savepoints)
6. [Distributed Transactions](#distributed-transactions)
7. [Error Handling and Recovery](#error-handling-and-recovery)
8. [Deadlock Prevention](#deadlock-prevention)
9. [Performance Optimization](#performance-optimization)
10. [Best Practices](#best-practices)
11. [Troubleshooting](#troubleshooting)
12. [API Reference](#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 Full
- Understanding of SQL basics
- Configured database connection

### Quick Start (5 minutes)

#### 1. Basic Transaction

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

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

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

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

```sql
-- Standard commit
COMMIT;

-- Alternative syntax
END;

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

### Rolling Back Transactions

```sql
-- Full rollback
ROLLBACK;

-- Alternative syntax
ABORT;

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

### Implicit vs Explicit Transactions

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

```sql
-- 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.

```sql
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.

```sql
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.

```sql
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.

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

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

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

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

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

```sql
-- 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:

```sql
-- 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:

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

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

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

```sql
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:

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

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

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

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

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

```sql
-- 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:

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

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

```sql
-- 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:

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

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

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

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

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

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

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

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

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

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

#### 4. Too Many Connections

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

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

```sql
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 support@heliosdb.com

**Related Documentation**:
- [MVCC Configuration Guide](https://www.heliosdb.com/docs/full/features/mvcc/mvcc_configuration_guide/)
- [Deadlock Prevention Operations Guide](https://www.heliosdb.com/docs/full/guides/user/deadlock_prevention_operations_guide/)
- [Time-Travel Debugging Guide](https://www.heliosdb.com/docs/full/guides/user/time_travel_debugging_user_guide/)

**License**: Included with all HeliosDB editions.