Questions / Quant development, SQL & data pipelines
A stable snapshot can still allow write skew
In PostgreSQL 16 at REPEATABLE READ, two rows mark risk services A and B as enabled. The rule requires at least one enabled service. Two overlapping transactions each read both enabled rows, then one disables A and the other disables B. They update different rows; there is no enforcing constraint, lock, or other transaction. Can both commits succeed and leave both disabled?
Answer it in the app It's free. You get hints, a second try if you miss, and a follow-up question.
Show the answer and worked solution
Answer: D. Yes; disjoint updates can produce write skew.
- Each transaction reads a snapshot in which the other service is enabled.
- One writes A and the other writes B, so their writes target different rows.
- Both can commit under this setup, leaving zero enabled services despite each local check passing.
Interview shortcut. Snapshot isolation allows write skew.
Takeaway. Test isolation against the business invariant, not just repeatable reads.
Why the other choices fail
- Yes; REPEATABLE READ allows dirty reads. PostgreSQL never allows dirty reads. Both transactions see consistent snapshots, and disjoint updates still break the invariant.
- No; reading both rows write-locks both services. Ordinary snapshot reads do not acquire the row-write locks needed to serialize the two disables.
- No; the snapshot rechecks peer status at commit. A repeatable snapshot can preserve the old peer value. It does not re-run this cross-row rule at commit.
In the app this has a follow-up where one thing about the setup changes. Try the follow-up.