Isolation levels & write skew

The problem

Run transactions concurrently for speed and they corrupt each other in subtle ways.

The idea

Isolation levels define which anomalies (dirty reads, lost updates, write skew) are allowed. Serializable is the gold standard.

How it works

Read Committed → Repeatable Read → Snapshot Isolation → Serializable, each forbidding more anomalies. Snapshot Isolation prevents most but allows write skew (two txns read an overlapping set, write different rows, break an invariant). Serializable Snapshot Isolation closes that gap.

The tradeoff

Stronger isolation = more blocking/aborts = less concurrency. Most apps run at Read Committed and don't realize the risks.

In the wild

Postgres SERIALIZABLE uses SSI; "is Snapshot Isolation serializable?" is a classic interview trap (no — write skew).

Interview deep dive

Flow

1. List the invariant each transaction must preserve.
2. Pick the weakest level that still protects it.
3. Use Snapshot Isolation so readers don't block writers.
4. Escalate to Serializable (SSI) where write skew can break the invariant.

Watch for

• Snapshot Isolation allows write skew despite feeling "safe".
• Read Committed (most defaults) permits lost updates and non-repeatable reads.
• Stronger isolation = more aborts/retries — handle them.

Interviewer trap

Answer the trap: Snapshot Isolation is NOT serializable — name write skew as the gap.

Related Academy