Questions: Snapshot Isolation and Write Skew Anomalies
5 questions to test your understanding
Score: 0 / 5
Question 1 Multiple Choice
Under snapshot isolation, two transactions T1 and T2 both run concurrently. T1 reads rows X and Y and writes row X; T2 reads rows X and Y and writes row Y. A constraint requires that X + Y > 0. Both T1 and T2 read X=5, Y=5 and each sets their respective row to -3. What happens?
ABoth commits succeed; the constraint X + Y > 0 is violated (write skew)
BOne transaction is aborted because both wrote to overlapping rows
CBoth transactions are aborted because SI detects the constraint violation
DT1 succeeds and T2 is aborted because T2 started later
This is textbook write skew. T1 writes only row X and T2 writes only row Y — their *writes* do not overlap. SI's first-committer-wins rule only aborts a transaction if it writes to a row already modified by a committed concurrent transaction. Since the writes are to different rows, neither triggers the conflict detector. Both commit successfully, setting X=-3, Y=-3, which violates X + Y > 0. The constraint violation is invisible to SI because it emerges from the *combination* of writes that each appeared safe individually.
Question 2 Multiple Choice
A developer wants to prevent write skew in a snapshot-isolated database without upgrading to SERIALIZABLE. What technique can they use at the application level?
AUse INSERT ... ON CONFLICT to deduplicate writes
BWrap all reads in SELECT FOR UPDATE to acquire write locks on read rows
CUse ROLLBACK SAVEPOINT after each read to create checkpoints
DSet the transaction isolation level to READ COMMITTED for the affected transactions
SELECT FOR UPDATE acquires a write lock on the rows read, even though you're not yet writing them. This converts the read dependency into a write conflict that SI's first-committer-wins rule *can* detect. If T1 and T2 both SELECT FOR UPDATE on the rows they read, whichever transaction commits second will find those rows locked by the first, and will be aborted. The other options do not address the root cause: ON CONFLICT handles duplicate inserts (not read-write cycles), SAVEPOINT doesn't prevent concurrent commits, and READ COMMITTED offers weaker — not stronger — isolation.
First-committer-wins prevents *lost updates* — the case where two transactions both write to the *same* row and the second silently overwrites the first's change. Write skew is different: the two transactions write to *different* rows, so no write-write conflict is detected. The anomaly arises because each transaction's individual write is safe, but their combined effect violates a constraint that both read simultaneously. First-committer-wins doesn't fire because the writes don't overlap. Preventing write skew requires either SERIALIZABLE isolation or explicit locking (SELECT FOR UPDATE).
Question 4 True / False
Write skew can occur even when two concurrent transactions never write to the same database row.
TTrue
FFalse
Answer: True
This is the defining characteristic of write skew — it occurs precisely because the conflicting writes are to *different* rows. Both transactions read an overlapping set of rows, each makes a decision based on what it sees, and each writes to a distinct row. The violation emerges only when both writes are combined. The on-call doctor example illustrates this: Doctor A updates her own row, Doctor B updates his own row, and neither write conflicts with the other — yet together they leave zero doctors on call, violating the invariant.
Question 5 Short Answer
Why does snapshot isolation fail to prevent write skew, even though it prevents lost updates, non-repeatable reads, and phantom reads?
Think about your answer, then reveal below.
Model answer: SI prevents anomalies that arise from a transaction observing *another transaction's writes* (dirty reads, non-repeatable reads, phantoms) and prevents *same-row* write-write conflicts via first-committer-wins. Write skew, however, involves a *read-write dependency across different rows*: each transaction's read set overlaps with the other's write set, but not vice versa. SI has no mechanism to detect this 'anti-dependency' cycle. Each transaction sees a consistent snapshot, each write appears locally valid, and the conflict is only visible when both committed states are combined — which SI never checks.
The core issue is that SI tracks which rows transactions *write* (to enforce first-committer-wins) but does not track which rows transactions *read*. The serialization anomaly in write skew requires detecting that T1's write to row X conflicts with T2's read of row X (and vice versa). Serializable Snapshot Isolation (SSI), as implemented in PostgreSQL, adds this read tracking to detect and abort transactions involved in such cycles.