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, letter by letter (and who owns each letter)

• Atomicity (engine) — all statements commit or all roll back. The transfer above is the canonical example. Delivered by the WAL + recovery (REDO/UNDO), not by "hoping both UPDATEs run."
• Consistency (mostly application + declared constraints) — a transaction leaves the database in a state that satisfies every invariant the engine was told about (PK/UNIQUE/FK/CHECK) and every business invariant the app encoded in the transaction (inventory never negative, double-entry balances). The engine cannot invent "A+B money conserved" unless you write both legs in one transaction and declare the constraints. A/I/D are engine properties; C is shared: constraints are engine-enforced, business rules are app-authored. "ACID means the database keeps my app correct" is the slogan failure mode of this page.
• Isolation (engine, but level-dependent) — concurrent transactions do not see each other's intermediate states beyond what the isolation level permits. "Don't corrupt each other" is not a single guarantee — it is a menu of anomalies (next section).
• Durability (engine) — once COMMIT returns, the committed effects survive process crash and power loss (WAL fsync / equivalent). Note: durability to the primary does not automatically mean durability on every replica.

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

Isolation is not a slogan: anomalies and defaults

Without naming anomalies, "I" is marketing. Four classic read anomalies (full traces live on the Isolation Levels page; you need the names now):

Anomaly	What goes wrong	Closed by (typical)
Dirty read	You read another txn's uncommitted write; it may roll back	Read Committed and above
Non-repeatable read	Same row, read twice in one txn, two different committed values	Repeatable Read / Snapshot Isolation
Phantom	Same predicate, re-run SELECT, different set of rows (insert/delete in range)	SI snapshot freezes classic phantom reads; lock engines use gap locks; true serializability needs SERIALIZABLE
Write skew / lost update	Two txns read overlapping state, each writes a different row; both commit; invariant broken	SELECT FOR UPDATE, atomic UPDATE, or SERIALIZABLE (SSI)

Defaults matter — "ACID" does not mean "serializable by default":

• PostgreSQL default: READ COMMITTED — each statement gets a fresh snapshot; non-repeatable reads and write skew are possible across statements in one transaction.
• MySQL InnoDB default: REPEATABLE READ — stronger than Postgres's default name, but InnoDB RR is not identical to Postgres RR (gap locks vs pure SI). Still not full serializability.
• Oracle default: READ COMMITTED (statement-level SI).
• SQL Server default: READ COMMITTED (locking RC unless RCSI is on).

So an app that "uses transactions" on Postgres still runs at RC unless you raise the level. Multi-statement money, inventory, or capacity checks need an explicit level (or SELECT … FOR UPDATE / single atomic UPDATE) — not faith in the word ACID.

How the database 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.

Why both REDO and UNDO exist: STEAL / NO-FORCE

Recovery is not an arbitrary design — it is forced by two buffer-manager policy answers:

• STEAL = yes: a dirty page holding an uncommitted change may be evicted to disk before that transaction commits. Without STEAL, a long transaction pins every page it dirtied in the buffer pool. With STEAL, disk can already contain uncommitted data after a crash → recovery must UNDO losers.
• FORCE = no (NO-FORCE): commit does not flush every touched data page to disk; only the WAL is forced. Commit stays a sequential log write. After a crash, committed changes may exist only in the WAL → recovery must REDO winners.

Real engines (Postgres, InnoDB, ARIES-style systems) choose STEAL + NO-FORCE for throughput. That single choice is why both REDO and UNDO appear on restart. FORCE would make REDO optional but slow every commit to random page I/O; NO-STEAL would make UNDO optional but starve the buffer pool under long writers. (Full ARIES trace and 2×2 matrix: the Transactions deep-dive recovery page.)

Pitfalls

• Autocommit: many drivers commit each statement unless you BEGIN — your "transaction" was three separate ones; the A→B transfer is not atomic.
• Long transactions hold locks and (under MVCC) hold back vacuum/undo cleanup — keep them short.
• Doing multi-step money/inventory logic without a transaction is the classic data-loss bug.
• "We use ACID so we're safe" — without knowing default isolation, you may still allow non-repeatable reads, phantoms (depending on engine), and write skew. Name the level.
• Assuming C is free — missing FKs/CHECKs and multi-statement invariants not wrapped in one transaction are app bugs the engine will not invent for you.

