Transactions API¶

The TransactionContext class and Database.transaction() method provide ACID-compliant transaction management with automatic commit/rollback via Python context managers.

Overview¶

ArcadeDB transactions provide:

Atomicity: All changes commit together or none commit
Consistency: Schema validation and constraint enforcement
Isolation: Read committed isolation level
Durability: A committed transaction survives a process crash. With the default WAL flush mode ('no') it does not survive a power cut; call db.set_wal_flush('yes_nometadata') (or 'yes_full') for that (see set_wal_flush)

Key Concepts:

Auto-commit queries read latest data but don't create transactions
Explicit transactions required for write operations
Context managers automatically handle commit/rollback
Rollback on exception ensures data integrity

Transaction Scope¶

It is important to distinguish between operations that require explicit transactions and those that do not:

Operation Type	Examples	Transaction Requirement
Schema Operations	`create_vertex_type()`, `create_property()`, `create_index()`, `db.command("sql", "DROP INDEX...")`	Apply immediately; not transactional (a rollback does not undo them). Not needed for one statement; for many, one `with db.transaction():` or one `sqlscript` writes the schema once
Data Write	`db.command("sql", "INSERT...")`, `db.command("sql", "UPDATE...")`, `db.command("sql", "DELETE...")`, `db.command("opencypher", "CREATE ...")`	Required (Wrap in `with db.transaction():`)
Bulk Operations	`db.command("sql", "IMPORT DATABASE...")`, `db.import_documents(...)`, `db.graph_batch(...)`	Auto-transactional / auto-managed (Built-in transaction management)
Data Read	`db.query()`, `db.command("sql", "SELECT...")`, `db.lookup_by_rid()`	Optional, and better outside one: a filtered scan runs in parallel only outside a transaction (Queries)
Vector Operations	`CREATE INDEX ... LSM_VECTOR`	Applies immediately; not transactional, like the schema operations above (one index needs no transaction)

Key Distinction: `db.query()` vs `db.command()`¶

db.query(): Always used for read-only queries. Returns a ResultSet with read-only results. Does NOT require a transaction.
db.command(): Used for both DDL and DML operations. For read statements such as SELECT, it returns a ResultSet and can run outside a transaction. For write statements such as INSERT, UPDATE, and DELETE, wrap it in with db.transaction():. DDL such as CREATE TYPE, CREATE PROPERTY, CREATE INDEX, DROP apply immediately and are not transactional (a rollback does not undo them), and bulk commands such as IMPORT DATABASE and MOVE manage their own transactions.
db.import_documents(): Runs the Java importer through the narrow document-import wrapper. It manages its own importer/async lifecycle, so you should not wrap it in with db.transaction():.
db.graph_batch(): Creates the engine-backed bulk graph-ingest helper. It manages its own flush/commit lifecycle, so you should not wrap it in with db.transaction():.

Best Practice Pattern¶

# ✅ CORRECT: Queries outside transaction
results = db.query("sql", "SELECT * FROM Person")
for result in results:
    name = result.get("name")  # Safe, read-only

# ✅ CORRECT: Write operations inside transaction
with db.transaction():
    db.command("sql", "INSERT INTO Person SET name = ?", "Alice")

# ❌ INCORRECT: Write operation without transaction
db.command("sql", "INSERT INTO Person SET name = ?", "Alice")  # Will fail

Transaction Methods¶

All transaction methods are on the Database class:

`Database.transaction() -> TransactionContext`¶

Create a transaction context manager.

Returns:

TransactionContext: Context manager for transaction scope

Example:

import arcadedb_embedded as arcadedb

db = arcadedb.open_database("./mydb")

# Context manager handles begin/commit/rollback
with db.transaction():
    # All operations in this block are transactional
    db.command(
        "sql",
        "INSERT INTO Person SET name = ?, age = ?",
        "Alice",
        30,
    )

# Automatically commits on successful exit
# Automatically rolls back on exception

`Database.begin()`¶

Manually begin a transaction.

If a transaction is already active, begin() starts a nested transaction (see Nested Transactions).

Raises:

ArcadeDBError: If the transaction cannot begin (for example, the database is closed)

Example:

db.begin()

try:
    db.command("sql", "INSERT INTO Person SET name = ?", "Bob")

    db.commit()
except Exception as e:
    db.rollback()
    raise

Recommendation: Use db.transaction() context manager instead for automatic handling.

`Database.run_in_transaction(fn, retries=12, backoff_s=0.005)`¶

