Skip to main content

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 provides full ACID transaction support with:

  • Multiple Isolation Levels - ReadUncommitted, ReadCommitted, RepeatableRead, Serializable
  • Nested Transactions - Savepoints for partial rollback
  • 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

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​

ekoDB supports four standard SQL isolation levels:

ReadUncommitted​

  • Dirty reads allowed
  • No read locks
  • Highest performance, lowest consistency
  • Use case: Analytics, non-critical reads

ReadCommitted (Default)​

  • No dirty reads
  • Read locks released after read
  • Good balance of performance and consistency
  • Use case: Most general-purpose transactions

RepeatableRead​

  • Consistent reads within transaction
  • Read locks held until commit
  • Prevents non-repeatable reads
  • Use case: Financial calculations, reporting

Serializable​

  • Full MVCC with conflict detection
  • Snapshot isolation
  • Highest consistency
  • 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 - Records current operation index
  2. Rollback - Reverts operations after savepoint
  3. Release - Removes savepoint (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": "transfer_funds",
  "description": "Transfer money between accounts",
  "transaction_config": {
    "enabled": true,
    "auto_rollback": true,
    "isolation_level": "Serializable"
  },
  "functions": [
    {
      "type": "Update",
      "collection": "accounts",
      "filter": {"field": "account_id", "operator": "Equals", "value": "{{from_account}}"},
      "update": {"$decrement": {"balance": "{{amount}"}}
    },
    {
      "type": "CreateSavepoint",
      "name": "after_debit"
    },
    {
      "type": "Update",
      "collection": "accounts",
      "filter": {"field": "account_id", "operator": "Equals", "value": "{{to_account}}"},
      "update": {"$increment": {"balance": "{{amount}"}}
    }
  ]
}

Executing the Function​

POST /api/functions/transfer_funds
{
  "from_account": "ACC001",
  "to_account": "ACC002",
  "amount": 100
}

Automatic Behavior​

  • Begin - Logged to WAL on function start
  • Commit - Automatic if all functions succeed
  • Rollback - Automatic if auto_rollback: true and error occurs
  • WAL Logging - All lifecycle events recorded

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
{"balance": 900}

PUT /api/update/accounts/account2
{"balance": 1100}

Note: Operations are not yet part of the transaction - you must track them manually or use the transaction manager's record_operation method.

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",
  "transaction_config": {
    "enabled": true,
    "auto_rollback": true,
    "isolation_level": "ReadCommitted"
  },
  "functions": [
    {
      "type": "Insert",
      "collection": "users",
      "record": {"email": "{{email}}", "name": "{{name}}"}
    },
    {
      "type": "Insert",
      "collection": "profiles",
      "record": {"user_email": "{{email}}", "bio": "{{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
{"$decrement": {"balance": 100}}

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

# 5. Credit destination account
PUT /api/update/accounts/ACC002
{"$increment": {"balance": 100}}

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

// Script with multiple checkpoints
{
  "label": "order_processing",
  "transaction_config": {
    "enabled": true,
    "auto_rollback": false,  // Manual control
    "isolation_level": "RepeatableRead"
  },
  "functions": [
    {"type": "CreateSavepoint", "name": "order_created"},
    {"type": "Insert", "collection": "orders", "record": {"...":  "..."}},

    {"type": "CreateSavepoint", "name": "inventory_reserved"},
    {"type": "Update", "collection": "inventory", "...":  "..."},

    {"type": "CreateSavepoint", "name": "payment_processed"},
    {"type": "Insert", "collection": "payments", "record": {"...":  "..."}},

    {"type": "Update", "collection": "orders", "update": {"status": "completed"}}
  ]
}

Best Practices​

1. Choose Appropriate Isolation Level​

Use CaseRecommended LevelWhy
Analytics/ReportingReadUncommittedPerformance over consistency
General CRUDReadCommittedGood balance
Financial CalculationsRepeatableReadConsistent reads
Banking/CriticalSerializableMaximum safety

2. Use Savepoints for Multi-Stage Operations​

// Good: Savepoints for partial rollback
[
  {"type": "CreateSavepoint", "name": "before_risky_op"},
  {"type": "Update", /* risky operation */},
  // If fails, can rollback to savepoint
]

// Bad: All-or-nothing without savepoints
[
  {"type": "Update", /* operation 1 */},
  {"type": "Update", /* operation 2 */},
  {"type": "Update", /* operation 3 */},
  // If operation 3 fails, all operations rollback
]

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: Use auto_rollback
{
  "transaction_config": {
    "enabled": true,
    "auto_rollback": true  // Automatic rollback on error
  }
}

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

Transaction TypeOverheadThroughput Impact
ReadCommitted~5%Minimal
RepeatableRead~10%Low
Serializable~15-20%Moderate

Savepoint Overhead​

  • Create: < 1μs (records operation index)
  • Rollback: ~N×2μs (N = operations to undo)
  • Release: < 1μs (removes from vector)

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 vector     │                  │
│           │  - Savepoints vector     │                  │
│           │  - Isolation level       │                  │
│           └───────────┬──────────────┘                  │
│                       │                                 │
│                       ▼                                 │
│           ┌───────────────────────────┐                 │
│           │  WAL (Write-Ahead Log)    │                 │
│           │  - Transaction events     │                 │
│           │  - Savepoint events       │                 │
│           │  - Audit trail            │                 │
│           └───────────────────────────┘                 │
│                                                          │
└─────────────────────────────────────────────────────────┘

Transaction State Machine​

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

Limitations & Future Enhancements​

Current Limitations​

  1. REST API Transactions

    • Manual operation tracking (not automatic like scripts)
    • No automatic CRUD integration with transaction ID
    • Requires explicit rollback calls

  2. Isolation Levels

    • Serializable not fully implemented with MVCC
    • No deadlock detection yet
    • No distributed transaction support

  3. Performance

    • In-memory only (no distributed coordination)
    • Single-node transactions only
    • No cross-database transactions

Planned Enhancements​

  1. Full MVCC Implementation

    • Snapshot isolation for Serializable level
    • Version tracking per record
    • Conflict detection and resolution

  2. Distributed Transactions

    • Two-phase commit protocol
    • Cross-database transactions
    • Ripple-aware transactions

  3. Enhanced API

    • Automatic operation tracking in REST API
    • Transaction middleware for CRUD endpoints
    • Transaction pooling

  4. Advanced Features

    • Deadlock detection and recovery
    • Transaction priorities
    • Advisory locks
    • Prepared transactions