Nemo Transactions Reference

This guide covers all transaction types, methods, callbacks, and patterns available in Nemo for both synchronous and asynchronous applications.

Transaction Methods Reference

All 7 Transaction Patterns and Use Cases

Pattern 1: Automatic Context Manager Transactions

The recommended Pythonic approach. The context manager automatically commits if the block finishes cleanly, or rolls back if an exception is raised.

Synchronous Context Manager

import nemo

with nemo.connect("accounts") as db:
    with db.connect_sync() as conn:
        with conn.transaction():
            conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1;")
            conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2;")
            # Successfully committed on exit

Asynchronous Context Manager

import asyncio
import nemo

async def transfer():
    with nemo.connect("accounts") as db:
        async with db.connect_async() as conn:
            async with conn.transaction():
                await conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1;")
                await conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2;")

asyncio.run(transfer())

Pattern 2: Explicit Manual Transactions

For complex workflows where transaction boundaries span multiple helper functions or conditional steps:

import nemo

with nemo.connect("store") as db:
    with db.connect_sync() as conn:
        txn = conn.begin_transaction()
        try:
            conn.execute("INSERT INTO orders (id, customer) VALUES (?, ?);", (501, "Alice"))
            conn.execute("UPDATE inventory SET stock = stock - 1 WHERE item_id = 99;")

            # Commit explicitly
            txn.commit()
        except Exception as e:
            # Revert all changes
            txn.rollback()
            raise

Pattern 3: Batch Ingestion with Autocommit Disabled

When executing thousands of single write statements, disabling autocommit groups them into a single transaction commit, drastically increasing write throughput:

import nemo

with nemo.connect("metrics") as db:
    with db.connect_sync() as conn:
        # Disable per-statement autocommit
        conn.set_autocommit(False)

        for i in range(10000):
            conn.execute("INSERT INTO sensor_log VALUES (?, ?);", (i, 20.0 + (i % 5)))

        # Commit all 10,000 writes at once
        conn.commit()

        # Restore autocommit
        conn.set_autocommit(True)

Pattern 4: Read-Only Snapshot Isolation

Nemo provides snapshot isolation: readers execute against a consistent snapshot of data without blocking concurrent writers, and writers do not block readers.

import nemo

with nemo.connect("analytics") as db:
    with db.connect_sync() as conn:
        # Long-running analytical query sees consistent point-in-time data
        with conn.transaction():
            total = conn.execute("SELECT SUM(amount) FROM ledger;").fetchone()[0]
            avg = conn.execute("SELECT AVG(amount) FROM ledger;").fetchone()[0]
            print(f"Total: {total}, Average: {avg}")

Pattern 5: Multi-Connection Concurrency

Multiple connections can execute transactions simultaneously across threads or coroutines without database-wide lock collisions:

import threading
import nemo

def worker(worker_id: int):
    with nemo.connect("cluster") as db:
        with db.connect_sync() as conn:
            with conn.transaction():
                conn.execute("INSERT INTO worker_heartbeats VALUES (?, ?);", (worker_id, "alive"))

threads = [threading.Thread(target=worker, args=(i,)) for i in range(8)]
for t in threads: t.start()
for t in threads: t.join()

Pattern 6: Connection Pool Transactions

When working with connection pools, transactions are contained within the leased connection:

import nemo

with nemo.connect("app") as db:
    pool = db.create_pool_sync(max_connections=4)

    with pool.acquire() as conn:
        with conn.transaction():
            conn.execute("INSERT INTO sessions VALUES (?);", ("session_token_xyz",))

    pool.close()

Pattern 7: Transient Retry Logic

For mission-critical production services, wrap transactions in an exponential backoff retry handler:

import time
import nemo
from nemo import ConcurrencyError, OperationalError

def execute_with_retry(conn, fn, max_attempts=5):
    for attempt in range(max_attempts):
        try:
            with conn.transaction():
                return fn(conn)
        except (ConcurrencyError, OperationalError):
            if attempt == max_attempts - 1:
                raise
            time.sleep(0.01 * (2 ** attempt))

Related Runnable Examples

• example_9.py: ACID Transactions, Rollbacks, and Multi-Version Concurrency (MVCC).
• example_10.py: Multi-Threaded Connection Pooling and High Concurrency.
• example_16.py: Exact Financial Decimals and Multi-Account Banking Ledgers.
• example_39.py: ACID Transaction Guarantees and Rollback Semantics.