A database transaction groups database operations into one unit that is either committed or rolled back. In a quant system, this boundary can keep a position update and its cash entry together, rather than leave half a ledger change recorded after a failure. A transaction may contain reads as well as writes, and may contain just one statement. PostgreSQL’s transaction tutorial explains this grouping.
Here, “transaction” means a database operation boundary. A market trade is an economic event; recording one trade can require several SQL statements within a database transaction. Committing those statements does not execute or settle the market trade.
The boundary matters more than the number of statements
Consider fictional accounts holding 100 USD in A and 50 USD in B. Moving 30 USD from A to B requires a debit and a credit. The completed result is A = 70 USD, B = 80 USD, with the combined balance still 150 USD.
If the debit commits independently and the credit fails, the stored balances can remain 70 USD and 50 USD, totalling 120 USD. Each successful statement can be individually valid while the business operation is incomplete. Explicitly grouping both updates lets the application roll the debit back when the credit cannot be completed.
COMMIT accepts the transaction; ROLLBACK cancels its pending database changes. SQLite documents these boundaries and the fact that errors do not all automatically roll back the entire transaction in its transaction reference.
The debit alone is a pending state on the writer connection. Commit keeps the complete transfer; rollback restores the starting balances.
The broader guarantees and their limits are the ACID properties. A transaction boundary does not invent the business rule: the application and database constraints must still enforce the intended transfer.
An explicit SQLite transaction in Python
This self-contained fixture uses integer whole USD, not floating-point money. It first attempts a transfer to a missing recipient, then repeats the transfer to B. Checking the credit’s affected-row count matters: updating no matching row is not itself a SQL error.
import sqlite3
con = sqlite3.connect(":memory:", autocommit=True)
con.execute("CREATE TABLE accounts (id TEXT PRIMARY KEY, "
"balance INTEGER NOT NULL CHECK (balance >= 0))")
con.executemany("INSERT INTO accounts VALUES (?, ?)", [("A", 100), ("B", 50)])
def transfer(recipient: str) -> None:
"""Move 30 whole USD from the fixture's account A."""
con.execute("BEGIN")
try:
con.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?",
(30, "A"))
credit = con.execute(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
(30, recipient),
)
if credit.rowcount != 1:
raise ValueError("recipient missing")
con.execute("COMMIT")
except Exception:
if con.in_transaction:
con.execute("ROLLBACK")
raise
try:
transfer("missing")
except ValueError:
print("rolled back:", con.execute(
"SELECT id, balance FROM accounts ORDER BY id").fetchall())
# rolled back: [('A', 100), ('B', 50)]
transfer("B")
print("committed:", con.execute(
"SELECT id, balance FROM accounts ORDER BY id").fetchall())
# committed: [('A', 70), ('B', 80)]
con.close()Executed with Python 3.14.4 and SQLite 3.53.0; the
autocommit argument requires Python 3.12 or later. With autocommit=True, this example deliberately controls the boundary through SQL BEGIN, COMMIT and ROLLBACK. Python’s con.commit() and con.rollback() methods have no effect in that mode. For ordinary Python-managed transactions, the Python documentation recommends autocommit=False; do not combine these two control styles without understanding when transactions open.The exception handler rolls back an active transaction and re-raises the failure. Only the outer demonstration catches the expected missing-recipient error. The query specifies
ORDER BY id, so the output order is intentional. The fixed sender A and fixed amount keep the example small; a general transfer service also needs validated inputs, account checks and duplicate-request handling. This in-memory demonstration checks commit and rollback behavior, not crash recovery or durable storage.What the boundary does not promise
Concurrency depends on isolation. A transaction is not automatically a globally frozen view of the database. PostgreSQL 18’s default Read Committed level can show different committed data in two successive queries; Serializable provides stronger guarantees and can require retrying the whole transaction. See its isolation reference. SQLite has its own concurrency rules, including one writer at a time. Under its normal separate-connection settings, other connections cannot read the writer’s uncommitted changes; the writer can read its own pending changes. Its isolation documentation explains the shared-cache exception and journal modes. The figure’s intermediate balance is the writer’s pending state.
External actions need their own coordination. A local database rollback cannot recall an order already sent to a broker or unsend an email. If recording an instruction and sending it must stay aligned, design a delivery-and-reconciliation process, including a way to recognise repeated requests. The local commit alone does not prove that the remote action happened exactly once.
For the surrounding research workflow, SQL and Persistent Data develops keyed price tables and bounded queries. Its public page is a preview; the full lesson requires membership.