Chapter 3: ACID Properties and Transactions

Why this matters

Chapter 1's checkout race condition — two customers both successfully buying the last unit of a limited-stock item — is exactly the failure mode transactions exist to prevent. Understanding ACID precisely (not just as a memorized acronym) is what lets you recognize when an operation genuinely needs transactional protection and reason correctly about what a transaction does and doesn't guarantee.

What a transaction is

[BEGINNER] A transaction groups multiple database operations into a single, all-or-nothing unit: either every operation in the transaction succeeds and is permanently applied (commit), or none of them are applied at all (rollback) — there's no possibility of the database ending up with only some of the transaction's changes applied.

BEGIN;

UPDATE accounts SET balance_cents = balance_cents - 5000 WHERE id = 1;  -- debit account 1
UPDATE accounts SET balance_cents = balance_cents + 5000 WHERE id = 2;  -- credit account 2

COMMIT;
-- Either BOTH updates happened, or NEITHER did. There is no possible outcome where
-- account 1 was debited but account 2 was never credited, even if the process
-- crashes between the two UPDATE statements — the database guarantees this.

ACID, precisely

[BEGINNER]

• Atomicity — a transaction's operations are all-or-nothing, as shown above; a crash mid-transaction results in the entire transaction being rolled back, not partially applied.
• Consistency — a transaction takes the database from one valid state to another valid state, respecting every constraint (Chapter 2) — a transaction that would violate a CHECK constraint or foreign key is rejected entirely, not partially committed with the violation intact.
• Isolation — concurrent transactions behave (to some configurable degree, covered fully in Chapter 4) as if they ran one at a time, even though they're actually executing concurrently for performance.
• Durability — once a transaction commits, its changes survive a subsequent crash, power loss, or restart — this is guaranteed via a write-ahead log (the database's own version of the companion Operating System book's Chapter 7 journaling concept: write the intended change durably before applying it, so a crash mid-write is always recoverable).

Applying this to Chapter 1's race condition

[IN PRACTICE] The stock-oversell bug from Chapter 1, fixed with a proper transaction:

-- WITHOUT a transaction (or with an insufficiently protective one), two concurrent
-- requests can both read stock_quantity = 1, both decide "there's enough stock,"
-- and both proceed to decrement it — resulting in stock_quantity = -1, and TWO
-- customers charged for the same single unit.

BEGIN;

SELECT stock_quantity FROM products WHERE id = 42 FOR UPDATE;  -- locks this row
-- FOR UPDATE: no other transaction can read this row for update (or write to it)
-- until THIS transaction commits or rolls back — preventing the race entirely.

-- Application checks: is stock_quantity > 0? If yes:
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 42;
INSERT INTO orders (customer_id, product_id) VALUES (123, 42);

COMMIT;

SELECT ... FOR UPDATE acquires a row-level lock, forcing a second concurrent transaction attempting the same operation to wait until the first one finishes — at which point it sees the updated stock_quantity, correctly determines there's no stock left, and doesn't oversell. This is the direct, practical answer to Chapter 1's motivating scenario.

Transactions are not free: the performance/correctness trade-off

[SENIOR INSIGHT] Every guarantee ACID provides has a real cost: locks (like FOR UPDATE above) block other transactions that need the same data, reducing concurrency; maintaining atomicity and durability requires real I/O overhead (the write-ahead log must be flushed to durable storage before a commit can be considered successful). This is exactly why not every operation needs to be wrapped in an explicit, broad transaction — a single, simple UPDATE statement is already atomic and durable on its own by default in PostgreSQL; explicit BEGIN/COMMIT blocks matter specifically when multiple statements need to succeed or fail together as one unit, as in the funds-transfer and stock-decrement examples above.

Common mistakes

1. Not wrapping multi-statement operations that must succeed or fail together in an explicit transaction, leaving a window where a crash or error between statements leaves the database in a partially-updated, inconsistent state.
2. Using a plain SELECT (without FOR UPDATE) to check a condition before an UPDATE in a concurrent context, recreating exactly Chapter 1's race condition — the check-then-act pattern needs an explicit lock to be safe under concurrency (a direct echo of the companion OOP book's Chapter 14 concurrency failure mode).
3. Wrapping unrelated, unnecessary operations into one large transaction, holding locks longer than needed and reducing concurrency for other transactions without a corresponding correctness benefit.
4. Assuming "the database is transactional" means every individual operation is automatically protected from every possible concurrency issue — atomicity/consistency/durability are guaranteed per transaction, but the isolation level (Chapter 4) determines exactly what concurrent transactions can and can't see of each other's in-progress work, and getting this wrong is a common, subtle source of bugs.

Checklist: using transactions correctly

1. Wrap any group of statements that must all succeed or all fail together in an explicit transaction (BEGIN/COMMIT/ROLLBACK) — funds transfers, multi-table updates that must stay consistent, and check-then-act sequences are the classic cases.
2. Use SELECT ... FOR UPDATE (or an equivalent locking mechanism) for any check-then-act sequence under concurrent access, not a plain SELECT followed by a separate UPDATE.
3. Keep transactions as short and narrowly scoped as possible — include only the operations that genuinely need to succeed or fail together, to minimize lock contention with other concurrent transactions.
4. Handle transaction failures (deadlocks, constraint violations) with an explicit retry strategy at the application layer, per the companion Operating System book's Chapter 10 deadlock detection/recovery discussion — a failed transaction due to a detected deadlock is expected, handleable behavior, not a bug.

Discussion / interview questions

1. "What does ACID stand for, and can you give a concrete example of what would go wrong if one of the four properties were missing?" Strong answer: Atomicity, Consistency, Isolation, Durability — gives a specific failure example for at least one (e.g., without atomicity, a crash mid-transfer could debit one account without crediting the other; without durability, a committed transaction could be silently lost on a crash).

2. "How would you prevent a race condition where two concurrent requests both see enough inventory and both decide to proceed with a purchase?" Strong answer: Uses SELECT ... FOR UPDATE (or an equivalent row-level locking mechanism) within a transaction to serialize access to the inventory check-and-decrement sequence, ensuring the second concurrent request sees the updated (already-decremented) value before making its own decision.

3. "Are all database operations automatically protected by ACID guarantees, or only ones explicitly wrapped in a transaction?" Strong answer: A single statement is atomic and durable by default; explicit transactions matter specifically when multiple statements need to succeed or fail together as one indivisible unit — and even within a transaction, the isolation level (Chapter 4) determines exactly what concurrent transactions can observe of each other's uncommitted work.

4. "What's the trade-off of wrapping many unrelated operations into one large database transaction?" Strong answer: Locks are held for the duration of the transaction, reducing concurrency for other transactions needing the same data — a large, broadly-scoped transaction increases contention without necessarily providing any additional correctness benefit over several narrower, appropriately-scoped transactions.

Chapter summary / cheat sheet

• A transaction groups operations into an all-or-nothing unit — commit (all applied) or rollback (none applied), with no partial-application outcome possible.
• ACID: Atomicity (all-or-nothing), Consistency (constraints always respected), Isolation (concurrent transactions behave as if sequential, to a configurable degree), Durability (committed data survives a crash, via a write-ahead log).
• SELECT ... FOR UPDATE locks a row, preventing the exact check-then-act race condition that causes classic bugs like overselling limited inventory.
• Transactions have a real performance cost (locking, durability I/O) — scope them narrowly to the operations that genuinely need to succeed or fail together, not broadly by default.

---