Exercises — Transactions — ACID properties (each one in detail)

• Atomicity → (c) crash leaving a transaction half-done. All-or-nothing on disk.
• Consistency → (d) a transaction breaking a declared rule (a CHECK, foreign key, invariant).
• Isolation → (a) another transaction at the same time (concurrency).
• Durability → (b) a crash losing committed work.

The one-line separators: A = my own crash, C = my own rules, I = other people, D = crash after I said "done".

1. Undo log. To reverse a change, you must know the old value to put back. Rollback replays undo records backwards.
2. Redo log. To re-apply a committed change, you need the new value. Recovery replays redo records forwards.

Memory hook: undo = old = Atomicity, redo = new = Durability. See Undo and Redo Logs.

False. Durability is only promised at COMMIT, and even then via the log first (WAL/fsync), not necessarily the data page. Before commit the write lives in a RAM buffer and can be lost. See Write-Ahead Logging (WAL).

The transaction never committed, so recovery treats it as failed and replays the undo log backwards:

• Undo record Alice.bal = 500 is applied → Alice.bal = 500.
• Bob was never touched → Bob.bal = 200.

Final: Alice.bal = 500, Bob.bal = 200. Total money = $500+200=700$, exactly what we started with. Why? Atomicity conserves the system — partial work is erased.

The transaction committed, so recovery replays the redo log forwards:

• Alice.bal := 400
• Bob.bal := 300

Final: Alice.bal = 400, Bob.bal = 300. Total = $400+300=700$. Why? Durability guarantees committed work is never lost — the redo log is the durable proof, even though the data pages were stale.

1. Transaction tries Alice.bal := 50 - 100 = -50.
2. This violates CHECK (bal >= 0).
3. Consistency rejects it → the whole transaction rolls back (Atomicity does the undo).
4. Alice.bal stays 50.

Final: Alice.bal = 50, transfer denied. Note how C detects the violation and A undoes the partial work — the two cooperate. See Database Constraints.

This is a dirty read: T2 read T1's uncommitted value that was later rolled back — a value that "never really existed". T2 acted on a phantom of reality.

Weakest level that stops it: Read Committed — it only ever shows committed values. See Isolation Levels.

The ladder is cumulative: each higher level stops everything below it too. Look at the staircase in the figure — each step blocks one more anomaly.

• Dirty read: you read uncommitted data.
• Non-repeatable read: you re-read a row and its value changed (someone updated+committed between your reads).
• Phantom read: you re-run a range query and new rows appear (someone inserted). See Concurrency Control.

A new row appearing in a range/predicate query is a phantom read (not a non-repeatable read — no existing row changed value; a new one entered the range).

It disappears at Serializable, the only level that prevents phantoms. See Locking and 2PL for how range/predicate locks (or MVCC snapshots) achieve this.

BEGIN;
  UPDATE stock SET qty = qty - 1 WHERE item = 'X' AND qty >= 1;
  INSERT INTO orders(item, buyer) VALUES ('X', 'Bob');
COMMIT;

1. Crash mid-way → Atomicity: undo log rolls back the decrement; no orphan half-order.
2. Negative stock → Consistency: a CHECK (qty >= 0) constraint (or the qty >= 1 guard) rejects the transaction.
3. Concurrent buyer reading between read & write → Isolation: an appropriate level (Repeatable Read + locking, or Serializable) prevents the lost-update/dirty-read.
4. Power loss after "order placed" → Durability: WAL flushed the redo log at commit; recovery replays it.

All four letters map cleanly onto the four risks — that is the whole point of ACID.

The missing property is Isolation. Interleaving:

1. Transfer: Alice.bal := 400 (debited), not yet committed.
2. Report reads Alice = 400 and Bob = 200 → sum = 600.
3. Transfer: Bob.bal := 300, commits.

The report saw a half-completed transaction: Alice's debit but not Bob's credit → 100 appears missing. Atomicity, Consistency, Durability were all fine — the transfer did finish and conserve money. The report simply observed an intermediate state, which only Isolation forbids. A serializable read would see either (500, 200) or (400, 300), both summing to 700.

Stronger isolation is bought with more locking and more aborts/retries. Serializable must block or abort transactions to forbid phantoms; under high concurrency this serialises work that could have run in parallel, causing lock waits, deadlocks, and retry storms → throughput falls.

The rule: pick the weakest isolation level that still preserves your invariants. Enumerate the anomalies your business logic actually cannot tolerate, find the lowest level that blocks exactly those, and stop there. Serializable is correct only when you genuinely need phantom-freedom. See Isolation Levels and MVCC (which gives snapshot reads without read locks, softening this trade-off).

• ACID-C = the database obeys its declared integrity rules on a single logical state (CHECK, foreign keys, invariants). It is a property of one database.
• CAP-C = all nodes return the same data at the same time — a replication agreement property across many nodes.

They share a word but measure different things: rule-keeping vs read-agreement.

A single-node ACID database has nothing to replicate, so CAP-C is trivially satisfied (there is only one copy — every read sees it). The CAP trade-off (choose Consistency or Availability under a partition) only appears once you have multiple replicas that a network partition can split. See CAP Theorem and Two-Phase Commit (which extends atomicity across nodes, a distributed-Atomicity problem, not distributed-Consistency).

WAL's invariant is "log first, data later": before COMMIT returns success, the redo log record is flushed to durable storage (fsync completes). Only after that does the client hear "OK". The data pages may still be stale in RAM.

Proof a crash right after cannot lose the commit:

1. "OK" was returned ⟹ the redo record is already on durable storage (by the invariant).
2. Crash wipes RAM (possibly stale data pages) but not durable storage.
3. On restart, recovery scans the redo log, finds the committed record, and replays it forward onto the data pages.
4. ⟹ the change is reconstructed. The commit survives.

The linchpin is step 1: "OK" is never returned before the log is durable, so a durable "OK" always has a durable log record behind it. See Write-Ahead Logging (WAL) and the flow below.

• Undo = old values = Atomicity = my own crash. Redo = new values = Durability = crash after "done".
• Isolation anomalies climb a staircase: dirty (Read Committed) → non-repeatable (Repeatable Read) → phantom (Serializable).
• Pick the weakest isolation that keeps your invariants — strength costs throughput.
• ACID-C ≠ CAP-C; 2PC ≠ CAP-C. Same words, different worlds.