Transactions — Aquilia Documentation
Comprehensive guide and documentation for Transactions in the Aquilia framework. View API reference, examples, and implementation patterns.
SQLite / Transactions & Savepoints Transactions & Savepoints Execute atomic block queries with automatic transaction rollbacks and nested savepoints. Basic Transactions Use the transaction() context manager of a connection to run multiple queries atomically. By default, transactions run in DEFERRED mode. async with pool.acquire(readonly=False) as conn: async with conn.transaction(): # Both inserts succeed together, or both are rolled back on error await conn.execute( "INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"] ) await conn.execute( "INSERT INTO settings (user_name, theme) VALUES (?, ?)", ["Alice", "dark"] ) Auto-Commit / Auto-Rollback: The transaction is committed automatically when exiting the context block without exceptions. If an unhandled exception occurs inside the block, the transaction is rolled back immediately. Savepoints (Nested Transactions) SQLite does not support nested transactions natively. However, Aquilia provides savepoint_ctx() to implement nested transaction boundaries that rollback partial work without rolling back the entire parent transaction. async with pool.acquire(readonly=False) as conn: async with conn.transaction() as parent_txn: # Parent insertion await conn.execute("INSERT INTO logs (message) VALUES (?)", ["Initial Log"]) try: # Nested savepoint context async with conn.savepoint_ctx("sp1"): await conn.execute("INSERT INTO users (name) VALUES (?)", ["Bob"]) raise ValueError("Rollback Bob insertion") except ValueError: # Bob insertion is rolled back, parent logs are preserved pass await conn.execute("INSERT INTO logs (message) VALUES (?)", ["Execution Finished"]) Transaction Locking Modes Control when lock acquisitions are obtained on the database file. Configure via the mode argument on transaction(). Lock Mode Lock Acquisition Typical Use Case ))} Read-Only Connections Maximize WAL concurrency by explicitly acquiring read-only connections. The pool routes read queries to the parallel reader pool when readonly=True is passed. # Acquire a read-only reader connection (does not block the writer connection) async with pool.acquire(readonly=True) as conn: rows = await conn.fetch_all("SELECT * FROM users WHERE active = 1") Common Pitfalls 1. Nested Transaction Calls Calling nested conn.transaction() blocks within each other raises an error. Always use savepoint contexts for nesting. # BAD - Will raise error async with conn.transaction(): async with conn.transaction(): # Raises TransactionFault pass # GOOD - Use savepoint contexts async with conn.transaction(): async with conn.savepoint_ctx("sp_checkpoint"): pass 2. Network I/O Inside Transactions Since SQLite serialization restricts concurrent writes, holding a transaction open while executing slow HTTP requests blocks other writers. # BAD - Blocks the single database writer connection async with conn.transaction(): await conn.execute("UPDATE users SET processing = 1 WHERE id = ?", [user_id]) await call_third_party_api() # Network call blocks connection pool await conn.execute("UPDATE users SET processed = 1 WHERE id = ?", [user_id]) # GOOD - Keep transaction blocks brief await conn.execute("UPDATE users SET processing = 1 WHERE id = ?", [user_id]) await call_third_party_api() async with conn.transaction(): await conn.execute("UPDATE users SET processed = 1 WHERE id = ?", [user_id]) )
Go to Homepage