Snapshot isolation detects and rejects two transactions that write the same row after both read it. Explain why that same mechanism does not stop two transactions whose reads overlap but whose writes touch different rows.
answer
- SI conflicts on write sets only
- Write skew has empty write-set intersection
- Cycle of read-write anti-dependencies
- Pivot transaction: in-edge and out-edge
- Fix = put reads into the conflict footprint
basics
~20 sSnapshot isolation's conflict detector compares write sets. Two transactions writing different rows have an empty write-set intersection, so there is nothing to reject — even though each transaction's decision depended on rows the other changed. Reads are never part of the conflict footprint.
solid answer
~60 sSnapshot isolation gives each transaction a consistent point-in-time view and adds exactly one conflict rule: if two concurrent transactions write the same row, only the first to commit succeeds (first-committer-wins, or the equivalent first-updater-wins via row locks). That rule is defined over **write sets**. Write skew has an empty write-set intersection by construction. The transactions conflict on **reads**: T1 read a row that T2 wrote, and T2 read a row that T1 wrote. In serialization-graph terms each transaction has a read-write (anti-)dependency on the other, forming a cycle, so the execution is non-serializable. Snapshot isolation never builds that graph — it only compares writes — so the cycle is invisible. The deeper reason is that snapshot isolation's guarantee is about what you *observe*, not about whether your observation is still valid when you commit. Nothing revalidates the reads. Preventing write skew therefore requires making reads participate in conflict detection — via serializable snapshot isolation's read tracking, or by taking locks on the rows you read, or by turning the rule into a constraint the index enforces.
code
text · 10 lineslost update T1 --ww--> T2 caught: same row written
(first-committer/updater wins)
write skew T1 --rw--> T2
T2 --rw--> T1 cycle of anti-dependencies
no write-set overlap -> undetected
SSI detection T_pivot has both an incoming rw edge and
an outgoing rw edge to concurrent transactions
-> abort one of them (serialization failure)go deeper
Say it plainly: the engine only complains when two transactions write the same row, and here they write different rows, so nothing complains.
Add that snapshot isolation's rule is defined over write sets while the real conflict is over reads, and that snapshots make each transaction blind to the other's work by design.
Use the dependency-graph framing — a cycle of read-write anti-dependencies that write-set comparison cannot see — and enumerate the real fixes: SSI read tracking, locking the read set, materializing the conflict, or a constraint.
Discuss the theory (the pivot structure that makes SSI possible) and the engineering trade: optimistic read tracking with aborts versus pessimistic locking versus a schema change that manufactures a write-write conflict, chosen by contention profile and retry tolerance.
## What snapshot isolation actually promises Two properties, no more: 1. **Snapshot reads.** Every read in the transaction is answered as of one instant, so the transaction sees a consistent state and no reader-visible anomaly (no dirty read, no non-repeatable read, no read phantom). 2. **Write-write conflict detection.** If two concurrent transactions write the same row, at most one commits. Implementations do this either as *first-committer-wins* (checked at commit) or *first-updater-wins* (an exclusive row lock taken at update time, with the loser aborting if the winner committed after its snapshot). Both properties are stated over rows the transaction *writes*, or over what it *sees*. Neither says anything about whether a row you merely read is still in the state your decision assumed. ## Why that leaves a hole Serializability is defined by the dependency graph over concurrent transactions. There are three edge types: - **write-write**: both write the same object. - **write-read**: one reads what another wrote. - **read-write (anti-dependency)**: one reads an object that another subsequently writes — the reader's view becomes stale. An execution is serializable when this graph is acyclic. Snapshot isolation eliminates write-write cycles by its conflict rule, and eliminates write-read edges between concurrent transactions by construction (a snapshot transaction never sees a concurrent transaction's writes at all). What it does not touch is **read-write anti-dependencies**, and the characteristic write-skew execution is a cycle made entirely of two of them: T1 read what T2 wrote, T2 read what T1 wrote. Theory tells you exactly what to look for: every non-serializable execution under snapshot isolation contains a transaction with an incoming and an outgoing anti-dependency to two concurrent transactions — the "pivot". That structural result is what serializable snapshot isolation implementations use to detect and abort the offending transaction. ## Why the snapshot itself makes it worse, not better It is tempting to think a stronger read guarantee helps. It does the opposite in one sense: under snapshot isolation each transaction is *guaranteed* not to see the other's work, so both proceed confidently on a view that is by design blind to the concurrent change. Under a weaker level such as read committed, a re-read might have caught it by luck. The snapshot removes the luck without adding the safety. Note also that a common intuition — "my transaction re-read the count right before writing, so it's fine" — fails twice over: under snapshot isolation the re-read returns the same snapshot value, and even without a snapshot the other transaction may commit in the window between the re-read and your commit. ## What actually closes it All fixes work by making reads part of the conflict footprint: - **Serializable snapshot isolation (SSI)**: the engine records which rows and index ranges each transaction read, watches for the pivot pattern, and aborts a transaction in the cycle. No blocking, but serialization failures the application must retry. This is the only option that keeps the invariant arbitrary and the code unchanged apart from the retry loop. - **Explicit locking of the read set**: take exclusive or share locks on the rows the decision depends on (a locking read over the doctors of the shift), so the second transaction blocks and then re-evaluates against the committed state. Correct, but it reintroduces blocking, requires consistent lock ordering to avoid deadlocks, and is only as good as the least careful caller. - **Materializing the conflict**: create a row that represents the contended resource — the shift, the time slot, the customer's overdraft group — and have every transaction lock or update that row. This converts disjoint writes into a genuine write-write conflict, which snapshot isolation already detects. It is a schema change made specifically to give the engine something to conflict on. - **Declarative constraints**: when the invariant fits an index — uniqueness, non-overlapping ranges — the index enforces it with no read-then-write window and no reliance on isolation level. What does *not* work: retrying without changing the mechanism, shortening the transaction (it shrinks the window, never closes it), adding a stronger read guarantee that is still snapshot-based, or checking again immediately before the write. ## The sentence to leave the interviewer with Snapshot isolation conflicts on what you wrote; write skew is a conflict on what you read. Until reads are in the conflict footprint — by tracking them, locking them, or replacing them with a constraint — two individually valid decisions can commit into a state neither would have permitted.
- What is 'materializing the conflict' and when would you use it?You introduce a row that stands for the contended resource — one row per shift, per time slot, per constraint group — and require every transaction that depends on the invariant to lock or update it. Because all the competing transactions now write the same row, snapshot isolation's existing write-write conflict detection rejects one of them. It is useful when you cannot run serializable, or need deterministic blocking behaviour, and it is worth the extra schema and the contention it deliberately creates on that row.
- Does taking a locking read on the rows the decision depends on fully prevent write skew?It prevents it for existing rows, because the second transaction blocks until the first commits and then evaluates the committed state. It does not cover the insert-shaped variant, where the decision depends on rows that do not exist yet — locking the rows you found protects nothing against a new row appearing, so you need range locking or a constraint. It also depends on every code path taking the lock, and consistent lock ordering to avoid deadlocks.
saying these in an interview costs you the question
- Saying snapshot isolation prevents it because reads are consistent
- Proposing a re-read just before the write as the fix
- Claiming shorter transactions eliminate the race rather than narrowing the window
- Confusing first-committer-wins with a general conflict detector that covers reads
- Asserting that retrying the transaction unchanged will eventually produce a correct result