Snapshot Isolation

Every time you open a bank app or book a flight, your request lands inside a transaction — a block of reads and writes that the database promises to treat as a single unit. But thousands of transactions run at the same moment, and letting them trample each other's work leads to corrupted data.

The simplest fix — a global lock that lets only one transaction run at a time — is safe but crushes performance. Databases instead pick an isolation level: a precise contract that spells out which concurrency anomalies can still happen in exchange for speed.

Snapshot isolation is one of the most popular choices. The idea is elegant: when your transaction starts, the database takes a frozen snapshot of every committed value. For the rest of the transaction, all your reads come from that snapshot — as though the rest of the world paused. You can never see a half-written update from a concurrent transaction, and you will never observe the same row changing value during your own work.

For decades this felt like a perfect solution. Then, in 1995, researchers at Microsoft showed that two transactions, each reading a perfectly consistent snapshot, can together write a result that no serial execution of those same transactions could ever have produced. That anomaly has a name: write skew. Understanding it — and the class of problems it represents — is the subject of this article.

The scenario: a hospital database has two on-call doctors, Alice and Bob. The rule is that at least one doctor must be on call at all times. Both doctors check the database at the same moment and see that two doctors are on call — it looks safe for one to leave. They each submit their own transaction to go off call simultaneously.

Under snapshot isolation each transaction reads from its own frozen snapshot — so each sees both doctors still on call when it decides. Both transactions commit successfully. The final state: zero doctors on call. The rule is violated.

<div class="scenario">
  <div class="rule-box" id="ruleBox">
    {{rule_text}}
  </div>
  <div class="db-state">
    <div class="doctor-card" id="cardAlice">
      <div class="doc-name">Alice</div>
      <div class="doc-status" id="statusAlice">{{on_call}}</div>
    </div>
    <div class="doctor-card" id="cardBob">
      <div class="doc-name">Bob</div>
      <div class="doc-status" id="statusBob">{{on_call}}</div>
    </div>
  </div>
  <div class="txns">
    <div class="txn" id="txnA">
      <div class="txn-title">{{txn_a_title}}</div>
      <div class="txn-log" id="logA"></div>
      <button id="btnA" type="button">{{btn_alice_off}}</button>
    </div>
    <div class="txn" id="txnB">
      <div class="txn-title">{{txn_b_title}}</div>
      <div class="txn-log" id="logB"></div>
      <button id="btnB" type="button">{{btn_bob_off}}</button>
    </div>
  </div>
  <div class="result-box" id="resultBox"></div>
  <div class="btns">
    <button id="btnRun" type="button" class="primary">{{btn_run}}</button>
    <button id="btnReset" type="button" class="ghost">{{btn_reset}}</button>
  </div>
</div>