Run a zero-argument callable inside a transaction and return its result. Any exception rolls the transaction back. On ConcurrentModificationException or NeedRetryException the callable is run again, up to retries times, sleeping backoff_s * attempt between attempts. A with db.transaction(): block cannot be re-entered, so use this for contended writes from several threads.

Example:

def add_view():
    db.command("sql", "UPDATE Counter SET value = value + 1 WHERE name = ?", "page_views")

db.run_in_transaction(add_view)

`Database.commit()`¶

Commit the current transaction and persist changes.

Raises:

ArcadeDBError: If no active transaction or commit fails

Example:

db.begin()

db.command("sql", "INSERT INTO Item SET value = ?", 42)

db.commit()  # Changes persisted

`Database.rollback()`¶

Roll back the current transaction and discard all changes. With no active transaction it does nothing and returns None.

Raises:

ArcadeDBError: If the rollback fails

Example:

db.begin()

db.command("sql", "INSERT INTO Test SET data = ?", "temporary")

# Oops, need to undo
db.rollback()  # Changes discarded

TransactionContext Class¶

Context manager returned by db.transaction(). Automatically manages transaction lifecycle.

Behavior¶

with db.transaction():
    # db.begin() called automatically

    # ... your code ...

    # On normal exit: db.commit() called
    # On exception: db.rollback() called

Implementation¶

The TransactionContext class is simple but powerful:

class TransactionContext:
    def __enter__(self):
        self.database.begin()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type is None:
            self.database.commit()  # Success
        else:
            self.database.rollback()  # Exception occurred

Usage Patterns¶

Basic Transaction¶

import arcadedb_embedded as arcadedb

db = arcadedb.create_database("./txn_demo")

# Create schema
db.command("sql", "CREATE DOCUMENT TYPE Account")
db.command("sql", "CREATE PROPERTY Account.name STRING")
db.command("sql", "CREATE PROPERTY Account.balance DECIMAL")

# Insert data
with db.transaction():
    db.command(
        "sql",
        "INSERT INTO Account SET name = ?, balance = ?",
        "Alice",
        1000.00,
    )

db.close()

Multiple Operations in Transaction¶

with db.transaction():
    # All these operations are atomic

    # Create multiple records
    for i in range(10):
        db.command(
            "sql",
            "INSERT INTO Item SET id = ?, value = ?",
            f"item_{i}",
            i * 10,
        )

    # Query within transaction (sees uncommitted changes)
    result = db.query("sql", "SELECT COUNT(*) as cnt FROM Item")
    count = result.first().get("cnt")
    print(f"Created {count} items")

# All commits together or none commit

Conditional Commit¶

with db.transaction():
    account_rec = db.query("sql", "SELECT FROM Account WHERE name = ?", "Alice").first()

    if not account_rec:
        raise ValueError("Account not found")

    account_doc = account_rec.get_element()
    current_balance = float(account_doc.get("balance"))
    withdrawal = 500.00

    if current_balance >= withdrawal:
        new_balance = current_balance - withdrawal
        db.command(
            "sql",
            "UPDATE Account SET balance = ? WHERE name = ?",
            new_balance,
            "Alice",
        )
        print(f"Withdrawal successful. New balance: {new_balance}")
    else:
        # Raise exception to trigger rollback
        raise ValueError(f"Insufficient funds: {current_balance}")

Nested Transactions¶

Important: begin() inside an active transaction starts a new, independent transaction on the same thread. The inner transaction commits or rolls back on its own, and the outer one continues after it:

try:
    with db.transaction():
        db.command("sql", "INSERT INTO Outer SET layer = ?", "outer")

        # This DOES create a new transaction
        with db.transaction():
            db.command("sql", "INSERT INTO Inner SET layer = ?", "inner")
        # The inner insert is committed here

        raise RuntimeError("rolls back the outer transaction only")
except RuntimeError:
    pass

# The "inner" record survives; the "outer" record is rolled back

Recommendation: Avoid nesting db.transaction() unless you want the inner block to commit independently of the outer one.

Manual Transaction Control¶

For more control, use begin(), commit(), rollback() directly:

db.begin()

try:
    # Operation 1
    db.command("sql", "INSERT INTO Step1 SET status = ?", "processing")

    # Operation 2 (may fail)
    result = db.command("sql", "UPDATE Step1 SET status = ?", "complete")

    # Check condition: UPDATE returns one row with the number of records it changed
    if result.first().get("count") == 0:
        raise Exception("Update failed")

    # Success - commit
    db.commit()
    print("Transaction committed")

