ACID Transactions
Prompt
Take a transaction — say moving money between two accounts — and walk me through what each letter of ACID is actually protecting you from. I don't want the expansions; I want the failure each one prevents.
How this round runs
This is a conversation that escalates — I'll keep asking "and what happens when…" until I find the edge of what you know. Tie each property to a concrete anomaly it prevents, and be honest about where your knowledge of isolation levels runs out rather than bluffing.
Model answer
I'll tie each letter to the specific failure it rules out, using the transfer — debit one account, credit the other.
Atomicity rules out the partial commit: the debit and credit are all-or-nothing, so a
crash between them can't leave money debited but never credited. Mechanically that's the
write-ahead log and rollback — on recovery the partial work is undone. Consistency means the
transaction moves the DB from one valid state to another with respect to its declared rules:
constraints, foreign keys, an invariant like "balance ≥ 0." A transaction that would violate
one is rejected, so queries never see a state that breaks the schema's promises. Durability
means once COMMIT returns, the change survives a crash — it's on stable storage (the log is
fsync'd), so a power loss a millisecond later doesn't lose it.
Isolation is the one with the most depth, because it's a dial, not a binary. It governs
what concurrent transactions can see of each other's in-progress work, and weaker settings
trade safety for throughput by permitting specific anomalies. Going a layer down: READ UNCOMMITTED permits dirty reads (you read another txn's uncommitted write that may roll
back). READ COMMITTED stops dirty reads but permits non-repeatable reads — read a row
twice, get two values because someone committed in between. REPEATABLE READ stops that but
classically permits phantoms — a range query returns new rows on re-execution. SERIALIZABLE
forbids all of them, as if transactions ran one at a time. My honest boundary: the names
map to that classic anomaly table, but real engines diverge — Postgres's REPEATABLE READ
is snapshot isolation and already blocks phantoms, and what "serializable" costs in practice
(locking vs. SSI) is engine-specific, which I'd verify before relying on it.
- Tied each letter to the specific failure it prevents, not the expansion of the acronym
- Reached for mechanism — write-ahead log/rollback for atomicity, fsync'd commit for durability
- Kept consistency (valid state per declared rules) distinct from isolation (inter-transaction visibility)
- Treated isolation as a dial and named the anomaly each level permits (dirty / non-repeatable / phantom)
- Honest boundary: the classic anomaly table is idealized; real engines (Postgres snapshot isolation) diverge
- Walk the isolation levels and which anomaly each one permits → dirty read, non-repeatable read, phantom, then serializable forbids all
- And what happens when two transfers touch the same two accounts concurrently under READ COMMITTED? → lost-update / write-skew risk depending on the read-modify-write pattern
- Does atomicity guarantee correctness across this transfer, or just that it's all-or-nothing? → just all-or-nothing; correctness is the consistency constraint plus your own invariants