ACID: what transactions promise
| Property | Storage meaning |
|---|---|
| Atomicity | All or nothing — WAL records undo/redo for crash recovery |
| Consistency | Constraints hold after commit (FK, CHECK, app invariants) |
| Isolation | Concurrent txs behave like some serial order |
| Durability | Committed data survives crash — WAL fsync |
ACID is a bundle; NoSQL systems often pick a subset (e.g., Dynamo tunable durability).
Anomalies under concurrency
| Anomaly | What goes wrong |
|---|---|
| Dirty read | See another tx's uncommitted write |
| Non-repeatable read | Same row reads different values in one tx |
| Phantom read | Row set from a range query changes (new rows appear) |
| Write skew | Two txs read overlapping state, write disjoint rows, invariant breaks |
Write skew is the subtle one — Serializable isolation or explicit locking required.
ANSI isolation levels
| Level | Dirty | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | ✓ allowed | ✓ | ✓ |
| Read Committed | ✗ | ✓ | ✓ |
| Repeatable Read | ✗ | ✗ | ✓* |
| Serializable | ✗ | ✗ | ✗ |
*Postgres Repeatable Read also blocks many phantoms via MVCC — engine-specific nuance matters in interviews.
Implementation families (awareness)
- 2PL (two-phase locking) — shared/exclusive locks; serializable but contention-heavy
- MVCC — readers don't block writers; snapshot isolation (Postgres default: Read Committed)
- SSI (serializable snapshot isolation) — tracks rw-dependencies, aborts on dangerous orderings
Postgres SERIALIZABLE uses SSI; REPEATABLE READ is snapshot isolation without SSI edge detection.
Isolation vs replication
Strong single-node isolation does not imply cross-replica linearizability. Reading a stale replica violates read-your-writes even under Serializable locally — see Replication & HA and Distributed Systems → Consistency Models.
Senior-level signal
Default to Read Committed unless you can name the anomaly you're preventing. Escalate isolation before sprinkling SELECT FOR UPDATE — measure abort rates on Serializable.
Where this goes next
MVCC Visibility & Implementation shows how Postgres stores multiple row versions to deliver snapshot isolation.
Further Reading
Hands-On Tasks (Optional)
Low-setup exercises — schema drills, paper walkthroughs, or optional local installs. No autograding; the goal is interview fluency on how data is stored.
- Classify isolation anomalies15m
For dirty read, non-repeatable read, phantom read, and write skew — name the minimum isolation level that prevents each in Postgres. One line each.