except Exception as e:
    # Failure - rollback
    db.rollback()
    print(f"Transaction rolled back: {e}")

Error Handling¶

Automatic Rollback¶

try:
    with db.transaction():
        db.command("sql", "INSERT INTO Test SET value = ?", "data")

        # Simulate error
        raise ValueError("Something went wrong!")

        # This won't execute
        db.command("sql", "UPDATE Test SET value = 'updated'")

except ValueError as e:
    # Transaction automatically rolled back
    print(f"Error: {e}")
    print("Changes were rolled back")

Partial Success Handling¶

from arcadedb_embedded import ArcadeDBError

success_count = 0
error_count = 0

records = [
    {"name": "Valid1", "age": 30},
    {"name": "Valid2", "age": 25},
    {"name": "Invalid", "age": "not_a_number"},  # Will fail
    {"name": "Valid3", "age": 35},
]

for record in records:
    try:
        with db.transaction():
            db.command(
                "sql",
                "INSERT INTO Person SET name = ?, age = ?",
                record["name"],
                record["age"],
            )

        success_count += 1

    except (ArcadeDBError, ValueError) as e:
        error_count += 1
        print(f"Failed to insert {record['name']}: {e}")

print(f"Success: {success_count}, Errors: {error_count}")

Validation Before Commit¶

with db.transaction():
    for i in range(5):
        db.command(
            "sql",
            "INSERT INTO Product SET sku = ?, price = ?",
            f"PROD-{i:03d}",
            i * 10.0,
        )

    # Validation check
    result = db.query("sql", "SELECT COUNT(*) as cnt FROM Product")
    count = result.first().get("cnt")

    if count < 5:
        # Trigger rollback by raising exception
        raise AssertionError(f"Expected 5 products, got {count}")

    # Validation passed, commit happens automatically

ACID Guarantees¶

Atomicity Example¶

# Transfer money between accounts (atomic)
with db.transaction():
    # Debit from Alice
    db.command("sql",
                "UPDATE Account SET balance = balance - 100 "
                "WHERE name = 'Alice'")

    # Credit to Bob
    db.command("sql",
                "UPDATE Account SET balance = balance + 100 "
                "WHERE name = 'Bob'")

# Both updates commit together or neither commits

Consistency Example¶

# Schema constraints enforced in transactions
db.command("sql", "CREATE DOCUMENT TYPE User")
db.command("sql", "CREATE PROPERTY User.email STRING (mandatory true)")
db.command("sql", "CREATE INDEX ON User (email) UNIQUE_HASH")  # a hash index enforces uniqueness too

# This will fail - email is mandatory
try:
    with db.transaction():
        db.command("sql", "INSERT INTO User SET name = 'Alice'")
except Exception as e:
    print(f"Constraint violation: {e}")
    # Transaction rolled back automatically

# This will fail - email must be unique
try:
    with db.transaction():
        db.command("sql", "INSERT INTO User SET email = 'alice@example.com'")
        db.command("sql", "INSERT INTO User SET email = 'alice@example.com'")  # Duplicate!
except Exception as e:
    print(f"Unique constraint violation: {e}")
    # Both user1 and user2 rolled back

Isolation Example¶

import threading
import time

def writer_thread():
    """Writer updates balance."""
    with db.transaction():
        time.sleep(0.5)  # Simulate slow operation
        db.command("sql", "UPDATE Account SET balance = 2000 WHERE name = 'Alice'")

def reader_thread():
    """Reader sees consistent data."""
    # Read before transaction commits
    result = db.query("sql", "SELECT balance FROM Account WHERE name = 'Alice'")
    balance = result.first().get("balance")
    print(f"Balance: {balance}")  # Sees old value (1000)

    time.sleep(1)  # Wait for writer to commit

    # Read after transaction commits
    result = db.query("sql", "SELECT balance FROM Account WHERE name = 'Alice'")
    balance = result.first().get("balance")
    print(f"Balance: {balance}")  # Sees new value (2000)

# Start threads
t1 = threading.Thread(target=writer_thread)
t2 = threading.Thread(target=reader_thread)
t1.start()
t2.start()
t1.join()
t2.join()

Durability Example¶

# Flush the WAL at every commit so a commit also survives a power cut
db.set_wal_flush("yes_nometadata")

with db.transaction():
    db.command("sql", "INSERT INTO Critical SET data = ?", "important")

