Learn the vocabulary of a subtle transaction anomaly that snapshot isolation alone doesn't catch.
0 / 5 completed
1 / 5
At standup, a dev mentions two transactions each reading the same overlapping data, each deciding their write is safe based on that read, then each writing a different row, together silently violating an invariant that spans both rows. What is this anomaly called?
Write skew occurs when two transactions each read the same overlapping data, each decide their own write is safe based on that read, and then each write a different row, together silently violating an invariant that spans both rows. A dirty read involves seeing an uncommitted value, which is a different and unrelated anomaly. Write skew is subtle precisely because neither transaction's individual read or write looks unsafe in isolation.
2 / 5
During a design review, the team wants to prevent write skew by forcing two transactions that read the same data to serialize against each other, rather than letting them proceed concurrently based on a shared snapshot. Which capability supports this?
Explicit row locking, such as SELECT ... FOR UPDATE, forces two transactions that read the same shared data to serialize against each other, so the second transaction can't proceed on a now-stale assumption. Relying only on snapshot isolation's shared read view lets both transactions proceed concurrently, each unaware the other is about to violate the same invariant. This explicit locking is one standard way to close the gap that plain snapshot isolation leaves open.
3 / 5
In a code review, a dev notices that under plain snapshot isolation, neither of two concurrent transactions ever detects a conflict, since each only writes a row the other one never touches. What does this represent?
Snapshot isolation's inability to detect a write-skew conflict shows up precisely because each transaction writes a row the other one never touches, so the database's usual conflict detection, which looks for the same row being written twice, never triggers. Assuming snapshot isolation reliably detects this kind of conflict misunderstands why write skew is considered one of its known blind spots. This gap is exactly why an invariant spanning multiple rows needs a stronger guarantee than plain snapshot isolation alone provides.
4 / 5
An incident report shows two on-call engineers each independently checked the schedule, saw at least one other person listed as covering, and both removed themselves from the on-call rotation, leaving nobody on call at all. What practice would prevent this?
Enforcing the invariant with explicit locking or a serializable isolation level ensures both removal transactions can't both succeed based on a read that's since become stale, since one would be forced to wait on or reread after the other. Letting each transaction proceed independently based on its own snapshot is exactly the pattern that left nobody on call in this incident. This is the textbook write-skew scenario used to illustrate why an invariant spanning two rows needs more than plain snapshot isolation.
5 / 5
During a PR review, a teammate asks why the team adds explicit row locking around an on-call removal instead of trusting that snapshot isolation alone will keep the 'at least one person on call' invariant intact. What is the reasoning?
Snapshot isolation lets each transaction read a consistent view of the data but doesn't detect a conflict between disjoint writes, so two transactions can each individually look perfectly safe while together violating a multi-row invariant. Explicit row locking closes that gap by forcing the transactions to serialize against each other on the rows the invariant actually depends on. The tradeoff is the reduced concurrency that comes from holding a lock across the shared rows for the duration of each transaction.