A schedule with no row in common
Take an on-call rota with the invariant that at least one doctor is on call at any time. The table holds three doctors for tonight's shift, and two of them, Ada and Ben, are each feeling unwell at the same moment. Each transaction checks the rule before acting.
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM shift SELECT count(*) FROM shift
WHERE on_call AND night = '2026-07-29'; WHERE on_call AND night = '2026-07-29';
UPDATE shift SET on_call = false
WHERE doctor = 'ada' AND night = ...; UPDATE shift SET on_call = false
WHERE doctor = 'ben' AND night = ...;
COMMIT; COMMIT;
Both commits succeed on PostgreSQL at REPEATABLE READ, and the shift now has one doctor fewer than the rule allows — or none, if the count had been two rather than three. Nothing was lost and nothing was overwritten. Each transaction did exactly what the data it read entitled it to do.
The reason no engine mechanism fired is the whole answer. Snapshot isolation gives each transaction a consistent view as of its start and detects conflicts between writes: if both had updated Ada's row, the second would have been rejected on a first-updater-wins check. Here the write sets are disjoint — one row each — so there is no intersection to test. What actually conflicted was A's read of the count with B's write to it, and a plain snapshot implementation does not track reads at all, so that dependency is invisible.
This is write skew, and its general shape is a decision made by reading a set of rows and then writing a different row. Phantom-style anomalies are the same thing over rows that do not exist yet: two transactions each check that no booking overlaps a time range, and both insert.
What the isolation level buys, per engine
Naming the engine matters, because the same level name means different things and this is where interviewers listen hardest.
| Engine and level | Behaviour on the schedule above |
|---|
PostgreSQL REPEATABLE READ | Snapshot isolation. Both commit; write skew is permitted |
PostgreSQL SERIALIZABLE | Serialisable snapshot isolation tracks read-write dependencies and aborts one transaction with a serialisation failure |
PostgreSQL READ COMMITTED | New snapshot per statement, so the count can be stale by the time the update runs. Still permitted, and less predictable |
MySQL InnoDB REPEATABLE READ | Consistent snapshot for plain reads, no read-dependency tracking. Both commit |
MySQL InnoDB SERIALIZABLE | Plain reads are made locking, which blocks the second transaction and prevents it |
SQL Server SNAPSHOT | Row-versioning snapshot; write skew is permitted |
SQL Server SERIALIZABLE | Locking with key-range locks, so the read is protected and one transaction waits |
Two things fall out of that table. The engines that prevent this by locking do so because a serialisable schedule under two-phase locking holds read locks to commit, which is precisely the read protection snapshot isolation lacks. And PostgreSQL's SERIALIZABLE prevents it without holding those locks, by watching for the dependency pattern and aborting rather than blocking — which means the anomaly turns into an error your application must handle rather than into a wait.
Making the invisible conflict physical
If you are not moving to serialisable, the fix is to give the engine something it can see, and there are three honest ways to do that.
Take a locking read over the rows your decision depends on, so that SELECT ... FOR UPDATE on the shift rows for that night makes the second transaction wait rather than proceed from a stale count. This works because it converts a read dependency into a lock the engine understands. Its cost is that you must lock every row the decision reads, which is easy to get wrong, and lock ordering across transactions now matters, so you have traded an anomaly for a deadlock risk.
Materialise the constraint into a row. If the invariant is about the shift, give the shift its own row and make every transaction that changes its composition update that row. Now both transactions write the same row, the write sets intersect, and the ordinary first-updater-wins check does the work. This is deliberately introducing a point of serialisation, which is a throughput cost you are choosing on purpose.
Or express the invariant declaratively where the shape allows it. A unique index enforces at most one of something across concurrent inserts, because index enforcement happens outside the snapshot. PostgreSQL's exclusion constraints extend that to non-equality conditions such as overlapping ranges, which resolves the double-booking version of this problem completely. What cannot be expressed this way is anything the SQL standard's CHECK would need to see across rows, and that limitation is the reason write skew exists as a category at all.
Why the rule you cannot write as a constraint is the one that breaks
The invariants that survive concurrency are the ones the database can enforce for you: a primary key, a foreign key, a uniqueness rule, a per-row check. Those are safe because the engine evaluates them physically, at write time, outside any transaction's snapshot. Every invariant that spans rows — a minimum count, a balance across accounts, no overlap in a schedule, at most one active record per customer — lives only in your application code, and application code reads a snapshot. That is the fault line, and knowing where it runs is worth more than memorising the anomaly names.
The other half of a strong answer is that choosing SERIALIZABLE on a snapshot-based engine is not free correctness. Transactions will be aborted with serialisation failures under load, and the application must retry the whole transaction from the beginning rather than resuming it. If your code treats a database error as a request failure, turning the level up converts a silent data problem into a visible reliability problem, which is better but is not nothing. Build the retry loop first, keep transactions short so the abort rate stays low, and only then raise the level.
Snapshot isolation validates writes against writes, so a decision made by reading one set of rows and writing another is unprotected by construction. Either give the engine a physical conflict to see, or turn on true serialisability and be ready to retry the transactions it aborts.
© 2026 Preptima. Originally published at preptima.com (opens in a new tab).