Roadmaps / Databases / Phase 2
Phase 2 of 4 · Study weeks 5-8
Transactions and concurrency
This phase makes the second checkpoint possible: a transfer that stays correct when 100 clients fight over the same rows.
Topics every week
- Week 58 h
- ACID in practice
- Multiversion concurrency control (MVCC)
- Isolation levels and the anomalies each allows
- Snapshots
Hand inA table of anomalies (dirty read, lost update, write skew) reproduced
- Week 68 h
- Row locks and
SELECT ... FOR UPDATE - Conditional updates
SKIP LOCKEDqueues- Advisory locks
Hand inThree transfer implementations from the worked reference
- Row locks and
- Week 78 h
- Deadlocks and lock ordering
- Serialisable isolation and retries
- Timeouts
- Idempotent retries
Hand inThe seeded deadlock found in the log and the lock-order rule
- Week 88 h
- Stress testing for invariants
- Checking the rules after a run
- Transactions in application code
- Predicted against measured
Hand inCheckpoint 2: the stress report and the rule checks
Practice projects
- Practice 1, the anomaly table (Week 5): reproduce a lost update and a write skew with two
psqlsessions. Done when: each anomaly has the exact statements and the isolation level that prevents it. - Practice 2, the three transfers (Week 6): implement read-then-write,
FOR UPDATEand the conditionalUPDATE. Done when: a table shows throughput, p99 and rule violations for each under 100 clients. - Practice 3, the deadlock (Week 7): lock two accounts in opposite order. Done when: the log shows the deadlock and your id-order rule makes it impossible and is tested.
- Optional, the job queue: build a queue with
SKIP LOCKED. Done when: 20 workers process 100,000 jobs with no job done twice.
GitHub repos to study
Existence and last commit were checked on 22 Nov 2026 by shallow clone. Every repository below had a commit in the last 12 months.
| Repo | What to read | Week |
|---|---|---|
| postgres/postgres | src/backend/storage/lmgr/README and the isolation test specs | 5 |
| jepsen-io/jepsen | Published analyses of how databases break their isolation claims | 5 |
Milestone checkpoint
Build Checkpoint 2. Write the stress client and the rule checker (negative balance, sum unchanged, no duplicate transfer) first. Add the three transfer versions, run each for 5 minutes, then seed the deadlock and the write skew. Hand in the stress report, the rule check output, ADR-3 (the transfer statement and lock order) and ADR-4 (the isolation level and the retry policy).
Check yourself
Answer each question in your head first, then open it to check.
What does MVCC give readers, and what does it cost?
Readers do not block writers because each sees a snapshot. The cost is old row versions that vacuum must clean up.What is write skew, and which isolation level prevents it?
Two transactions each read overlapping data, make decisions that are valid alone and write different rows, so together they break a rule. Serialisable isolation prevents it, and the application must retry on failure.Why does
SELECT ... FOR UPDATEheld across a network call hurt?The row stays locked while the client waits on the network, so other transfers queue behind it.How do you make a retry safe?
Give each request an idempotency key with a unique constraint, so the second attempt is rejected or returns the first result.How do you prove the rules held after a stress test?
Run a query that checks the rules over all rows after the run, and compare the total to the starting total.