Course contents
On this page
Write skew and conflicting transactions
Deeper reading after the guided lesson: write skew and conflicting transactions.
Start with the guided lesson. These sections develop the mechanisms and tradeoffs beyond that first example.
Two snapshots can each look safe
Two doctors are on call. Each transaction reads both doctors and verifies that someone else remains available. One transaction marks Alice off call; the other marks Bob off call. If each sees the original snapshot and their writes affect different rows, both can commit under a snapshot-isolation model that detects only direct write/write conflicts. No doctor remains available.
The anomaly is write skew. Each transaction made a locally reasonable decision from a consistent snapshot, but the combined result violates the shared invariant. This demonstrates why multiversion concurrency control, snapshot isolation, and serializability are distinct concepts.
Trace the write-skew history
Let the invariant be Alice.on_call OR Bob.on_call. Initially both values are true. Transaction T1 reads both and plans to set Alice false. T2 reads both and plans to set Bob false. Their write sets are disjoint.
| Step | T1 | T2 |
|---|---|---|
| 1 | Reads Alice=true, Bob=true | |
| 2 | Reads Alice=true, Bob=true | |
| 3 | Writes Alice=false | |
| 4 | Writes Bob=false | |
| 5 | Commits | Commits |
No serial order of these two correctly checking transactions permits both to leave: the second would observe that the other doctor had already left. The accepted combined history therefore cannot be explained by a valid serial order under the stated transaction logic.
A remedy can protect the shared predicate through serializable conflict detection, explicit locking, or a carefully chosen shared guard record. The guard can intentionally serialize this invariant. A more distributed business model may alter the invariant, but that is a product change rather than a transparent concurrency optimization.
Serializable validation and dependency tracking
Some engines detect dangerous read/write dependencies and abort transactions that could create a serialization anomaly. Others validate read sets against current versions. Timestamp-ordering methods impose additional rules. The implementation must protect absence and ranges as well as rows actually returned.
Tracking reads has costs in metadata, validation, and false or conservative conflicts. A broad scan can create a large dependency footprint. An index can help express a narrower range, but a correct serializable mechanism must still account for new rows entering that range.
Retries are part of the application-facing contract. A transaction aborted for serialization should restart its logic against a new snapshot, within a bounded retry policy. Repeating only its final writes with stale decisions does not resolve the anomaly. External side effects must not be duplicated during re-execution.
Indexes and update representation
An update can create a new version in the same page, another page, or a separate version structure. Secondary indexes may reference logical rows or particular versions. The engine must avoid returning invisible versions and must eventually remove obsolete index entries without losing valid access paths.
Physical presence in an index does not prove visibility. A unique check must consider concurrent in-progress writes differently from an ordinary snapshot read. Otherwise two transactions could each fail to see the other’s tentative identity and both commit duplicates.
Version-chain length affects lookup work. A highly updated hot row with an old snapshot can require traversing many versions. Maintaining direct visibility metadata or pruning safe segments can help, but the engine must coordinate with concurrent readers and recovery. MVCC changes where work occurs rather than abolishing synchronization.
Serialization graphs explain write skew
Return to the on-call example. T1 reads Bob’s old on-call value, and T2 later writes Bob off call. This creates a read/write antidependency from T1 to T2: in a serial interpretation, T1 needs to precede T2 to explain its read. T2 reads Alice’s old value, and T1 writes Alice off call, creating the reverse antidependency. The cycle shows why no serial ordering explains both successful decisions.
T1 --read old Bob before T2's write--> T2
T2 --read old Alice before T1's write--> T1These are serialization dependencies, not wait-for edges. Both transactions can execute without blocking and still form the cycle. Preventing deadlock therefore does not prevent write skew. Similarly, a consistent snapshot prevents dirty observations but does not eliminate every dependency involving concurrent transactions.
A guard record can intentionally force a direct conflict. Both on-call changes update one schedule-version row, so one must wait or retry according to the engine’s rules. This may be a compact solution for a small shared invariant. If every hospital schedule shares one global guard, unrelated work becomes unnecessarily serialized. Guard identity should match the invariant’s actual scope.
Serializable snapshot isolation tracks patterns of dependencies and aborts when its rules indicate a dangerous structure. It need not build and retain a complete graph forever. Its implementation can conservatively abort some histories that would turn out to be serializable, trading concurrency for practical tracking costs. A serialization failure is a permitted outcome under such an implementation, and the application must restart the whole decision.
Uniqueness is not an ordinary snapshot read
Imagine T1 and T2 both insert username nova. Each ordinary snapshot read can fail to see the other’s tentative row. If uniqueness checking reused only that visibility rule, both could decide the name is available. Enforcing the constraint needs conflict coordination against in-progress candidates, often causing one insertion to wait or fail after the other’s outcome is known.
Deletion and reinsertion add more cases. A transaction deleting nova has not necessarily released the name to unrelated transactions before commit. An update changing the key can require coordination over old and new identities. A secondary index may contain multiple physical entries for versions, and its constraint path must distinguish historical entries from unresolved conflicting writes.
This is why a unique index is more than a faster implementation of SELECT followed by INSERT. It embeds a concurrency rule at the trust boundary. Applications should use that rule where it expresses the invariant instead of attempting to recreate it with stale reads. The engine still needs to define how constraint errors interact with transaction abort and retries.
Foreign keys have related visibility problems. A child insert must not commit against a parent that concurrently disappears in a way forbidden by the constraint. Versioned reads alone do not decide this relationship. The engine combines visibility, locks or validation, and constraint semantics so the accepted history remains valid.