We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningAdvanced · Consistency Models
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.
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.
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.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Snapshot Isolation across a run of lessons, not one page. These 4 alone are about 164 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
Snapshot isolation, explained: each transaction reads one frozen view of the data. How MVCC builds it, the write skew it allows, and the Postgres fix.
Transactions execute as if they ran one at a time, the gold standard of isolation.
What does a catalog like Unity Catalog do that a folder cannot? I put 3,475,226 NYC taxi trips behind three real catalogs: an Iceberg SQL catalog, the Apache Iceberg REST catalog and Unity Catalog OSS 0.6.0. A rename sent one SQL UPDATE and moved 0 of 9 files, while moving the folder broke a reader after 1 of 10 files. A writer that crashed before its swap left a newest metadata file with 123,646 trips nobody committed. Two catalogs on one folder both accepted writes and disagreed. Unity Catalog refused a user with no grants, but the same user read every row straight from the folder.
Block storage explained: what it is, why databases like PostgreSQL run on it, block vs file vs object storage, and examples like AWS EBS, with a hands-on lab.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Snapshot Isolationpay once,
yours for life
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.
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.
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 old row version that was replaced or deleted after its snapshot began. A single forgotten transaction open for hours can make tables grow unboundedly until it ends.
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 FOR UPDATE on the on-call rows to lock them, 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.
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.
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.
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.
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.
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.
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.
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.
This page explains the idea. The "Snapshot Isolation" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Snapshot Isolation lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Snapshot Isolation in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
