Most teams never touch the isolation level. The database picks one, the tests pass, and everyone moves on. Then, months later, two requests arrive at the same moment and a business rule quietly breaks, with no error in the logs.
What Snapshot Isolation Promises
First, the good news. Snapshot isolation gives each transaction a consistent view of the data, taken as if frozen at one moment. Consequently, you avoid dirty reads and repeated reads that change under your feet. In PostgreSQL, the REPEATABLE READ level works this way, as this PostgreSQL walkthrough explains.
However, a consistent view is not the same as a correct outcome. Snapshot isolation only stops two transactions from overwriting the same row. It does not stop them from making decisions based on the same stale picture.
How Write Skew Happens
Consider a hospital rule: at least one doctor must stay on call. Alice and Bob are both on call. Each opens a transaction, counts the doctors on call, sees two, and decides it is safe to leave.
Because the two updates touch different rows, snapshot isolation sees no conflict. As a result, both commit, and the rule is broken. Notably, no serial execution could produce that result, since the second transaction would have seen one doctor and stayed.
Which Defaults Are Exposed?
So where does your default stand? The PostgreSQL documentation states that the default is ordinarily READ COMMITTED. MySQL’s InnoDB goes the other way, since its default is REPEATABLE READ. Be careful, though, because the MySQL page describes locking behavior that differs from PostgreSQL’s pure snapshot model. Therefore, test your own engine rather than assuming.
In PostgreSQL, our experiment below shows that both the default and the snapshot level let the anomaly through.
Testing Six Approaches on PostgreSQL
To compare fixes with real numbers, I ran the doctors scenario on PostgreSQL 16.15. Two concurrent sessions raced 1,000 times per approach, and I repeated the whole run twice. Aborted transactions were retried automatically.
The picture is clear. The default and snapshot levels failed in every trial. Meanwhile, all four protective approaches reached zero violations. However, they differ in cost, as the next chart shows.
| Approach | Violations | Cost observed |
|---|---|---|
| Read committed + FOR UPDATE | 0 | The second session waited; no retries |
| Guard row lock | 0 | The second session waited; no retries |
| Repeatable read + FOR UPDATE | 0 | One retry per trial |
| Serializable | 0 | About 1.5 retries per trial, slowest overall |
Choosing a Fix
In practice, the right choice depends on how well you know the rule. If you can name the rows involved, lock them with FOR UPDATE. Alternatively, if no single row represents the rule, lock a dedicated guard row, which acts like a mutex. These two options are explicit, but you must remember them in every code path.
On the other hand, PostgreSQL’s SERIALIZABLE level uses Serializable Snapshot Isolation, which, according to the pgdash article, detects dangerous read-write dependencies automatically. It protects code you did not think about, yet it demands retry logic. In our test it was also the slowest option, so measure it with your own workload before committing.
Write skew checklist
- Does any transaction read several rows, then update one based on what it saw?
- Is there a rule that spans rows, such as “at least one” or “total below a limit”?
- Do you know your engine’s real default level, and what it allows?
- Does every aborted transaction get retried safely?
What We Have Learned
Snapshot isolation prevents overwrites but not decisions made on a stale view, so write skew slips through. PostgreSQL defaults to read committed and MySQL InnoDB to repeatable read, and in our PostgreSQL test both the default and the snapshot level broke the on-call rule in all 1,000 trials. Row locks, a guard row and serializable isolation all fixed it, at different costs: waiting, one retry, or many retries. So identify which of your rules span several rows, choose a fix deliberately, and always plan for retries.
