ACID Transactions - SoliDB Documentation

Overview

SoliDB supports fully ACID-compliant transactions across multiple documents and collections. Transactions are managed via a Write-Ahead Log (WAL) to ensure durability even in the event of a system crash.

Key Properties

Atomic: All operations succeed or none do.
Consistent: Database moves from one valid state to another.
Isolated: Transactions are invisible to others until committed.
Durable: Committed changes are permanently saved.

How it works

Transactions are initiated via the API, returning a Transaction ID (tx_id). All subsequent operations using this ID are staged in memory and the WAL. Changes are only applied to the main storage engines upon COMMIT.

Isolation Levels

SoliDB supports configurable isolation levels to balance consistency and performance. The default is Read Committed.

Read Uncommitted

Fastest

Transactions can see uncommitted changes from other transactions. "Dirty reads" are possible.

Read Committed

Default

Transactions only see data that has been committed. Prevents dirty reads but allows non-repeatable reads.

Repeatable Read

Consistent

Ensures that if you read a row twice, you get the same result. Prevents dirty and non-repeatable reads.

Serializable

Strictest

Transactions are executed as if they happened serially. Prevents all concurrency anomalies including phantom reads.

Basic Usage

Transactions are handled via HTTP endpoints. The workflow is: Begin → Perform Operations → Commit (or Rollback).

1 Begin Transaction

# Request

POST /_api/database/mydb/transaction/begin

{ "isolationLevel": "read_committed" }

# Response

{ "id": "tx:1678901234", "status": "active" }

2 Execute Operations

Use the returned tx_id in the URL path.

# Insert Document

POST /_api/database/mydb/transaction/tx:1678901234/document/users

{ "name": "Alice", "balance": 100 }

# Update Document

PUT /_api/database/mydb/transaction/tx:1678901234/document/users/user_123

{ "balance": 90 }

3 Commit

# Request

POST /_api/database/mydb/transaction/tx:1678901234/commit

# Response

{ "status": "committed" }

Transactional SDBQL

You can also execute complex queries, including conditional logic and batch updates, within a transaction.

# Execute Query in Transaction

POST /_api/database/mydb/transaction/tx:1678901234/query

{
"query": "FOR u IN users FILTER u.active == true UPDATE u WITH { status: 'processed' } IN users"
}

Transactional SDBQL queries act as "staged" operations. If the query contains INSERT, UPDATE, or REMOVE, the changes are not visible to other clients until you call COMMIT.

Lua Scripting

Transactions can be executed directly within Lua scripts for maximum performance and atomicity. The db:transaction function handles the commit/rollback logic automatically.

-- Execute Transaction in Lua

db:transaction

(function(tx)

-- Get transactional collection handles
local users = tx:collection("users")
local logs = tx:collection("audit_logs")

-- Perform operations
users:update("user_123", { balance = 90 })
logs:insert({ action = "transfer", amount = 10, user = "user_123" })

end)

If the callback function throws an error, the transaction is automatically rolled back. If it creates a return value, the transaction is committed and the value is returned.

Row-Level Locking

SoliDB implements document-level locking within transactions. Each document (row) can be independently locked, allowing high concurrency while ensuring data integrity.

Shared Locks (Reads)

Multiple transactions can hold shared locks on the same document simultaneously. Use tx:collection(name):get(key) for reads.

local accounts = tx:collection("accounts")
-- Acquires shared lock on account_123
local account = accounts:get("account_123")

Exclusive Locks (Writes)

Only one transaction can hold an exclusive lock on a document. Use insert, update, or delete for writes.

-- Acquires exclusive lock on account_123
accounts:update("account_123", { balance = 100 })

-- If another transaction tries to modify this, it will wait or fail

Transfer Example

-- Atomic transfer between accounts

local

function transfer(source_key, dest_key, amount)

return

db:transaction(function(tx)

local accounts = tx:collection("accounts")

-- Read both accounts (shared locks)
local source = accounts:get(source_key)
local dest = accounts:get(dest_key)

-- Verify funds
if source.balance < amount then

error("Insufficient funds")

end

-- Update accounts (exclusive locks)
accounts:update(source_key, { balance = source.balance - amount })
accounts:update(dest_key, { balance = dest.balance + amount })

return { success = true, amount = amount }

end)

end

Eager Locking: Locks are acquired at the START of the transaction (when first accessed), not lazily. This prevents more conflicts but requires knowing which documents will be accessed.

Deadlock Warning: If two transactions access the same documents in different order, deadlocks may occur. Design your transactions to access documents in a consistent order.

Limitations

• Graph Traversals: Multi-hop graph queries are not yet supported inside transactions.
• Aggregations: The COLLECT statement is currently read-only and cannot be used in a transactional mutation query.
• Constraint Violations: Unique index violations will cause the COMMIT to fail and automatically rollback the entire transaction.