Snapshot isolation, in practice

Saying “we use snapshot isolation” is a sentence. Living with it is a stack of small decisions that every team makes again, badly, the first time a customer hits a write-skew bug at 2am.

This post is the cheat sheet we wished we had: what you get, what you don’t, what error your client actually sees, why we don’t auto-retry, and the one application pattern that resolves most real-world write-skew without coordination.

What snapshot isolation gives you

Each transaction sees a consistent snapshot of the database as of the moment it began. Readers never block writers. Writers never block readers. Two transactions can read overlapping rows in parallel — neither waits for the other to commit.

For 90% of OLTP code that previously ran on read-committed Postgres or MySQL with REPEATABLE READ, this is a strict upgrade:

No more dirty reads, non-repeatable reads, or phantom reads inside a single transaction.
Long-running reports stop blocking the write path.
“Why is my SELECT seeing half-applied changes?” disappears as a category.

That part is real and wonderful. You can stop reading here if your workload is small and you only ever update one row per transaction.

What snapshot isolation does NOT give you

Snapshot isolation is not serializable. There’s exactly one anomaly that survives, and it has a name: write skew.

The textbook example uses doctors going off-call. Forget the textbook. Here’s one we actually shipped a patch for:

Real write-skew: tenant seat allocation

A SaaS plan permits up to N active seats per tenant. The check-and-insert looks like this:

BEGIN;
  -- T1 and T2 both see active_count = 4, max = 5
  SELECT count(*) FROM seats
   WHERE tenant_id = $1 AND active = true;
  -- T1 inserts seat #5 → 4 + 1 = 5, within limit, commit.
  -- T2 inserts seat #5 → 4 + 1 = 5, within limit, commit.
  INSERT INTO seats(tenant_id, user_id, active) VALUES ($1, $2, true);
COMMIT;

Both transactions read the same snapshot. Neither modifies a row the other modifies — they each insert a different new row. Snapshot isolation has no conflict to detect. Both commit. The tenant now has 6 active seats on a 5-seat plan.

Under serializable isolation the database would force one of them to abort. Under snapshot isolation, you have to do the work.

We’ve seen this exact shape in:

Inventory reservation (last unit sold twice).
Bank-style transfers when the constraint is “account balance must stay non-negative” and two debits land on the same account from different sessions.
Approval workflows where exactly one approver should advance a step.

The pattern is always: the invariant spans rows the transaction reads but does not modify.

What clients actually see

When two transactions modify the same row and one commits first, the second receives:

ERROR: serialization conflict on commit
SQLSTATE: 40001
DETAIL: write conflict with concurrent transaction <txid>

SQLSTATE 40001 is the standard signal. Every mature client library checks for it. We deliberately picked the same code Postgres uses so that existing retry middleware in your stack just works.

What the second transaction sees on its COMMIT call:

try {
  await db.transaction(async (tx) => {
    await tx.query('UPDATE counters SET n = n + 1 WHERE id = $1', [id])
    await tx.query('INSERT INTO events(...) VALUES (...)')
  })
} catch (err) {
  if (err.code === '40001') {
    // Conflict. Application decides what to do.
  }
  throw err
}

The whole transaction is rolled back. No partial state, no half-applied writes, no need to clean up manually.

Why we don’t auto-retry

Several engines retry 40001 transparently inside the driver. We considered it. We chose not to. Three reasons:

Retries hide concurrency bugs. A function that silently runs 1–N times changes meaning. Idempotency stops being optional — it becomes a load-bearing requirement that nobody documents until production breaks.
The retry budget belongs to the application. “Retry 3 times with backoff” is correct for some workloads. For others (financial), the right answer is “abort, surface to user, let them re-issue.” We can’t pick that for you.
Auto-retry composes badly with side effects. If your transaction calls sendEmail() mid-flight via a trigger or hook, retry sends two emails. The retry decision has to live at the layer that knows what’s safe to repeat.

