Transactions & ACID — Atomicity and the Write-Ahead Log

All-or-nothing, even when the server dies

Transfer $100 from A to B: debit A, then credit B. If the database crashes between those two steps, $100 has vanished. A transaction bundles statements so they either all commit or none do — the property called atomicity.

ACID, concretely

• Atomicity — all statements commit or all roll back (the transfer above).
• Consistency — a transaction moves the DB from one valid state to another (constraints, foreign keys hold).
• Isolation — concurrent transactions don't corrupt each other (next lesson).
• Durability — once COMMIT returns, the data survives a crash.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;   -- both or neither; on error -> ROLLBACK

How the database actually delivers atomicity + durability: the WAL

Before changing a data page, the DB appends the change to a write-ahead log and fsyncs that first. On COMMIT it only needs the log durable (sequential write = fast). On crash recovery it replays the log: REDO committed transactions, UNDO uncommitted ones — restoring exactly the all-or-nothing boundary. (Same idea as the journal in a filesystem.)

Pitfalls

• Autocommit: many drivers commit each statement unless you BEGIN — your "transaction" was three separate ones.
• Long transactions hold locks and (under MVCC) block cleanup — keep them short.
• Doing multi-step money/inventory logic without a transaction is the classic data-loss bug.

Takeaways

• A transaction = atomic, isolated, durable bundle; wrap any multi-statement invariant in one.
• The WAL gives durability (sequential commit) and atomicity (REDO/UNDO on recovery).
• Watch autocommit; keep transactions short.

---

Re-authored for this guide; crash/atomicity diagram hand-authored as SVG. Follows Designing Data-Intensive Applications ch. 7 and the PostgreSQL docs. See also: Isolation Levels & Anomalies, MVCC & Locking.

I just read a lesson on **Transactions & ACID — Atomicity and the Write-Ahead Log** (Databases) and want to truly understand it. Explain Transactions & ACID — Atomicity and the Write-Ahead Log from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Transactions & ACID — Atomicity and the Write-Ahead Log** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Transactions & ACID — Atomicity and the Write-Ahead Log** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Transactions & ACID — Atomicity and the Write-Ahead Log** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.