# After commit, the data survives a process crash (with the default mode "no",
# it would not survive a power cut)

db.close()

# Reopen database
db = arcadedb.open_database("./mydb")

# Data is still there
result = db.query("sql", "SELECT FROM Critical WHERE data = 'important'")
assert result.first() is not None
print("Data survived!")

Performance Considerations¶

Batch Operations¶

# Inefficient: Many small transactions
for i in range(1000):
    with db.transaction():
        db.command("sql", "INSERT INTO Item SET value = ?", i)
# 1000 commits = slow

# Efficient: One large transaction
with db.transaction():
    for i in range(1000):
        db.command("sql", "INSERT INTO Item SET value = ?", i)
# 1 commit = fast

Guideline: Batch related operations in a single transaction for better performance.

Transaction Size Limits¶

# For very large batches, commit periodically
batch_size = 10000
count = 0

db.begin()
try:
    for i in range(100000):
        db.command("sql", "INSERT INTO BigData SET `index` = ?", i)

        count += 1
        if count >= batch_size:
            db.commit()
            db.begin()
            count = 0

    # Commit remaining
    if count > 0:
        db.commit()

except Exception as e:
    db.rollback()
    raise

Guideline: Commit every 10K-100K records for very large imports.

Common Patterns¶

Read-Modify-Write¶

with db.transaction():
    # Read
    result = db.query("sql", "SELECT FROM Counter WHERE name = ?", "page_views")
    counter = result.first()

    # Modify
    current_value = counter.get("value")
    new_value = current_value + 1

    # Write
    rid = counter.get_rid()
    db.command("sql", f"UPDATE {rid} SET value = ?", new_value)  # the RID names the record; the value is bound

Conditional Create¶

with db.transaction():
    # Check if exists
    result = db.query("sql", "SELECT FROM User WHERE email = ?", "alice@example.com")

    if result.first() is not None:
        print("User already exists")
    else:
        # Create if not exists
        db.command(
            "sql",
            "INSERT INTO User SET email = ?, name = ?",
            "alice@example.com",
            "Alice",
        )
        print("User created")

Optimistic Locking¶

def update_with_retry(db, rid, new_value):
    """Update with optimistic locking and retry."""

    def update():
        # Read current version
        record = db.query("sql", f"SELECT FROM {rid}").first()
        if record is None:
            raise ValueError("Record not found")

        # Update (ArcadeDB checks the record version at commit)
        db.command("sql", f"UPDATE {rid} SET value = ?", new_value)

    # Rolls back and re-runs update() on a concurrent-modification conflict
    db.run_in_transaction(update, retries=3)

Best Practices¶

Use Context Managers: Prefer with db.transaction() over manual begin()/commit()
Keep Transactions Short: Long-running transactions can block other operations
Batch Related Operations: Group related writes in one transaction
Handle Exceptions: Always handle exceptions to ensure rollback
Avoid Nested Contexts: A nested db.transaction() commits independently of the outer one
Don't Hold Transactions: Don't keep transactions open during I/O or network calls
Commit Regularly for Large Batches: For imports >100K records, commit periodically

Transactions API¶

Overview¶

Transaction Scope¶

Key Distinction: db.query() vs db.command()¶

Best Practice Pattern¶

Transaction Methods¶

Database.transaction() -> TransactionContext¶

Database.begin()¶

Database.run_in_transaction(fn, retries=12, backoff_s=0.005)¶

Database.commit()¶

Database.rollback()¶

TransactionContext Class¶

Behavior¶

Implementation¶

Usage Patterns¶

Basic Transaction¶

Multiple Operations in Transaction¶

Conditional Commit¶

Nested Transactions¶

Manual Transaction Control¶

Error Handling¶

Automatic Rollback¶

Partial Success Handling¶

Validation Before Commit¶

ACID Guarantees¶

Atomicity Example¶

Consistency Example¶

Isolation Example¶

Durability Example¶

Performance Considerations¶

Batch Operations¶

Transaction Size Limits¶

Common Patterns¶

Read-Modify-Write¶

Conditional Create¶

Optimistic Locking¶

Best Practices¶

See Also¶

Key Distinction: `db.query()` vs `db.command()`¶

`Database.transaction() -> TransactionContext`¶

`Database.begin()`¶

`Database.run_in_transaction(fn, retries=12, backoff_s=0.005)`¶

`Database.commit()`¶

`Database.rollback()`¶