* { box-sizing: border-box; margin: 0; padding: 0; }
body { font-family: system-ui, sans-serif; font-size: 14px; color: #1a1a2e; background: #f8f9fc; padding: 12px; }
.scenario { display: flex; flex-direction: column; gap: 10px; }
.rule-box { background: #e8f4fd; border: 1.5px solid #3a86d4; border-radius: 8px; padding: 8px 12px; font-weight: 600; color: #1a5296; font-size: 13px; }
.rule-box.violated { background: #fde8e8; border-color: #d9534f; color: #a02020; }
.rule-box.ok { background: #e8fdf0; border-color: #28a745; color: #155724; }
.db-state { display: flex; gap: 10px; }
.doctor-card { flex: 1; background: #fff; border: 1.5px solid #c8d6e5; border-radius: 10px; padding: 10px 12px; text-align: center; }
.doctor-card.off { opacity: 0.45; }
.doc-name { font-weight: 700; font-size: 15px; margin-bottom: 4px; }
.doc-status { font-size: 12px; background: #d4edda; color: #155724; padding: 2px 8px; border-radius: 12px; display: inline-block; }
.doctor-card.off .doc-status { background: #f5c6cb; color: #721c24; }
.txns { display: flex; gap: 10px; }
.txn { flex: 1; background: #fff; border: 1.5px solid #c8d6e5; border-radius: 10px; padding: 10px 12px; display: flex; flex-direction: column; gap: 6px; }
.txn-title { font-weight: 700; font-size: 12px; color: #555; text-transform: uppercase; letter-spacing: .04em; }
.txn-log { min-height: 72px; font-size: 11.5px; color: #333; line-height: 1.55; }
.txn-log .step { padding: 1px 0; }
.txn-log .step.read { color: #1a5296; }
.txn-log .step.write { color: #c55a00; }
.txn-log .step.commit { color: #155724; font-weight: 700; }
.txn button { font: 600 12px system-ui; padding: 5px 10px; border-radius: 7px;
              border: 1.5px solid #1d3557; background: #1d3557; color: #fff; cursor: pointer; }
.txn button:disabled { opacity: .4; cursor: not-allowed; }
.result-box { min-height: 32px; padding: 8px 12px; border-radius: 8px; font-weight: 700;
              font-size: 13px; text-align: center; display: none; }
.result-box.violation { display: block; background: #fde8e8; color: #a02020; border: 1.5px solid #d9534f; }
.result-box.safe { display: block; background: #e8fdf0; color: #155724; border: 1.5px solid #28a745; }
.btns { display: flex; gap: 8px; flex-wrap: wrap; }
button.primary { font: 700 13px system-ui; padding: 7px 16px; border-radius: 8px;
                 border: 1.5px solid #1d3557; background: #1d3557; color: #fff; cursor: pointer; }
button.ghost { font: 600 13px system-ui; padding: 7px 14px; border-radius: 8px;
               border: 1.5px solid #1d3557; background: #fff; color: #1d3557; cursor: pointer; }
button:disabled { opacity: .4; cursor: not-allowed; }

// Code not found

Notice: each individual transaction is internally consistent and reads only committed data. The problem is that two valid snapshots, combined through concurrent writes to different rows, produced a state that no serial ordering of the two transactions could ever reach. That is write skew in its purest form.

Snapshot isolation is not serializable — this is a proven fact, not a bug to be fixed. Here is what the model guarantees, and what it intentionally leaves open:

What SI prevents:

• Dirty reads — you can never see another transaction's uncommitted changes, because your snapshot was taken before they wrote anything.
• Non-repeatable reads — reading the same row twice in a transaction always returns the same snapshot value.
• Phantom reads — new rows committed by others are invisible to your snapshot.

What SI allows:

• Write skew — two transactions each read a set of rows, each sees a consistent state, each writes to a different row, yet the combined result violates a constraint that spans both rows. Formally, this happens because SI uses a first-committer-wins conflict rule: two transactions conflict only if they write the same row. Different rows, no conflict — even if a constraint links them.
• Read-only anomalies — even transactions that never write can observe orderings inconsistent with any serial schedule, as shown by Fekete et al. (2004).

The hierarchy: ANSI SQL defines four levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable. Snapshot isolation sits awkwardly outside this list: it is stronger than Repeatable Read in most respects but still weaker than Serializable because of write skew.

Serializable Snapshot Isolation (SSI), introduced by Cahill, Röhm and Fekete (2008), closes the gap by detecting dangerous structures — pairs of transactions whose snapshot reads conflict with each other's writes — and aborting one. SSI is now the default in PostgreSQL (SERIALIZABLE) and CockroachDB. It achieves true serializability with only a modest performance penalty over plain SI.

The take-away: snapshot isolation is a carefully engineered point on the isolation spectrum, not a shortcut. Its anomalies are the provable price of not holding locks across the full transaction. See also the related trade-off in P vs NP — many correctness guarantees have a matching hardness cost.

Snapshot isolation (often implemented via MVCC — Multi-Version Concurrency Control) is the default or common mode in nearly every major database:

• PostgreSQL: the $REPEATABLE READ$ level is full SI; SERIALIZABLE upgrades it to SSI.
• Oracle: the default $READ COMMITTED$ level is SI per statement; SERIALIZABLE is transaction-level SI (not true serializability — write skew is still possible in Oracle's implementation).
• Microsoft SQL Server: the SNAPSHOT isolation level, available since SQL Server 2005, gives full SI semantics.
• CockroachDB and YugabyteDB: distributed databases built on SSI by default.

Where write skew bites in practice:

• Double-booking — two agents book the last seat on a flight by each reading "1 seat left" on their own snapshot and both writing "0 seats left" to a reservation row.
• Overdraft — two withdrawals each see the balance as sufficient; both commit, leaving a negative balance.
• Constraint violations — any business rule that spans multiple rows (on-call rosters, inventory limits, uniqueness across a table) is vulnerable.

How practitioners defend against it:

1. Use $SELECT FOR UPDATE$ on the rows that form the constraint — this converts an SI transaction into a lock-based one for those rows.
2. Upgrade to SSI (SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in PostgreSQL) — the database detects and aborts dangerous pairs automatically.
3. Redesign the schema so the constraint lives in a single row that both transactions must touch — turning write skew into a detectable write–write conflict.

Understanding snapshot isolation is essential for anyone building systems where correctness matters — the same discipline applies to distributed consensus and scheduling problems where global constraints must hold across independent agents.

Snapshot isolation is a triumph of database engineering: by giving each transaction its own frozen view of the world, it eliminates entire classes of anomalies without the performance cost of global locking. For the vast majority of workloads, it is the right default.

But the write-skew anomaly is a reminder that partial correctness guarantees have precise, provable limits. Two transactions, each reading a perfectly valid snapshot, can together write a state that no sequential execution of those transactions could ever have reached. The guarantee was real — it just covered something slightly narrower than what the application needed.

The lesson generalizes far beyond databases: whenever a system grants agents independent, consistent views and lets them act on those views concurrently, global invariants can shatter even when every local decision was sound. Recognizing that gap — between local correctness and global correctness — is one of the most valuable instincts a system designer can develop.

Serializable Snapshot Isolation closes the gap almost for free. When in doubt, use it. And when you can't, document exactly which anomalies you are accepting — and why.