We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
6 system design questions lean on this idea. Each walks through the full answer.
System Design Masterclass · Consistency Models
Free preview: the first 2 parts of this 8-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Consistency Models
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
You run a financial report reading thousands of rows. Halfway through, another transaction updates some rows. Without snapshot isolation, your report sees a mix of old and new values. Totals do not add up.
Snapshot isolation gives each transaction a consistent view as of its start time. All reads see the same snapshot regardless of concurrent writes. In PostgreSQL this is the REPEATABLE READ level. It is not the default: PostgreSQL defaults to READ COMMITTED, which takes a fresh snapshot for every statement instead of one for the whole transaction.
The catch: write skew. Two transactions read overlapping data, make decisions, and write non-overlapping data. The combined result is inconsistent, neither transaction would have produced it alone.
MVCC (Multi-Version Concurrency Control) maintains multiple versions of each row. Each transaction reads from its snapshot (a specific point in time). Writers create new versions rather than overwriting.
| Anomaly | Prevented by SI? | Example |
|---|---|---|
| Dirty read | Yes | Reading uncommitted data |
| Non-repeatable read | Yes | Same query, different results |
| Phantom read | Mostly | New rows appearing in range queries |
| Write skew | No | Two doctors both going off-call because each sees the other on-call |
Write skew example: Hospital rule: at least one doctor must be on-call. Doctors A and B are both on-call. A checks: B is on-call, so A goes off-call. Concurrently, B checks: A is on-call, so B goes off-call. Under snapshot isolation, both see the other on-call (from their snapshot) and both go off-call. Rule violated.
Serializable Snapshot Isolation (SSI) in PostgreSQL detects and prevents write skew by tracking read-write conflicts.