Interview drills

1. L1: Name A, C, I, D in one sentence each. Which are purely engine properties?
   Answer: A all-or-nothing, C valid state transition, I concurrent anomaly control, D commit survives crash. A/I/D engine; C shared (constraints + app).
2. L2: Why does recovery need both REDO and UNDO?
   Answer: STEAL ⇒ uncommitted pages may be on disk ⇒ UNDO; NO-FORCE ⇒ committed pages may not be on disk ⇒ REDO.
3. L3: Postgres defaults to READ COMMITTED. Can two statements in one transaction see different committed values of the same row?
   Answer: Yes — non-repeatable read is allowed at RC. Raise to REPEATABLE READ / SERIALIZABLE or use FOR UPDATE for critical rows.
4. L4: Client got COMMIT success, then the primary's disk failed before a replica applied the change. Is durability violated?
   Answer: Single-node durability held if WAL was fsynced on the primary; cross-replica durability is a separate replication/sync-commit guarantee.

Takeaways

• A transaction = atomic, isolated (at a named level), durable bundle; wrap any multi-statement invariant in one.
• The WAL + STEAL/NO-FORCE buffer policy give durability and force both REDO and UNDO on recovery.
• C is not "the database is always right" — it is constraints plus application transaction logic.
• I is anomaly-shaped; know dirty / non-repeatable / phantom / write skew and your engine's default level.
• Watch autocommit; keep transactions short; never treat "ACID" as "serializable."

---

Re-authored for this guide; crash/atomicity diagram hand-authored as SVG. Follows Designing Data-Intensive Applications ch. 7, the ARIES recovery rationale (STEAL/NO-FORCE), and the PostgreSQL / InnoDB isolation defaults. See also: Isolation Levels & Anomalies, MVCC & Locking, Transactions deep dive (recovery/SSI).

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — Transactions & ACID — Atomicity and the Write-Ahead Log

Why this concept exists (judgment layer)

Entry page for transactions: A/I/D are engine mechanisms; C is shared; I is anomaly menu + default levels — not the slogan 'ACID means safe.' STEAL/NO-FORCE explains REDO+UNDO.

Mental model (install this intuition)

Atomicity = commit record all-or-nothing. Durability = WAL fsync. Isolation = level-selected anomaly control. Consistency = declared constraints + app invariants in one txn. Defaults: PG RC, MySQL InnoDB RR — neither is full serializable.

Worked example with numbers or traced steps

BEGIN; A-=100; crash; B+=100 never runs
Without txn: money gone. With WAL: UNDO debit on recovery
STEAL: dirty uncommitted pages may hit disk → need UNDO
NO-FORCE: commit does not flush all data pages → need REDO
PG RC: two SELECTs in one txn can see different committed values of same row

When NOT to use / named alternative

Do not wrap every read in long SERIALIZABLE transactions (lock/SSI abort cost). Do not assume multi-statement safety under autocommit. Single-row atomic UPDATE may beat multi-statement txn for simple counters.

Failure mode & ops fingerprint

Fingerprint: transfer as three autocommit statements; 'we use Postgres so we're serializable'; fsync off; long txn holds vacuum; write skew on two doctors on-call both going off duty under SI.

Hostile-panel drills (defend the decision)

Q1. Which ACID letters are purely engine?
Model answer: A, I (mechanism), D. C is shared: engine constraints + application transaction logic.

Q2. Why both REDO and UNDO?
Model answer: STEAL ⇒ uncommitted data may be on disk ⇒ UNDO losers. NO-FORCE ⇒ committed data may only be in WAL ⇒ REDO winners.

Q3. Postgres default and non-repeatable read?
Model answer: READ COMMITTED — yes, two statements can see different committed values; raise level or FOR UPDATE for critical sections.

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.