Worked examples — Transactions — ACID properties (each one in detail)

Every symbol we use is plain: bal is a number of rupees stored in a row; a write is "put a new number in a row"; the undo log stores the old number so we can go back; the redo log stores the new number so we can re-do it after a crash. That's all the machinery you need.

---

The scenario matrix

Think of every ACID story as one cell in this grid. The axis is which promise is being tested; the case is what specifically goes wrong (or doesn't).

#	Property tested	Case class	What we throw at it
M1	Atomicity	Happy path (no failure)	Full transfer, clean commit
M2	Atomicity	Crash before commit	Undo log must erase partial work
M3	Durability	Crash after commit	Redo log must re-apply lost data-page write
M4	Consistency	Rule violation	A CHECK would break → forced rollback
M5	Consistency	Application invariant	"Total money constant" the DB can't see
M6	Isolation	Dirty read (uncommitted)	Reader sees a value that gets rolled back
M7	Isolation	Non-repeatable read	Same row, two different values in one txn
M8	Isolation	Phantom read	Range query gains a new row
M9	Degenerate input	Empty / zero / self	Empty txn, ₹0 transfer, Alice→Alice
M10	Limiting / exam twist	Isolation-level choice	Weakest level that still holds an invariant

Each worked example below is tagged with the cell(s) it fills. Together they cover every row.

---

Setup we reuse

Two accounts. We reset before each example so numbers are clean.

---

Example 1 — Happy path commit · cell M1

Steps.

1. BEGIN. Why this step? Opens the bracket; from here everything is one unit.
2. Write undo record Alice.bal = 500 (old value). Why? So that if we abort later we can restore it.
3. Write redo record Alice.bal: 500→400 and set Alice.bal := 400. Why? The redo record is durable proof of the new value; the in-memory write makes it visible inside the txn.
4. Write undo Bob.bal = 200, redo Bob.bal: 200→300, set Bob.bal := 300. Why? Same pattern for the credit side.
5. COMMIT: flush the redo log to disk (fsync), then return "OK". Why? Durability is delivered by writing the log first (see Write-Ahead Logging (WAL)).

Verify. Final: Alice.bal = 400, Bob.bal = 300. Sum = 700 ✓ (invariant held). Money conserved, no rule broken. ✓

---

Example 2 — Crash before commit · cell M2 (Atomicity)

Steps.

1. BEGIN; write undo Alice.bal = 500; set Alice.bal := 400. Why? Normal first half.
2. 💥 Crash before the credit and before any COMMIT. Why note this? No commit record exists on disk → this transaction is officially unfinished.
3. Restart. Recovery scans the log, sees a BEGIN with no matching COMMIT. Why? That is the exact signature of a partial transaction.
4. Recovery replays the undo log backwards: restore Alice.bal := 500. Why? Atomicity = all-or-nothing; since "all" didn't happen, we force "nothing" (see Undo and Redo Logs).

Verify. Final: Alice.bal = 500, Bob.bal = 200. Sum = 700 ✓. The ₹100 that momentarily "vanished" is restored — no money created or destroyed. ✓

---

Example 3 — Crash after commit · cell M3 (Durability)

Steps.

1. Perform the transfer in memory: Alice.bal := 400, Bob.bal := 300. Why? Fast in-RAM updates.
2. COMMIT: flush redo records Alice: 500→400, Bob: 200→300, then a COMMIT marker, to durable storage. Return "OK". Why? Log-first rule — the durable log now proves the commit even though the data pages are still only in RAM.
3. 💥 Crash before the dirty data pages get lazily written back to the data file. Why matter? The data file still shows the OLD numbers 500/200.
4. Restart. Recovery finds a COMMIT marker for this txn but sees the data file is stale, so it replays the redo log forward: re-apply Alice := 400, Bob := 300. Why? A committed transaction must survive; redo re-delivers the promised effect.

Verify. Final: Alice.bal = 400, Bob.bal = 300, sum = 700 ✓. The "OK" the client received was honoured. ✓

---

Example 4 — Constraint violation forces rollback · cell M4 (Consistency)

Reset for this one: Alice.bal = 50, Bob.bal = 200.

Steps.

1. BEGIN; attempt Alice.bal := 50 - 100 = -50. Why? The debit is applied tentatively.
2. The engine checks CHECK (bal >= 0) against -50. It is false. Why this step? Declared rules are enforced at statement/commit time — this is exactly Database Constraints doing its job.
3. Rule violated → the whole transaction is rolled back via the undo log; the credit to Bob never even runs. Why? Consistency: a state that breaks a declared rule must never be persisted; partial success is not allowed (that's atomicity backing consistency up).

Verify. Final: Alice.bal = 50, Bob.bal = 200, sum = 250 (unchanged) ✓. No negative balance was ever committed. ✓

---

Example 5 — Application invariant the DB can't see · cell M5 (Consistency)

Reset: Alice.bal = 500, Bob.bal = 200.

Steps.

1. BEGIN; Alice.bal := 400 (−100); Bob.bal := 300 (+100); then a second, buggy Bob.bal := 400 (+100 again). Why show this? Each individual write is a legal non-negative number.
2. COMMIT succeeds — every CHECK (bal >= 0) passes. Why? The DB only knows declared rules; "total is constant" is not declared, so the DB cannot catch it.

Verify. Final: Alice.bal = 400, Bob.bal = 400, sum = 800 ≠ 700 ✗. The invariant is broken, and ACID-C did not save us. Lesson: ACID Consistency = the DB keeps rules it was told about; invariants you never declared are the application's responsibility. ✓ (check confirms the sum really is 800.)

---

Example 6 — Dirty read · cell M6 (Isolation)

Steps (Read Uncommitted — the broken case).

1. T1 BEGIN; sets Alice.bal := 0 (not committed). Why? T1 is mid-work.
2. T2 (Read Uncommitted) reads Alice.bal = 0. Why possible? This level does not hide uncommitted data. See Isolation Levels.
3. T2 concludes "Alice is broke, deny her withdrawal." Why matter? T2 is acting on a phantom value.
4. T1 hits an error and rolls back → Alice.bal restores to 500. Why? The = 0 never truly existed.

Verify. T2's decision was based on a value (0) that was never committed → dirty read, a real bug. At Read Committed, step 2 would instead show the last committed value 500, and T2 would allow the withdrawal — correct. Difference in T2's seen value: 0 vs 500. ✓

---

Example 7 — Non-repeatable read · cell M7 (Isolation)

Steps (Read Committed).

1. T1 BEGIN; reads Alice.bal → 500. Why? First read.
2. T2 BEGIN; Alice.bal := 400; COMMIT. Why? A committed change slips in between T1's two reads.
3. T1 reads Alice.bal again → 400. Why disagree? Read Committed only guarantees committed data at each read — it does not freeze the row for T1's whole life.

Verify. T1 saw 500 then 400: difference = 100, a non-repeatable read. Under Repeatable Read (via MVCC snapshots or Locking and 2PL) T1 would see 500 both times — the two reads agree. ✓

---

Example 8 — Phantom read · cell M8 (Isolation)

Steps (Repeatable Read, which does not stop phantoms).

1. T1 BEGIN; runs SELECT COUNT(*) FROM acct WHERE bal > 100 → suppose 2 (Alice 500, Bob 200). Why? Baseline range query.
2. T2 INSERT a new account Carol.bal = 300; COMMIT. Why? A new row enters the range — not an update to an existing row, an insert.
3. T1 re-runs the same range query → 3. Why grow? Repeatable Read protects rows it already read, but a brand-new row inside the range is a phantom.

Verify. Count went 2 → 3: difference = 1 new (phantom) row. Only Serializable forbids this (via range/predicate locks or serializable snapshots — see Concurrency Control). ✓

---

Example 9 — Degenerate inputs · cell M9

Reset: Alice.bal = 500, Bob.bal = 200.

(a) Empty transaction.

1. BEGIN; COMMIT; with no operations. Why test? Zero-length work must be legal.

Verify (a): nothing changes; Alice.bal = 500, Bob.bal = 200, sum 700 ✓.

(b) Zero-rupee transfer.

1. BEGIN; Alice.bal := 500 - 0 = 500; Bob.bal := 200 + 0 = 200; COMMIT. Why test? A ₹0 move is the limiting value of a transfer.

Verify (b): balances unchanged, sum 700 ✓. No CHECK risk since nothing crossed zero.

(c) Self-transfer Alice → Alice, ₹100.

1. BEGIN; Alice.bal := 500 - 100 = 400; then Alice.bal := 400 + 100 = 500; COMMIT. Why test? Same row on both sides — the two writes must net out.

Verify (c): Alice.bal = 500 (net zero), sum 700 ✓. Note the balance dipped to 400 inside the txn but that intermediate is hidden by isolation.

---

Example 10 — Exam twist: pick the weakest safe isolation level · cell M10

Reasoning steps.

1. Identify the anomaly that would break the invariant. Why? You choose the level by the specific anomaly, not by "safest wins."
2. Two txns inserting the same primary key is a write conflict on a unique constraint, not a phantom read. Why? The PRIMARY KEY / UNIQUE constraint (a Database Constraints feature) rejects the duplicate at write time regardless of isolation level.
3. Therefore even Read Committed is enough: the constraint itself blocks the duplicate; one insert commits, the other errors. Why not Serializable? Stronger isolation adds locking/aborts and lowers throughput for no extra safety on this invariant.

Verify. Correct answer: Read Committed (the weakest common level that, together with the unique constraint, holds the invariant). Serializable would also work but is strictly more expensive — the engineering rule is "weakest level that still holds your invariant." ✓ (This is the twin of the parent's mistake: "just use Serializable everywhere.")

---

Filling the matrix — did we cover everything?

Every cell M1–M10 has at least one worked example. ✓

---

Active recall

Recall Which log and why?

Crash before commit fixes with which log? ::: The undo log — no commit marker exists, so we erase partial work (Atomicity). Crash after commit fixes with which log? ::: The redo log — a commit marker exists, so we re-apply the promised change (Durability).

Recall Anomaly → weakest level that stops it

Dirty read ::: Read Committed. Non-repeatable read ::: Repeatable Read. Phantom read ::: Serializable.

Recall Consistency reality check

In Example 5 the DB committed happily but the sum became 800. Whose fault, and why? ::: The application's — "total money constant" was never a declared rule, so ACID-C (which only enforces declared constraints) cannot catch it.