Day 86: Transactions & isolation levels
ACID and isolation levels
A transaction groups operations so they succeed or fail as one unit (Atomicity), leaves the database in a valid state (Consistency), behaves as if it ran alone even amid concurrent transactions (Isolation), and survives a crash once committed (Durability).
Isolation is the most nuanced of the four — full isolation (as if transactions ran one at a time) is expensive, so SQL defines weaker levels trading correctness guarantees for concurrency/performance.
- Read Uncommitted — can see other transactions' uncommitted changes (dirty reads); Postgres doesn't actually implement this level distinctly
- Read Committed (Postgres default) — only ever sees committed data, but a value can change between two reads in the same transaction (non-repeatable read)
- Repeatable Read — the same query returns the same rows throughout the transaction, no matter what else commits meanwhile
- Serializable — behaves as if transactions ran one at a time; strongest guarantee, may abort transactions that would violate it, requiring a retry
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... queries ...
COMMIT;This is Phase 7's consistency spectrum, made concrete
Choosing an isolation level is choosing a point on exactly the strong-vs-eventual-consistency trade-off from Day 45 — just within a single database instead of across replicas.
Key terms
- ACID
- Atomicity, Consistency, Isolation, Durability — the transaction guarantees a relational database provides.
- Isolation level
- How much a transaction is shielded from the effects of concurrently running transactions.
Under Read Committed (Postgres's default), why might the same SELECT run twice within one transaction return different results?
