Visual walkthrough — Transactions — ACID properties (each one in detail)

Step 0 — The world we are drawing

Before any promise, we need the stage. Three things exist:

• RAM (random-access memory): fast, but volatile — it forgets everything when power dies.
• Disk: slow, but durable — it remembers through a crash.
• The log: a special append-only file on disk, where we write short notes about what we are about to do.

Our task: move ₹100 from Alice to Bob. Alice starts at 500, Bob at 200. The rule the world must never break: total money = 700, always.

Look at the picture: two boxes (RAM and disk) side by side. The account values live in RAM first because that is fast; disk is where they must eventually land to be safe. The log sits on disk from the start — that is the whole trick.

Step 1 — Write the plan before touching anything

WHAT. The transaction begins. Before we change Alice's balance, we append two records to the log:

• an undo record: Alice.bal was 500 (the old value),
• a redo record: Alice.bal will be 400 (the new value).

WHY. This is the Write-Ahead Logging rule: log first, data later. If we wrote the balance first and crashed, we would have a change with no note explaining it — no way to undo it, no proof it was intended. By writing intentions first, every later state is explainable.

PICTURE.

$\underset{\text{which row}}{\underbrace{\text{Alice.bal}}}:\underset{\text{undo = old}}{\underbrace{500}}\to \underset{\text{redo = new}}{\underbrace{400}}$

• Alice.bal — the address of the thing we touch, so recovery knows where to write.
• 500 (undo) — the value to restore if we must reverse. This serves Atomicity.
• 400 (redo) — the value to re-apply if the change is lost after commit. This serves Durability.

One record, two escape hatches. See Undo and Redo Logs.

Step 2 — Apply the first change in RAM

WHAT. Now we actually set Alice.bal := 400 — but only in the RAM copy.

WHY not disk yet? Disk writes are slow. We batch them for speed. Since the log already knows both the old and new value, the RAM value can be lost or changed freely and we can always reconstruct the truth. RAM is a scratchpad the log protects.

PICTURE.

Notice in the figure: RAM now shows Alice = 400, but the disk data page still shows 500. They disagree — and that is fine, because the log holds the reconciliation plan. Total money as the world could reconstruct it is still 700 (log says Alice→400 not yet committed, disk still 500).

Step 3 — The dangerous middle: crash before commit

WHAT. The machine loses power right here — Alice has been debited in RAM, Bob has not been credited.

WHY this is the scary case. Naively, ₹100 just vanished: Alice is 400, Bob is 200, total = 600. This is exactly the disaster Atomicity must forbid.

PICTURE.

On reboot RAM is blank. Disk survives with: data page (Alice 500, Bob 200) and the log (undo Alice=500, redo Alice→400, but no COMMIT mark). Recovery reasons: "This transaction never committed. Its half-work must be erased." It applies the undo record → any leaked change is rolled back to 500.

$\text{no COMMIT record}\Rightarrow \text{UNDO}\Rightarrow \text{Alice.bal}=500$

Total = 500 + 200 = 700. The disaster is undone. Atomicity delivered by the undo log.

Step 4 — No crash this time: credit Bob too

WHAT. Rewind to a timeline with no early crash. We log Bob's change, then apply it in RAM.

$\text{Bob.bal}:\underset{\text{undo}}{\underbrace{200}}\to \underset{\text{redo}}{\underbrace{300}}$

WHY the same two-record ritual. Every write, without exception, gets its undo+redo pair first. Uniformity is what makes recovery a dumb, reliable loop instead of a pile of special cases.

PICTURE.

RAM now: Alice 400, Bob 300 (sum 700 — the invariant holds). Disk data pages: still 500, 200 (stale, but log-covered). We are one step from making it official.

Step 5 — COMMIT = flush the log, then say "OK"

WHAT. We append a COMMIT record to the log and force it to disk with fsync (a command that says "do not return until these bytes are truly on durable storage"). Only after that flush completes do we tell the client "success".

WHY the order matters. The COMMIT record on durable disk is the single source of truth for "this happened." If we returned "OK" before the flush, a crash a millisecond later would lose a payment we already promised. So durability is pinned to this exact instant: log flushed ⇒ committed ⇒ un-losable.

PICTURE.

$\underset{\text{durable proof}}{\underbrace{\text{fsync(log)}}}\to \underset{\text{promise to client}}{\underbrace{\text{return OK}}}\to \underset{\text{lazy, safe}}{\underbrace{\text{write data pages later}}}$

Crucial and counter-intuitive: at the moment we return "OK", the data pages on disk may still say 500 and 200. That is allowed — the log is the durability guarantee, not the data page. This is the Durability promise.

Step 6 — Crash after commit: replay the redo log

WHAT. Power dies right after "OK", before the lazy data-page writes finish. Disk data pages still read Alice 500, Bob 200 — the stale values.

WHY we are safe anyway. Recovery reads the log and finds a COMMIT record for this transaction. Rule: committed work must be re-applied. It replays the redo records → Alice=400, Bob=300 written to the data pages.

PICTURE.

$\text{COMMIT record present}\Rightarrow \text{REDO}\Rightarrow \text{Alice}=400,\text{Bob}=300$

Total = 400 + 300 = 700, and the transfer that the client was promised actually stuck. Compare with Step 3: same crash, opposite decision — because Step 3 had no COMMIT record and Step 6 did. That one record is the switch between undo and redo. See Undo and Redo Logs.

Step 7 — The degenerate cases (so nothing surprises you)

WHAT / WHY / PICTURE for the corners the happy path skipped:

• Empty transaction (BEGIN; COMMIT; with no writes): no undo/redo records, COMMIT is a no-op. Nothing to recover — trivially atomic and durable.
• Explicit ROLLBACK (no crash): same machinery as the Step-3 crash — apply undo records, discard. A crash of an uncommitted transaction and a deliberate rollback are the same code path.
• Rule violation (CHECK (bal >= 0)): if crediting would make a balance negative, the constraint rejects the write and the engine triggers rollback — this is Consistency using the same undo path. See Database Constraints.
• Two users at once: while all this runs, another transaction reading Alice must not see the in-flight 400 before COMMIT — that is Isolation, enforced separately by locks or MVCC. Our single-transaction picture is the foundation isolation builds on.

The undo path and redo path together cover every exit: commit-then-live, commit-then-crash, uncommitted-crash, explicit-rollback, constraint-abort. No scenario is left undrawn.

The one-picture summary

One timeline, one fork. Everything hinges on whether a COMMIT record made it to durable disk before the crash:

• No COMMIT record → walk the log backward, apply undo → old values → Atomicity.
• COMMIT record present → walk the log forward, apply redo → new values → Durability.

The log written before the data (WAL) is what makes both directions possible.