So you get an explicit 40001 and you decide. In practice most teams add a small wrapper:

async function withRetry<T>(fn: () => Promise<T>, attempts = 3): Promise<T> {
  for (let i = 0; i < attempts; i++) {
    try { return await fn() } catch (err: any) {
      if (err.code !== '40001' || i === attempts - 1) throw err
      await sleep(20 * Math.pow(2, i) + Math.random() * 20)
    }
  }
  throw new Error('unreachable')
}

That belongs in your application layer. We refuse to put it in the driver.

The one pattern that fixes most real write-skew

For the seat-allocation example above, the cleanest fix isn’t “switch to serializable” (which would force aborts on workloads that don’t actually conflict). It’s read-then-write CAS: include the value you read in the predicate of the write, so a concurrent change breaks your assumption explicitly.

For the seat example we keep a counter row per tenant:

CREATE TABLE tenant_seat_counters (
  tenant_id  uuid primary key,
  active_n   int  not null,
  version    int  not null default 0
);

The transaction becomes:

BEGIN;
  -- 1. Read the counter and version inside the transaction snapshot.
  SELECT active_n, version FROM tenant_seat_counters
   WHERE tenant_id = $1;

  -- 2. Check the invariant in application code.
  -- If active_n + 1 > plan.max_seats, abort and return a friendly error.

  -- 3. CAS the counter: predicate includes the version you read.
  UPDATE tenant_seat_counters
     SET active_n = active_n + 1, version = version + 1
   WHERE tenant_id = $1 AND version = $2;
  -- If rowcount = 0, another transaction won. Abort.

  -- 4. Insert the seat row.
  INSERT INTO seats(tenant_id, user_id, active) VALUES ($1, $3, true);
COMMIT;

Now both T1 and T2 race on the same row (the counter). Snapshot isolation detects the conflict on step 3 and aborts the loser with 40001. The application retries (or surfaces a “couldn’t grab a seat, please try again” message). The invariant holds.

This pattern generalises: any time your invariant spans multiple rows, find or invent a single row whose version represents that invariant, and gate the write on that version. You’ve turned a write-skew anomaly into a write-write conflict, which snapshot isolation handles natively.

When read-then-write CAS isn’t enough

A few real workloads escape this pattern:

Multi-tenant invariants that span an unbounded set of rows (“no two appointments overlap for the same room”). A version row works only if all writers consent to touch it; if you have legacy writers, you need range locks.
Cross-table invariants that traverse foreign keys with cascading effects. Here we suggest a per-aggregate version row owned by the aggregate root.
Hot counters under sustained contention. The CAS pattern becomes a thrash. Switch to a sharded counter (N rows, randomly chosen on write, summed on read) and accept eventual consistency in the count.

For the rare cases where none of the above works, we expose SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. It costs more, aborts more, but kills write skew at the database layer. Use it surgically.

A quick checklist before you ship

If you’re about to deploy a feature whose correctness depends on a multi-row invariant under concurrent writers, ask:

What’s the invariant in one sentence?
Which rows do I read to verify it, and which rows do I write to enforce it?
If the read set and write set don’t overlap, where does write skew hide?
Is there a single row whose version represents the invariant — and if not, can I add one?
If conflict-on-commit happens, what’s the user-visible message?

We’ve never regretted answering those before merging. We’ve regretted skipping them more than once.

TL;DR

Snapshot isolation removes 90% of concurrency footguns and is the right default.
The 10% it leaves behind is write skew, and it’s a real bug, not a textbook one.
Conflicts surface as SQLSTATE 40001. We don’t auto-retry — that’s an application decision.
For most write-skew, read-then-write CAS on a version row restores correctness with no global locking.
When CAS doesn’t fit, switch the offending transaction to serializable. Pay the cost where it’s earned.

Snapshot isolation isn’t a slogan. It’s a contract with a known asterisk. Read the asterisk before you sign.