Chapter 13 · Transactions and Concurrency
ACID Properties and Transaction Boundaries
A transaction is not merely a group of SQL statements. It is the database boundary around one logical state transition: either every required change becomes visible, or none of it does.
Learning outcomes
Define the unit of correctness before writing SQL
Explain atomicity, consistency, isolation, and durability without treating them as vague marketing terms.
Distinguish database-enforced invariants from business rules enforced by an application workflow.
Choose transaction boundaries that contain one complete logical operation without holding resources longer than necessary.
Use SQLite autocommit, BEGIN, COMMIT, and ROLLBACK deliberately.
Implement and verify an atomic account transfer with a durable audit row.
A transaction is one state transition
Suppose the database state before a transfer is S₀. A successful transfer produces S₁; a failed transfer must leave the database equivalent to S₀. Intermediate states may exist inside the transaction, but other work must not treat them as committed truth.
The transaction boundary encloses the complete business transition, not each statement independently.
ACID as engineering properties
| Property | Operational meaning | Typical mechanism |
|---|---|---|
| Atomicity | All effects of the transaction commit together, or the database removes them together. | Transaction log, rollback journal, undo records, write-ahead logging. |
| Consistency | A committed transaction preserves declared constraints and the application’s valid-state rules. | CHECK, UNIQUE, FOREIGN KEY, triggers, and explicit pre/postcondition checks. |
| Isolation | Concurrent transactions behave according to the selected visibility policy rather than exposing arbitrary partial work. | Locks, snapshots, MVCC, validation, and serialization checks. |
| Durability | After successful commit, the result survives later crashes according to the storage and configuration guarantees. | Log flush, atomic commit protocol, recovery, replication, and reliable storage. |
A database can enforce balance_cents >= 0, but it cannot infer whether a transfer was authorized, whether a daily limit was exceeded, or whether an external payment actually settled unless those rules are represented and checked.
Autocommit and explicit transactions
Most SQL clients operate in autocommit mode: each standalone statement is its own transaction. That is convenient for independent changes but dangerous when several statements form one operation.
-- Autocommit: these are two separate transactions.UPDATE accountSET balance_cents = balance_cents - 2500WHERE account_id = 1;UPDATE accountSET balance_cents = balance_cents + 2500WHERE account_id = 2;-- Explicit transaction: one atomic unit.BEGIN IMMEDIATE;UPDATE accountSET balance_cents = balance_cents - 2500WHERE account_id = 1 AND balance_cents >= 2500;UPDATE accountSET balance_cents = balance_cents + 2500WHERE account_id = 2;COMMIT;If the process stops between the two autocommitted statements, money disappears from one account without reaching the other. The explicit transaction prevents that partial committed state.
Build the transfer laboratory
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS transfer_event;DROP TABLE IF EXISTS account;CREATE TABLE account ( account_id INTEGER PRIMARY KEY, owner_name TEXT NOT NULL, balance_cents INTEGER NOT NULL CHECK (balance_cents >= 0), version_no INTEGER NOT NULL DEFAULT 0 CHECK (version_no >= 0)) STRICT;CREATE TABLE transfer_event ( transfer_id TEXT PRIMARY KEY, from_account INTEGER NOT NULL REFERENCES account(account_id), to_account INTEGER NOT NULL REFERENCES account(account_id), amount_cents INTEGER NOT NULL CHECK (amount_cents > 0), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, CHECK (from_account <> to_account)) STRICT;INSERT INTO account (account_id, owner_name, balance_cents) VALUES (1, 'Ava Chen', 12500), (2, 'Liam Ortiz', 7500), (3, 'Mina Park', 5000);The schema encodes local invariants: balances cannot be negative, transfer amounts must be positive, accounts must exist, and a transfer cannot target the same account.
Execute an atomic transfer
BEGIN IMMEDIATE;UPDATE accountSET balance_cents = balance_cents - 2500, version_no = version_no + 1WHERE account_id = 1 AND balance_cents >= 2500;-- The application must verify that exactly one row changed.SELECT changes() AS debit_rows;UPDATE accountSET balance_cents = balance_cents + 2500, version_no = version_no + 1WHERE account_id = 2;SELECT changes() AS credit_rows;INSERT INTO transfer_event (transfer_id, from_account, to_account, amount_cents)VALUES ('tr_20260805_001', 1, 2, 2500);SELECT SUM(balance_cents) AS total_balance_centsFROM account;COMMIT;The total balance is an invariant:
The application should verify both row counts and the invariant before committing. If a debit affects zero rows because funds are insufficient, it must roll back instead of continuing.
Choose the boundary deliberately
Too small
Committing each statement separately exposes partial business operations.
Too large
Keeping a transaction open during user input, network calls, or report rendering increases contention and failure cost.
Business-sized
Begin after external input is ready; perform database reads and writes; verify; commit; then publish side effects.
Not magically atomic
A database transaction cannot by itself roll back an email, HTTP request, or third-party payment. Use an outbox, saga, or compensating action.
Retry-aware
The transaction must be safe to repeat or protected by an idempotency key when the client cannot know whether commit succeeded.
Boundary anti-patterns
| Anti-pattern | Failure mode | Better design |
|---|---|---|
| Call a remote API while holding locks | Long waits, deadlocks, timeouts, and uncertain recovery. | Call before the transaction when safe, or write an outbox record and process it after commit. |
| Read outside, write later without a guard | The decision is based on stale state. | Recheck the predicate inside the transaction or use a version column. |
| Commit before audit insertion | Business state changes without the required evidence row. | Write state and audit event in the same transaction. |
| One transaction for an entire import file | Huge rollback cost and prolonged write ownership. | Use validated batches and savepoints with resumable progress. |
| Assume COMMIT means external durability under every setting | Storage configuration can weaken guarantees. | Understand synchronous, WAL, replication, and infrastructure settings. |
Checkpoint
Reason about boundaries
- Why are two individually correct UPDATE statements insufficient for a transfer?
- Which ACID property is primarily violated if another session observes the debit before the credit?
- Does a CHECK constraint guarantee that the transfer was authorized?
- Why should a transaction normally exclude a slow HTTP call?
- What should the application do if the guarded debit updates zero rows?
Review the answers
The two updates must commit as one atomic transition. Exposing the intermediate debit is an isolation problem and committing only the debit is an atomicity problem. CHECK validates represented data, not authorization. Slow external calls extend lock and snapshot lifetimes. A zero-row debit means the precondition failed, so the application should roll back and report the business conflict.
Summary and references
- ACID describes observable transaction guarantees, not a replacement for explicit business rules.
- Autocommit is suitable for independent statements; multi-statement operations need an explicit boundary.
- Good boundaries are complete enough for correctness and short enough for concurrency.
- Verify affected-row counts and invariants before commit.
- External side effects require patterns such as outbox processing or compensating actions.