Transactions Architecture

This document provides a comprehensive overview of ekoDB's transaction support, including ACID guarantees, isolation levels, savepoints, WAL integration, and both function-based and REST API transaction management.

Overview

ekoDB transactions are buffered and guarantee atomicity (all-or-nothing), isolation (enforced, configurable), and durability (WAL). They provide:

• Atomicity - each statement stages into the transaction; the whole set applies atomically at commit, and a failure or rollback discards the staged writes (nothing partially applied)
• Isolation - enforced via optimistic concurrency over per-record version stamps (MVCC), configurable per transaction up to Serializable
• Durability - committed transactions are persisted via the WAL
• Deferred Replication - ripple propagation is deferred to commit and respects per-operation bypass_ripple
• Nested Transactions - Savepoints for partial rollback (rewind the buffer)
• WAL Audit Trail - All transaction events logged for recovery and auditing
• Dual Interface - Both function-based and REST API transaction management
• Automatic Cleanup - Expired transaction management (discards the buffer)

Isolation is enforced

ekoDB enforces isolation between concurrent transactions via optimistic concurrency on per-record version stamps (MVCC). A transaction's staged writes are invisible to others until commit (no dirty reads) and visible to its own reads that carry the transaction id (read-your-writes); at commit, ekoDB validates that the records it read or wrote are unchanged and otherwise rejects the commit as a retryable conflict (HTTP 409) — nothing is applied. No locks are held across statements. See Isolation Levels for what each level validates.

Table of Contents

1. Transaction Types
2. Isolation Levels
3. Savepoints (Nested Transactions)
4. Function-Based Transactions
5. REST API Transactions
6. WAL Integration
7. Usage Examples
8. Best Practices

Transaction Types

1. Function-Based Transactions

Transactions embedded within callable functions with automatic lifecycle management.

Features:

• Automatic begin/commit/rollback
• WAL logging built-in
• Savepoint support via Function enum
• Configurable isolation levels

2. REST API Transactions

Direct transaction management via HTTP endpoints:

POST   /api/transactions                              # Begin
GET    /api/transactions/{id}                         # Status
POST   /api/transactions/{id}/commit                  # Commit
POST   /api/transactions/{id}/rollback                # Rollback
POST   /api/transactions/{id}/savepoints              # Create savepoint
POST   /api/transactions/{id}/savepoints/{name}/rollback
DELETE /api/transactions/{id}/savepoints/{name}

Features:

• Manual transaction control
• Long-running transaction support
• Timeout management
• Active transaction tracking

Isolation Levels

The four levels can be passed to TransactionConfig and the REST API. The level selects how much ekoDB validates at commit under its optimistic-concurrency model (version-stamp checks over per-record stamps; no locks held across statements). It is resolved per transaction as the value you pass, else the server-wide default_isolation_level setting (ReadUncommitted out of the box). Official client methods leave the level unset when it is omitted, and the server default is runtime-tunable via /api/config.

ekoDB validates at commit rather than taking per-statement read snapshots, so the levels map to validation strength (which conflicts abort the commit), not classic lock-based read semantics.

ReadUncommitted

• No commit validation; concurrent commits are last-writer-wins. The zero-cost default fast path.
• Use case: Analytics, non-critical / high-throughput writes

ReadCommitted

• Lost-update prevention — the commit is rejected if a record this transaction wrote was changed by another committed transaction since it was observed.
• Use case: Most general-purpose transactions

RepeatableRead

• Adds read-set validation on top of ReadCommitted: the commit is rejected if a record this transaction read (not only one it wrote) changed before commit. Prevents non-repeatable reads / read skew. Phantoms are still possible.
• Use case: Reads that must stay valid through commit (financial calculations, reporting)

Serializable

