Four SQL levels, each blocking one more anomaly
SERIALIZABLE means the result equals some one-at-a-time order. It costs waiting or aborts, so most databases default lower.
Same level name, different promise
Bad: MySQL REPEATABLE READ
Two ₹200 top-ups both read ₹500 and write ₹700. ₹200 vanishes.
Good: PostgreSQL REPEATABLE READ
The second writer fails with 40001. Retry it; nothing is lost.
How snapshot isolation works
- Every read sees data as of your start
- Readers and writers never block each other
- Two writers of one row: first committer wins
- Old row versions are kept to serve it
Both doctors sign off. Nobody is on call.
Each snapshot shows the other on call, and each updates a different row. No write conflict, so snapshot isolation commits both.
Under SQL-92’s strict wording, snapshot isolation shows no dirty read, fuzzy read or phantom. It passes as SERIALIZABLE.
The catch
Yet it allows write skew. Oracle’s SERIALIZABLE and PostgreSQL’s REPEATABLE READ are both snapshot isolation.
Start from the anomaly, then pick the cheapest fix
Most races are fixed in the write, not by raising the level.
40001
the error a stronger level hands you
PostgreSQL and CockroachDB abort conflicting transactions with it. Retry the whole transaction, reads included, and keep emails and payments outside the retry.
Remember
Name the anomaly, not the level. Atomic writes for one row, one snapshot for reports, SERIALIZABLE plus retries across rows.