Snapshot Isolation
A consistency level where each transaction reads from a consistent snapshot of the database taken at the transaction's start time. Prevents dirty reads without full serializability overhead.
What is Snapshot Isolation?
In short
Snapshot isolation is a database concurrency model where every transaction reads from a frozen, consistent snapshot of the data as it existed when the transaction began, so it never sees changes committed by other transactions while it runs. It blocks dirty reads, non-repeatable reads, and most phantom problems without the locking overhead of full serializability, but it still allows a defect called write skew.
What it actually is
Snapshot isolation gives each transaction its own private, unchanging view of the database. The moment a transaction starts, the system records a logical point in time, and from then on every read returns the data as it looked at that instant. If five other transactions commit while yours is running, you do not see any of their changes. Your snapshot is yours alone.
This solves the most common read anomalies. You never see uncommitted data from another transaction (no dirty reads). If you read the same row twice, you get the same value both times (no non-repeatable reads). A range query run twice returns the same rows (no phantom reads under most implementations). Readers and writers never block each other, which is the headline benefit.
The catch is that snapshot isolation is weaker than serializability. Two transactions can each read the same snapshot, make decisions based on rows the other is about to change, and both commit. The classic failure is write skew: each transaction reads a shared constraint, sees it satisfied, and writes a different row, and together they violate the constraint that neither alone broke.
How it works under the hood
Snapshot isolation is built on multi-version concurrency control, or MVCC. Instead of overwriting a row in place, the database keeps multiple versions of it. Every version carries metadata about which transaction created it and when. A read picks the newest version that was committed before the reader's snapshot point and ignores everything newer.
Each transaction gets a unique, monotonically increasing ID. PostgreSQL, for example, stamps every row version with the transaction ID that inserted it (xmin) and the one that deleted or superseded it (xmax). At snapshot time the engine captures the set of in-progress transaction IDs, and visibility rules use that set to decide which versions each query can see.
Writes are handled with first-committer-wins or first-updater-wins conflict detection. If your transaction tries to update a row that another transaction has already modified and committed since your snapshot, the database aborts you with a serialization error so you can retry. This is how snapshot isolation prevents lost updates without taking read locks. Old versions that no transaction can still see are eventually cleaned up by a garbage process, called vacuum in Postgres.
When to use it and the trade-offs
Snapshot isolation is the right default for read-heavy workloads and analytics queries that need a stable picture without freezing out writers. Long reports can run for minutes against a consistent snapshot while transactional traffic keeps committing. This is why it is the workhorse isolation level in most general purpose databases.
The trade-off is correctness for certain invariants. If your business logic depends on a rule that spans multiple rows, like on-call scheduling where at least one doctor must remain on shift, or a bank rule that the sum of two accounts must stay positive, plain snapshot isolation can let both transactions slip through and break the rule. You either move to Serializable Snapshot Isolation, add explicit row locks with SELECT FOR UPDATE, or materialize the conflict on a single guard row.
There is also storage and maintenance cost. Keeping many row versions consumes space and creates bloat, and a long-running transaction holds back cleanup of every version newer than its snapshot. A single forgotten transaction open for hours can make tables grow unboundedly until it ends.
A concrete example
Imagine a hospital system enforcing that at least one doctor is always on call. Two doctors, Alice and Bob, are both on call. Each opens a transaction to go off call. Both transactions read the same snapshot, see that two doctors are on call, conclude that removing one is safe, and each updates their own row. Under snapshot isolation neither write touches the same row, so first-updater-wins finds no conflict, and both commit. Now zero doctors are on call, which the rule forbids. That is write skew.
To stop this, the application could use SELECT count FOR UPDATE to lock the queried rows, force both transactions to serialize on that count, or run under Serializable Snapshot Isolation, which tracks read-write dependencies and aborts one of the offenders. PostgreSQL's SERIALIZABLE level does exactly this with predicate locks layered on top of MVCC.
Where it is used in production
PostgreSQL
Its REPEATABLE READ level is true snapshot isolation via MVCC, and SERIALIZABLE adds dependency tracking to catch write skew.
Oracle Database
Its default READ COMMITTED uses statement-level snapshots, and SERIALIZABLE provides transaction-level snapshot isolation.
Microsoft SQL Server
Offers an explicit SNAPSHOT isolation level backed by row versioning in tempdb.
CockroachDB
A distributed SQL database that historically defaulted to serializable but supports snapshot-style reads from consistent timestamps across nodes.
Frequently asked questions
- What is the difference between snapshot isolation and serializable?
- Snapshot isolation guarantees each transaction reads a consistent point-in-time view, which stops dirty, non-repeatable, and phantom reads. Serializable additionally guarantees the result is equal to running all transactions one after another, so it also catches write skew that plain snapshot isolation lets through.
- Does snapshot isolation prevent lost updates?
- Yes. When two transactions try to update the same row, conflict detection (first-committer-wins or first-updater-wins) aborts one of them with a serialization error, so the second update cannot silently overwrite the first. You retry the aborted transaction.
- What is write skew?
- Write skew happens when two transactions read overlapping data, each writes to a different row based on what it read, and together they violate a constraint that neither broke alone. The on-call doctor example, where both go off shift and leave zero on call, is the textbook case.
- Is REPEATABLE READ the same as snapshot isolation in PostgreSQL?
- In PostgreSQL, yes. PostgreSQL implements REPEATABLE READ as full transaction-level snapshot isolation, which is stronger than the SQL standard minimally requires. The standard's READ COMMITTED in Postgres uses a fresh snapshot per statement instead.
- Why do long transactions cause bloat under snapshot isolation?
- MVCC keeps old row versions until no active transaction can still need them. A transaction open for hours pins every version created after its snapshot, blocking cleanup (vacuum in Postgres). Tables and indexes keep growing with dead versions until that transaction ends.
Learn Snapshot Isolation hands-on
This page explains the idea. The full lesson lets you step through the ring as servers join and leave, read the implementation, and check yourself with a quiz. It is one of 760+ lessons in the System Design Masterclass, from your first API call to distributed consensus. Eleven Foundation lessons are free, no signup. Lifetime access is ₹499 in India or $7.99 worldwide, one payment, no subscription.
See also
Related glossary terms you might want to look up next.
Serializability
A guarantee that concurrent transactions produce the same result as if they were executed one at a time in some serial order. The gold standard for database transaction isolation.
Isolation Level
Controls how much one transaction can see changes made by other concurrent transactions. Ranges from Read Uncommitted (fastest, least safe) to Serializable (slowest, safest).
Transaction
A sequence of database operations treated as a single atomic unit. Either all operations succeed (commit) or none of them do (rollback).
Eventual Consistency
A consistency model where updates propagate asynchronously and all replicas will eventually converge to the same value. Trades immediacy for availability.
Strong Consistency
A guarantee that after a write completes, all subsequent reads will return the updated value. Safer but slower than eventual consistency.
Linearizability
The strongest consistency guarantee: every operation appears to take effect atomically at some point between its invocation and completion. As if there's a single copy of the data.