• Strongest — adds collection-membership validation on top of RepeatableRead: the commit is rejected if the membership of any collection this transaction queried changed before commit (an insert / delete / update that shifts a predicate's result set). Prevents phantoms, and with them write skew; the committed history is serializable.
• Use case: Banking, critical financial operations

Savepoints (Nested Transactions)

Savepoints allow partial rollback within a transaction:

REST API

# Create savepoint
POST /api/transactions/{id}/savepoints
{"name": "before_debit"}

# Rollback to savepoint
POST /api/transactions/{id}/savepoints/before_debit/rollback

# Release savepoint
DELETE /api/transactions/{id}/savepoints/before_debit

How It Works

1. Create - Marks a restore point within the transaction
2. Rollback - Reverts all operations performed after the savepoint
3. Release - Releases the savepoint when no longer needed

Benefits:

• Partial rollback without full transaction abort
• Multi-stage error recovery
• Complex workflow support

Function-Based Transactions

Creating a Transactional Function

POST /api/functions
{
  "label": "create_order_with_audit",
  "name": "Create order with audit",
  "description": "Create an order and its audit record atomically",
  "transaction_config": {
    "enabled": true,
    "auto_rollback": true,
    "isolation_level": "Serializable"
  },
  "parameters": {
    "order_id": {"required": true},
    "customer_id": {"required": true}
  },
  "functions": [
    {
      "type": "Insert",
      "collection": "orders",
      "record": {
        "id": {"type": "Parameter", "name": "order_id"},
        "customer_id": {"type": "Parameter", "name": "customer_id"},
        "status": "pending"
      }
    },
    {
      "type": "CreateSavepoint",
      "name": "after_order"
    },
    {
      "type": "Insert",
      "collection": "order_audit",
      "record": {
        "order_id": {"type": "Parameter", "name": "order_id"},
        "event": "created"
      }
    }
  ]
}

Executing the Function

POST /api/functions/create_order_with_audit
{
  "order_id": "ORDER001",
  "customer_id": "CUSTOMER001"
}

Automatic Behavior

• Begin - Logged to WAL on function start
• Commit - Automatic if all functions succeed
• Rollback - Any error discards all staged writes. auto_rollback remains a required compatibility field, but is not a runtime behavior switch.
• WAL Logging - All lifecycle events recorded

Transactional Update and Delete filters support the same text "{{parameter}}" substitution as ordinary filtered function stages. Use the structural { "type": "Parameter", "name": "..." } form in JSON-valued stage fields such as Insert.record and Update.updates, especially when the parameter's original type must be preserved.

REST API Transactions

Full Transaction Lifecycle

1. Begin Transaction

POST /api/transactions
Content-Type: application/json

{
  "isolation_level": "Serializable",
  "timeout_seconds": 600
}

# Response
{
  "transaction_id": "tx-abc123",
  "isolation_level": "Serializable",
  "created_at": 1699814400
}

2. Perform Operations

# Use standard CRUD APIs
PUT /api/update/accounts/account1?transaction_id=tx-abc123
{"balance": 900}

PUT /api/update/accounts/account2?transaction_id=tx-abc123
{"balance": 1100}

Note: Pass ?transaction_id=<id> on each statement (insert/update/delete/ batch and point reads) to stage it into the transaction. Staged writes are applied atomically at commit; reads that carry the id see the transaction's own staged writes (read-your-writes). Reads without the id see only committed data.

3. Create Savepoint

POST /api/transactions/tx-abc123/savepoints
{"name": "checkpoint1"}

# Response
{
  "status": "created",
  "transaction_id": "tx-abc123",
  "savepoint_name": "checkpoint1"
}

4. Commit or Rollback

# Commit
POST /api/transactions/tx-abc123/commit

# Response
{
  "status": "committed",
  "transaction_id": "tx-abc123",
  "operations_count": 5
}

# OR Rollback
POST /api/transactions/tx-abc123/rollback

# Response
{
  "status": "rolled_back",
  "transaction_id": "tx-abc123",
  "operations_rolled_back": 5
}

5. Check Status

GET /api/transactions/tx-abc123

# Response
{
  "transaction_id": "tx-abc123",
  "state": "Active",
  "isolation_level": "Serializable",
  "operations_count": 5,
  "savepoints_count": 2,
  "created_at": 1699814400,
  "last_activity": 1699814450,
  "timeout_seconds": 600
}

Transaction Manager

The transaction manager provides:

• In-memory tracking of active transactions
• Automatic cleanup of expired transactions (every 60 seconds)
• Thread-safe operation
• Timeout management

WAL Integration

Transaction Lifecycle Events

All transaction events are logged to the Write-Ahead Log, including:

• Transaction lifecycle: Begin, Commit, Rollback
• Savepoint events: Create, Rollback, Release
• Timestamps: For audit trail and recovery
• Operation counts: For tracking and monitoring

Replay Behavior

Transaction events are audit logs only:

• Not replayed on WAL recovery
• Provide complete audit trail
• Enable transaction history queries
• Support compliance requirements

Usage Examples

Example 1: Simple Function Transaction

// Create function
POST /api/functions
{
  "label": "create_user_with_profile",
  "name": "Create user with profile",
  "parameters": {
    "email": {"required": true},
    "name": {"required": true},
    "bio": {"required": true}
  },
  "transaction_config": {
    "enabled": true,
    "auto_rollback": true,
    "isolation_level": "ReadCommitted"
  },
  "functions": [
    {
      "type": "Insert",
      "collection": "users",
      "record": {
        "email": {"type": "Parameter", "name": "email"},
        "name": {"type": "Parameter", "name": "name"}
      }
    },
    {
      "type": "Insert",
      "collection": "profiles",
      "record": {
        "user_email": {"type": "Parameter", "name": "email"},
        "bio": {"type": "Parameter", "name": "bio"}
      }
    }
  ]
}

// Call function
POST /api/functions/create_user_with_profile
{
  "email": "user@example.com",
  "name": "John Doe",
  "bio": "Software Engineer"
}

Result: Both records created atomically or neither created if error occurs.

Example 2: Multi-Stage Transfer with Savepoints

# 1. Begin transaction
POST /api/transactions
{"isolation_level": "Serializable", "timeout_seconds": 600}
# -> {"transaction_id": "tx-001", ...}

# 2. Create initial savepoint
POST /api/transactions/tx-001/savepoints
{"name": "start"}

# 3. Debit source account
PUT /api/update/accounts/ACC001?transaction_id=tx-001
{"balance": 900}

# 4. Create savepoint after debit
POST /api/transactions/tx-001/savepoints
{"name": "after_debit"}

# 5. Credit destination account
PUT /api/update/accounts/ACC002?transaction_id=tx-001
{"balance": 1100}

# 6. If credit fails, rollback to after_debit and retry
POST /api/transactions/tx-001/savepoints/after_debit/rollback
# Retry credit operation...

# 7. Commit transaction
POST /api/transactions/tx-001/commit

Example 3: Long-Running Workflow

// Function with multiple checkpoints
{
  "label": "order_processing",
  "name": "Order processing",
  "transaction_config": {
    "enabled": true,
    "auto_rollback": true,
    "isolation_level": "RepeatableRead"
  },
  "functions": [
    {"type": "CreateSavepoint", "name": "order_created"},
    {"type": "Insert", "collection": "orders", "record": {"id": "ORDER001", "status": "pending"}},

    {"type": "CreateSavepoint", "name": "inventory_reserved"},
    {"type": "Update", "collection": "inventory", "filter": {"type": "Condition", "content": {"field": "sku", "operator": "Eq", "value": "SKU001"}}, "updates": {"reserved": true}},

    {"type": "CreateSavepoint", "name": "payment_processed"},
    {"type": "Insert", "collection": "payments", "record": {"order_id": "ORDER001", "amount": 100}},

    {"type": "Update", "collection": "orders", "filter": {"type": "Condition", "content": {"field": "id", "operator": "Eq", "value": "ORDER001"}}, "updates": {"status": "completed"}}
  ]
}

Best Practices

1. Declare Intended Isolation Level

The table below describes which level to declare for a given workload. Isolation is enforced (see Isolation Levels): a higher level applies stricter conflict validation, and conflicting concurrent transactions are rejected with a 409 rather than committing inconsistent state.

Use Case	Declare	Why
Analytics/Reporting	ReadUncommitted	Performance over consistency
General CRUD	ReadCommitted	Prevents lost updates
Financial Calculations	RepeatableRead	Consistent reads
Banking/Critical	Serializable	Maximum safety

2. Use Savepoints for Multi-Stage Operations

# The caller creates a recovery point before a group of staged operations.
POST /api/transactions/tx-001/savepoints
{"name": "before_optional_changes"}

PUT /api/update/orders/ORDER001?transaction_id=tx-001
{"note": "optional enrichment"}

# If application-level validation rejects that group, the caller rewinds it.
POST /api/transactions/tx-001/savepoints/before_optional_changes/rollback

A stored function aborts immediately on a stage error and discards its entire buffer, so a later rollback stage cannot recover from that error. Function savepoints are useful only for explicit, deterministic rewinds in the declared stage sequence; use the REST transaction API when the caller decides whether to rewind after inspecting an operation result.

3. Set Appropriate Timeouts

// Short transaction
{"timeout_seconds": 60}  // 1 minute

// Long-running workflow
{"timeout_seconds": 1800}  // 30 minutes

// Critical operation
{"timeout_seconds": 300}  // 5 minutes (default)

4. Handle Errors Gracefully

// Function-based: staged writes are always discarded on failure.
// auto_rollback is retained as a required compatibility field.
{
  "transaction_config": {
    "enabled": true,
    "auto_rollback": true
  }
}

// API-based: Manual error handling
try {
  // Perform operations
  POST /api/transactions/{id}/commit
} catch (error) {
  POST /api/transactions/{id}/rollback
}

5. Monitor Active Transactions

# List all active transactions
GET /api/transactions

# Check specific transaction
GET /api/transactions/{id}

6. Use WAL for Auditing

Transaction events in WAL provide:

• Complete transaction history
• Audit trail for compliance
• Debugging failed transactions
• Performance analysis

Performance Characteristics

Transaction Overhead

Transactions buffer their writes in memory and apply them at commit, so overhead is the cost of buffering plus WAL logging of begin/commit/rollback events plus commit-time version-stamp validation. Concurrency control is optimistic — no locks are held across statements — so the per-level cost is just how much the commit validates:

Cost component	Overhead	Notes
Write buffering	Low	In-memory staging until commit
WAL begin/commit/rollback events	Low	Async, batched, non-blocking
Commit validation	Per-level	None at ReadUncommitted; write-set at ReadCommitted; read+write-set at RepeatableRead; read+write-set plus collection membership at Serializable

Savepoint Overhead

• Create: Snapshots the in-memory buffer
• Rollback: Restores the buffer snapshot (no live-store compensation)
• Release: Sub-microsecond

WAL Logging

• Transaction events: Async, non-blocking
• Batched writes for efficiency
• Minimal impact on transaction performance

Common Use Cases

1. Real-Time Financial Systems

• Strong consistency for account updates
• Real-time WebSocket updates for UI
• Comprehensive audit logging

2. E-Commerce Platforms

• Order creation with inventory reservation (savepoints)
• Payment processing with rollback capability
• Multi-step checkout flows with error recovery

3. Multi-Tenant SaaS

• Tenant data isolation with transactions
• Batch operations with partial rollback
• Audit trail for compliance

4. Workflow Orchestration

• Long-running workflows with checkpoints
• Partial rollback on step failure
• State persistence via WAL

Architecture Diagram

┌─────────────────────────────────────────────────────────┐
│                    Transaction Layer                     │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  ┌─────────────────┐         ┌──────────────────┐      │
│  │  Function-Based │         │   REST API       │      │
│  │  Transactions   │         │   Transactions   │      │
│  └────────┬────────┘         └────────┬─────────┘      │
│           │                           │                 │
│           └───────────┬───────────────┘                 │
│                       │                                 │
│           ┌───────────▼──────────────┐                  │
│           │  TransactionManager      │                  │
│           │  - Active tracking       │                  │
│           │  - Timeout management    │                  │
│           │  - Savepoint handling    │                  │
│           └───────────┬──────────────┘                  │
│                       │                                 │
│           ┌───────────▼──────────────┐                  │
│           │  Transaction             │                  │
│           │  - Operations tracking   │                  │
│           │  - Savepoint management  │                  │
│           │  - Isolation level       │                  │
│           └───────────┬──────────────┘                  │
│                       │                                 │
│                       ▼                                 │
│           ┌───────────────────────────┐                 │
│           │  WAL (Write-Ahead Log)    │                 │
│           │  - Transaction events     │                 │
│           │  - Savepoint events       │                 │
│           │  - Audit trail            │                 │
│           └───────────────────────────┘                 │
│                                                          │
└─────────────────────────────────────────────────────────┘

Transaction State Machine

                    ┌─────────┐
                    │  BEGIN  │
                    └────┬────┘
                         │
                         ▼
              ┌──────────────────┐
              │     ACTIVE       │◄─────┐
              └──────┬───┬───────┘      │
                     │   │              │
          ┌──────────┘   └──────────┐   │
          │                         │   │
          ▼                         ▼   │
    ┌──────────┐            ┌──────────────┐
    │ COMMIT   │            │  SAVEPOINT   │
    └──────────┘            │  ROLLBACK    │
                            └──────┬───────┘
                                   │
                                   └───────┘

Related Documentation

• White Paper - ACID compliance and architecture overview
• Functions - Reusable stored procedures