Snapshot Isolation
Also known as: MVCC snapshot, REPEATABLE READ (Postgres)
Snapshot isolation gives each transaction a consistent view of the database as of its start time, so reads never block writes and writes never block reads. It prevents dirty reads, non-repeatable reads and phantoms, but permits write skew — two transactions reading an overlapping set and writing disjoint rows.
Last reviewed · Part of the Architecture Glossary
In practice
Snapshot isolation is what MVCC buys you. Each row version carries the transaction ID that created it; a reader sees only versions committed before its snapshot. That is why a long analytical query in Postgres does not block the OLTP writes running alongside it.
The costs are operational rather than logical:
- Bloat. Old row versions stay until vacuum removes them, and vacuum cannot remove anything newer than the oldest running snapshot. One forgotten
BEGIN;in a psql session pins the horizon and the table grows all afternoon. - First-committer-wins. Two transactions writing the same row: one aborts. Under high contention on a counter row, that abort rate is your throughput ceiling.
- Write skew. The anomaly it does not prevent, and the reason SERIALIZABLE exists.
When it matters
It is the default in PostgreSQL (READ COMMITTED uses per-statement snapshots, REPEATABLE READ uses per-transaction), Oracle, SQL Server with RCSI, and MySQL InnoDB's REPEATABLE READ.
Common mistake
Reading a value, deciding in application code, then writing a different row — the exact shape write skew exploits. If the invariant spans rows, you need SELECT ... FOR UPDATE, a materialised conflict row, or SERIALIZABLE.
See also
- Serializable IsolationSerializable isolation guarantees that concurrent transactions produce the same result as some serial execution of them.
- Write SkewWrite skew is the anomaly where two concurrent transactions each read an overlapping set of rows, each decides an invariant still holds, and each writes a different row — leaving the invariant violated.
- Replication LagReplication lag is the delay between a write committing on the primary and becoming visible on a replica.