Consistency & Isolation Models

Choose an isolation level

Transactions on one database read and write the same rows: rewrite the operation, push the rule into a constraint, or pick read committed, snapshot isolation or serializable? Four questions, cheapest answer first.

Decide on consistency

Data is replicated across machines or regions: does your rule need coordination at all, can you split the budget so replicas act alone, or do you need real-time ordering? Five questions, cheapest answer first.

Anomaly count equals rate times window

Count conflicting operations inside a window and compare with λ × W, from one SQL statement to replica lag under load.

Measure the Dekker pattern and the price of `r + w ≥ d`

Two regions with an 80 ms delay run the Dekker test: local reads and writes can both read 0; only waiting for the other copy removes it.

Count the shapes with a tiny dependency-graph checker

Build ww, wr and rw edges from small histories and classify lost update, write skew and read skew by their cycles.

Reproduce the lost update at Read Committed, then fix it three ways

Lose an update under Read Committed in PostgreSQL, then fix it four ways.

Same name, different promise: compare engines

One phantom test at the level PostgreSQL and MySQL both call Repeatable Read, with different results.

Write skew under Repeatable Read, and the two cures

Two transactions each check a total, then withdraw from different accounts: write skew under Repeatable Read, and the two cures.

Read-your-writes with a replica, and a lost token

Write on a PostgreSQL primary, read from a lagging replica, then carry a log-position token, then lose it.

Anomaly rate equals window times rate

Stale reads from a lagging replica against λ × W, for three lags, then reads routed to the primary after a write.

LWW versus a G-counter under clock skew

Three replicas, one clock 3 s ahead, 1,000 increments: last-writer-wins and max lose them; a G-counter keeps all.

R + W > N is not recency

A toy N = 3 register: a failed W = 2 write leaves reads with R = 2 going back and forth.

Count I-confluence in your own schema

Classify every constraint of a schema as invariant-confluent or not, and price the escrow for its bounds.

Run an Elle-style check on your own database

A list-append workload against PostgreSQL at three isolation levels, with the dependency graph rebuilt and searched for cycles.

Interview questions

Short questions on consistency, such as isolation levels and their anomalies, linearizability, causal consistency, session guarantees, CRDTs, quorums, CAP and coordination, each with an answer you could give aloud and a link to the full explanation.

Scenarios

Longer problems on consistency, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.