ACID properties are four guarantees used to describe reliable database transactions: atomicity, consistency, isolation and durability. For a quant system recording cash movements, they answer different questions: can half a transfer survive, do the stored values obey the rules, what can concurrent jobs observe, and what survives a failure after commit?
A transaction is the unit of work to which these guarantees apply. Two individually successful SQL statements do not automatically form one atomic transfer.
Four promises, four different questions
| Property | Meaning | Cash-transfer consequence |
|---|---|---|
| Atomicity | The transaction's changes take effect together, or are undone together. | A failed credit must not leave a committed debit from the same transfer. |
| Consistency | A valid transaction preserves the database's declared integrity rules and the application's required invariants. | Required account identifiers and nonnegative balances can be enforced; conservation of cash must also be designed into the transfer. |
| Isolation | Concurrent transactions interact according to the database's isolation level. | A valuation job's view of a transfer depends on the engine, mode and transaction boundaries. |
| Durability | A successful commit survives the failures covered by the system's durability guarantee. | A confirmed cash posting must survive those failures; logging, storage and configuration determine that guarantee. |
The distinction matters. A database can atomically commit the wrong amount. A nonnegative-balance check does not prove that the credit equals the debit, that the recipient was intended, or that an exchange executed the matching trade. Database constraints encode particular rules; ACID does not infer business correctness.
A transfer has one boundary
Consider a fictional ledger measured in whole USD: account A holds 100, account B holds 0, and a transfer moves 30 from A to B. The intended committed state is A = 70 and B = 30, keeping total cash at 100.
One transaction groups both ledger changes. The pending debit is visible to the transaction itself; it is not a completed cash transfer.
This Python 3.12+ example uses SQLite and explicit SQL boundaries. Each call starts a separate in-memory fixture.
autocommit=True leaves transaction control to the SQL commands here: BEGIN, followed by COMMIT or ROLLBACK. Setup statements occur before that boundary. Values are bound through placeholders, and ORDER BY makes the returned account order explicit. See Python's transaction-control documentation.import sqlite3
def transfer(fail_after_debit: bool) -> list[tuple[str, int]]:
"""Run one fictional 30 USD transfer on a fresh ledger."""
con = sqlite3.connect(":memory:", autocommit=True)
con.execute("""CREATE TABLE cash (
account TEXT PRIMARY KEY NOT NULL,
usd INTEGER NOT NULL CHECK (usd >= 0)
)""")
con.executemany("INSERT INTO cash VALUES (?, ?)", [("A", 100), ("B", 0)])
con.execute("BEGIN")
try:
con.execute("UPDATE cash SET usd = usd - ? WHERE account = ?", (30, "A"))
if fail_after_debit:
raise RuntimeError("injected failure")
con.execute("UPDATE cash SET usd = usd + ? WHERE account = ?", (30, "B"))
con.execute("COMMIT")
except RuntimeError:
con.execute("ROLLBACK")
rows = con.execute("SELECT account, usd FROM cash ORDER BY account").fetchall()
con.close()
return rows
print(transfer(True))
# [('A', 100), ('B', 0)]
print(transfer(False))
# [('A', 70), ('B', 30)]The first call deliberately fails immediately after the debit. The handler rolls back the whole open transaction, restoring both starting balances. The second call completes the credit before committing. SQLite documents explicit and implicit transaction boundaries in its transaction reference.
This is an atomicity demonstration, with a known ledger result. It does not test concurrent readers or crash recovery. The table's
CHECK rejects negative balances, but it does not enforce the cross-account total. A real posting service must also validate accounts, amounts and its ledger invariants, and handle failures beyond this deliberately injected exception.Isolation and durability need their settings
Isolation is not universally serializable. SQLite normally serializes writes; separate connections cannot read another connection's uncommitted changes, except when shared cache and
read_uncommitted are both enabled. Its WAL mode allows readers to retain an older snapshot while a writer commits. These are SQLite-specific rules, described in Isolation in SQLite. PostgreSQL 18, for comparison, defaults to Read Committed: two successive queries in one transaction can see different committed data. Stronger isolation and application handling of retries may be needed for a multi-step risk or limit decision. See PostgreSQL transaction isolation.Durability requires persistent storage and an appropriate configuration. The example's
:memory: database disappears when its connection closes. Its successful output proves neither persistence nor resilience to power loss. For a file-backed SQLite database, journal mode and synchronous settings matter: WAL with synchronous=NORMAL can lose a committed transaction after a power failure, whereas FULL adds a WAL synchronization at each commit. The storage system must honor the required writes and synchronization. Consult SQLite's synchronous settings for the mode-specific guarantees.Finally, the transaction covers its database work. Rolling it back cannot recall an email already sent or reverse an order already accepted by an external exchange. Coordinating such effects needs an application protocol, including a way to recognize duplicate requests; ACID alone does not extend the local transaction boundary.
Continue with persistent quant data
SQL and Persistent Data develops a price schema, rejects duplicate instrument-date keys and queries a research panel. The public page is a preview; the full lesson requires membership. It is a useful next step for deciding what integrity rules belong in the data store.
Sources
- Python 3.14: sqlite3 transaction control — Python wrapper behavior and explicit control.
- SQLite: Transaction — transaction boundaries and commit/rollback behavior.
- SQLite: Isolation — separate connections, shared-cache exception and WAL snapshots.
- SQLite: synchronous PRAGMA — persistence trade-offs by journal and synchronization mode.
- PostgreSQL 18: Transaction Isolation — isolation levels and Read Committed behavior.