Snapshot isolation can let two transactions commit even when their combined result violates a rule that each transaction checked before writing. The anomaly appears when both transactions read the same logical condition, then write different rows. Because their write sets do not overlap, ordinary write-write conflict detection has nothing to reject.
This is write skew. It matters at the boundary between application invariants and database isolation: a transaction may see a consistent snapshot and still participate in a final state that would have failed its own predicate.
A consistent snapshot does not serialize the decision
Consider an on-call table with a rule that at least one doctor must remain available:
CREATE TABLE on_call (
doctor_id bigint PRIMARY KEY,
available boolean NOT NULL
);Suppose rows for doctors 10 and 20 both have available = true. Two transactions start from snapshots containing that state. Each counts the available doctors and sees two. Transaction A marks doctor 10 unavailable; transaction B marks doctor 20 unavailable.
Conceptually, their work is:
SELECT count(*)
FROM on_call
WHERE available = true;
UPDATE on_call
SET available = false
WHERE doctor_id = ?;Each transaction made its decision from a coherent database state. Neither observed a partial commit. The problem is that the predicate read by both transactions spans more state than either transaction writes.
If the isolation implementation permits both commits because A writes row 10 and B writes row 20, the final state has zero available doctors. Both local checks passed; the cross-row invariant did not survive concurrent execution.
The conflict is a predicate, not a shared row
Lost updates are easier to expose because competing transactions commonly write the same item. A version column, compare-and-swap condition, or row lock can make that collision explicit.
Write skew has a different shape. The transactions conflict through the meaning of the data. Each write changes the truth of a predicate that the other transaction relied on:
count(available doctors) >= 1No single row necessarily carries that invariant. Treating row-level write conflict as the complete concurrency boundary therefore misses the dependency.
This distinction also separates write skew from a dirty read. Each transaction can read only committed data from its own snapshot. The anomaly does not require one transaction to observe another transaction’s uncommitted state.
Snapshot isolation and serializable isolation make different promises
Snapshot isolation is commonly implemented with multiversion concurrency control, but MVCC alone does not define one universal isolation contract. Product semantics and transaction settings determine which histories can commit.
Under snapshot isolation, a transaction generally reads from a stable snapshot and concurrent updates to the same versioned item are prevented from both succeeding. That combination blocks several familiar anomalies, but it does not by itself reject every dependency cycle involving predicate reads and disjoint writes.
Serializable isolation has a stronger contract: committed transactions must have an effect equivalent to some serial execution. A history in which both doctors independently leave the roster cannot satisfy that contract if each transaction requires another doctor to remain available.
The implementation used to provide serializability varies. A database may use predicate or range locking, serialization-graph analysis, or another concurrency-control scheme. Applications should depend on the documented isolation contract rather than infer guarantees from the presence of MVCC.
Row locks work only when the locked rows cover the invariant
One response is to lock every row whose state participates in the decision. For a small, stable set, a transaction might select relevant rows with a locking read before evaluating the rule. Concurrent transactions then contend on a common lock set.
That approach becomes fragile when the predicate describes a changing set. A query such as WHERE available = true does not automatically mean that every database protects the absence or future appearance of matching rows in the same manner. Range and predicate protection are database- and isolation-specific.
A more explicit design can introduce a row that represents the serialization point. For example, an on-call group row can be locked before membership changes. Transactions modifying separate doctor rows then deliberately contend on the shared group row.
This does not make the invariant self-enforcing. It creates a common synchronization object, and correctness depends on every mutation path participating in that protocol.
Constraints are strongest when the invariant fits their model
A database constraint can remove application timing from the correctness boundary when the invariant can be expressed directly by the database. Unique constraints are a common example: concurrent transactions can race to claim the same logical key, while the database arbitrates the final accepted state.
Not every cross-row rule maps cleanly to a declarative constraint. Aggregate conditions such as a minimum count often need a different representation, explicit serialization, or serializable transactions. Triggers can centralize enforcement in some systems, but their concurrency behavior still depends on the locking and isolation semantics surrounding the data they inspect.
The useful design question is not merely where the validation code runs. It is whether the mechanism that validates the predicate also prevents a concurrent transaction from invalidating the premise before commit.
Retries are part of serializable conflict handling
Serializable execution does not imply that every transaction can commit on its first attempt. Implementations that detect dangerous dependency patterns may abort one participant so the accepted history remains serializable.
Applications using such isolation need to treat serialization failure as a transaction-level outcome. A retry must rerun the whole transaction from a new snapshot, including the reads that produced the decision. Retrying only the final UPDATE would preserve a decision derived from state that the database has already rejected as unsafe.
Retry policy also needs a finite boundary. Persistent contention can keep producing conflicts, and callers may need an explicit failure after a bounded number of attempts rather than an unbounded loop.
The invariant defines the synchronization boundary
Write skew exposes a mismatch between row-shaped writes and invariant-shaped decisions. A transaction can be internally consistent, touch no row written by its peer, and still contribute to an invalid committed state.
The remedy follows the invariant’s actual scope. Serializable isolation can make the database reject non-serializable histories. Explicit locks can force transactions through a shared serialization point. A declarative constraint can let the database arbitrate a rule directly when the data model supports it.
What does not suffice is a check detached from the concurrency mechanism that protects its premise. If correctness depends on several rows remaining in a particular relationship, that relationship—not merely each row independently—is the unit that concurrency control must preserve.