Most developers can name the classic isolation anomalies: dirty reads, non-repeatable reads, phantoms. There is one more that matters in practice, because it lives in a level most people consider safe. It is called write skew.
Context
Take the standard example. A hospital requires that, for each shift, at least one doctor stays on call. Two doctors, Alice and Bob, are both on call tonight and both feel ill. Each opens the app and asks to go off call.
Each transaction checks the rule first:
sql
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM on_call WHERE shift_id = 42 AND active;
-- both transactions see 2, so both conclude it is safe
UPDATE on_call SET active = false
WHERE shift_id = 42 AND doctor_id = :me;
COMMIT;
Both transactions run in parallel. Each sees a snapshot where two doctors are on call. Each updates only its own row. Both commit.
The shift now has nobody on call.
Why the database allowed it
Snapshot isolation prevents two transactions from writing to the same row concurrently. If Alice and Bob had both updated the same row, one of them would have been aborted.
But here they updated different rows. There is no write-write conflict to detect. The problem is that each decision was based on a read that the other transaction invalidated, and snapshot isolation does not track that.
This is not a bug. In PostgreSQL, REPEATABLE READ is snapshot isolation. In SQL Server it is SNAPSHOT. Oracle calls its snapshot level SERIALIZABLE, which is a famously unhelpful name.
It also explains why the bug is so hard to catch. It needs two transactions overlapping in a window of milliseconds, so it never appears in a unit test and shows up in production under load.
Fix 1: use SERIALIZABLE and retry
PostgreSQL's SERIALIZABLE level is implemented with Serializable Snapshot Isolation. It runs transactions on snapshots like before, but it also tracks read-write dependencies between them, and when it detects a pattern that could not occur in any serial order, it aborts one of the transactions.
The aborted one gets SQLSTATE 40001. The contract is that the application must retry:
csharp
for (var attempt = 0; attempt < 5; attempt++)
{
try
{
await using var tx = await connection.BeginTransactionAsync(IsolationLevel.Serializable, ct);
var onCall = await connection.ExecuteScalarAsync<int>(
"SELECT count(*) FROM on_call WHERE shift_id = @shift AND active", new { shift }, tx);
if (onCall <= 1)
throw new InvalidOperationException("At least one doctor must stay on call.");
await connection.ExecuteAsync(
"UPDATE on_call SET active = false WHERE shift_id = @shift AND doctor_id = @doctor",
new { shift, doctor }, tx);
await tx.CommitAsync(ct);
return;
}
catch (PostgresException ex) when (ex.SqlState == PostgresErrorCodes.SerializationFailure)
{
await Task.Delay(Backoff(attempt), ct);
}
}
Two cautions. The detection is conservative, so occasional unnecessary aborts are normal. And without the retry loop, SERIALIZABLE just turns a silent anomaly into visible errors.
Read-only reports can use SERIALIZABLE READ ONLY DEFERRABLE, which waits for a safe snapshot and then runs without any risk of being aborted.
Fix 2: lock what you read
If you would rather avoid retries, make the dependency explicit by locking the rows the decision is based on:
sql
SELECT doctor_id FROM on_call
WHERE shift_id = 42 AND active
FOR UPDATE;
Now the second transaction blocks until the first commits, and then sees the updated state.
This works when the rows already exist. It does not protect against phantoms, that is, rows another transaction is about to insert, because you cannot lock something that is not there yet.
Fix 3: materialize the conflict
The most robust option is often to make the conflict physical. Lock a row that represents the thing being protected, such as the shift itself:
sql
SELECT id FROM shifts WHERE id = 42 FOR UPDATE;
Every transaction touching that shift's on-call list takes the same lock first. For rules like "no overlapping bookings" or "balance must not go negative across accounts", a unique constraint, an exclusion constraint or a single guard row can encode the invariant directly in the schema.
Other shapes of the same bug
On-call doctors are a textbook story, but the pattern hides in very ordinary features:
- Room booking. Check that no booking overlaps the requested time, then insert one. Two requests for the same slot both pass the check and both insert.
- Claiming a name. Check that the username is free, then insert it.
- A combined balance. A customer's rule is that the sum across two accounts must stay positive. Two withdrawals, one from each account, each checked against the sum, each writing a different row.
- Capacity. At most ten participants per event, checked by counting rows and then adding one.
The structure is always the same: read a set of rows, decide, and write something that changes the property you just checked. If two transactions can do that at once on a snapshot, the rule is not protected.
Constraints beat code
Whenever the rule can be expressed as a constraint, let the database enforce it. A unique index settles the username case with no help from isolation levels. For overlapping ranges, PostgreSQL has an exclusion constraint:
sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
room_id integer NOT NULL,
during tstzrange NOT NULL,
EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
Now two concurrent inserts for overlapping periods cannot both succeed. One of them fails with an exclusion violation, whatever the isolation level, because the check happens inside the index at the moment of the write.
For rules that no constraint can state, such as "at least one doctor", the guard row from the previous section is the next best option.
The same code behaves differently across engines
Isolation level names are not portable, and this is where migrations bite.
- PostgreSQL
REPEATABLE READ is snapshot isolation, and SERIALIZABLE detects dangerous patterns and aborts.
- SQL Server offers
SNAPSHOT, which allows write skew, and its SERIALIZABLE works with range locks, so conflicts show up as blocking and deadlocks instead of serialization failures.
- MySQL InnoDB defaults to
REPEATABLE READ, but locking reads and writes see the latest committed data and take gap locks, so it behaves differently from the PostgreSQL level with the same name.
- Oracle calls its snapshot level
SERIALIZABLE, and it allows write skew.
When code moves between engines, or when you only test on one, the invariants you believed the isolation level provided may no longer hold.
Finding it before production
Write skew needs two transactions to interleave at exactly the wrong moment, so make them do it on purpose. A test with two connections and a barrier forces both to read before either writes:
csharp
[Fact]
public async Task Two_doctors_cannot_both_go_off_call()
{
var barrier = new Barrier(2);
async Task GoOffCall(int doctorId)
{
await using var connection = await _db.OpenAsync();
await using var tx = await connection.BeginTransactionAsync(IsolationLevel.RepeatableRead);
var onCall = await connection.ExecuteScalarAsync<int>(
"SELECT count(*) FROM on_call WHERE shift_id = 42 AND active", transaction: tx);
barrier.SignalAndWait(); // both have read before either writes
if (onCall > 1)
await connection.ExecuteAsync(
"UPDATE on_call SET active = false WHERE shift_id = 42 AND doctor_id = @doctorId",
new { doctorId }, tx);
await tx.CommitAsync();
}
await Task.WhenAll(GoOffCall(1), GoOffCall(2));
Assert.True(await CountActiveAsync(shiftId: 42) >= 1); // fails here: the anomaly is reproduced
}
On RepeatableRead this test fails every time, which is the point: you now have a reproduction. Change the fix under test, whether it is serializable with a retry, a lock or a constraint, and watch it pass.
What it costs
SERIALIZABLE is not free. Detection is conservative, so some transactions abort that would have been fine, and the probability grows with the number of rows a transaction touches and with how long it stays open. Keep serializable transactions short, touch as few rows as you can, and declare reports as read-only. The cost is usually small compared with the cost of writing, and then debugging, a bespoke locking scheme.
Conclusion
Write skew is what happens when a transaction decides based on data it did not lock, and someone else changes that data without touching anything it wrote.
The question to ask about any invariant that spans more than one row is: which mechanism enforces this when two requests arrive at the same moment? If the honest answer is "the isolation level", check which level that is, and whether it really detects this.
References
- Transaction Isolation in the PostgreSQL documentation.
- Berenson et al., A Critique of ANSI SQL Isolation Levels (1995), where write skew was formally described.
- Ports and Grittner, Serializable Snapshot Isolation in PostgreSQL (VLDB 2012).