knaxuslearn · build · ship

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

  1. 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

  2. Week 68 h
    • Row locks and SELECT ... FOR UPDATE
    • Conditional updates
    • SKIP LOCKED queues
    • Advisory locks

    Hand inThree transfer implementations from the worked reference

  3. 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

  4. 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 psql sessions. 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 UPDATE and the conditional UPDATE. 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.

RepoWhat to readWeek
postgres/postgressrc/backend/storage/lmgr/README and the isolation test specs5
jepsen-io/jepsenPublished analyses of how databases break their isolation claims5

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.

  1. 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.
  2. 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.
  3. Why does SELECT ... FOR UPDATE held across a network call hurt?
    The row stays locked while the client waits on the network, so other transfers queue behind it.
  4. 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.
  5. 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.