Transactions and isolation

Most engineers learn ACID as a memorized acronym and never think about it again until the day a customer reports that their payment went through twice. By then it's too late to read about isolation levels — you're reading the audit log trying to figure out which read saw which write in what order, and whether your application code was wrong or the database was doing exactly what you asked it to.

The honest summary is this: ACID is the easy part. Atomicity, consistency, isolation, durability — every mainstream database gives you all four, and you almost never have to think about three of them. The fourth, isolation, is the one that quietly leaks. Every SQL database ships with a default isolation level that's weaker than serializable, and every application built on the default eventually meets one of the anomalies the level was designed to allow.

What you actually get by default

Postgres defaults to read committed. MySQL InnoDB defaults to repeatable read (with a twist — see below). Oracle defaults to read committed. SQL Server defaults to read committed. SQLite defaults to serializable. You can change the default per session or per transaction with SET TRANSACTION ISOLATION LEVEL ....

The defaults are not arbitrary — they reflect the database's cheapest isolation level that still doesn't let through the most embarrassing anomalies. Cheaper means less locking, more throughput, fewer retries. The price is that you have to know which anomalies the default still permits.

The four anomalies you'll meet

Each isolation level is defined by which of these anomalies it prevents. There are four classical anomalies, and you only need to remember what they look like:

Dirty read — Tx A reads a row that Tx B has written but not committed. If B then rolls back, A read data that never officially existed. Almost every modern database prevents this by default. The only level that allows it is READ UNCOMMITTED and you should not use it.

Non-repeatable read — Tx A reads the same row twice in the same transaction and gets two different values, because Tx B committed an update in between. Allowed by READ COMMITTED. This is the one most engineers hit first: you read a balance, do some math, write it back, and another transaction sneaks in between your read and your write.

Phantom read — Tx A runs the same query twice and gets a different set of rows (not just different values) because Tx B inserted or deleted a row matching the predicate. Allowed by READ COMMITTED and REPEATABLE READ in most engines. The "select all unpaid invoices, mark them paid" pattern is where you meet phantoms.

Write skew — Tx A and Tx B both read overlapping sets, decide their writes are safe based on what they read, and commit. Each was individually consistent; together they violate an invariant. The classic example is "at least one doctor must be on call" — A removes doctor 1 from on-call, B removes doctor 2, both checked first that the other was still on call, both commit, now nobody's on call. Allowed by every level except true SERIALIZABLE.

* In Postgres, REPEATABLE READ is actually snapshot isolation and prevents phantoms; in standard SQL it doesn't. MySQL's REPEATABLE READ prevents phantoms via gap locks. The standard is loose; engines fill in the gaps differently. Always check what your specific database guarantees.

The isolation level isn't a description of what your database does. It's a description of which mistakes it lets the application make.

Snapshot isolation, and why most teams stop here

Snapshot isolation is the sweet spot for almost every workload. Each transaction sees a consistent snapshot of the database as of its start time. Readers never block writers, writers never block readers. The implementation cost is MVCC — every row carries a version chain, old versions live until vacuum collects them. Postgres, Oracle, SQL Server, and Spanner all do MVCC.

The trade-off is write skew. If two transactions both read overlapping data and write disjoint rows, they both commit even when the result violates a business invariant. The on-call doctor example is the textbook case. In practice you also meet it as "two users both try to claim the last seat", "two transfers from the same account simultaneously drain it twice", "two threads check inventory and both decrement it".

The application-level fix is to either upgrade specific transactions to serializable, or to explicitly lock the rows you're going to depend on with SELECT ... FOR UPDATE. The latter is the move most production codebases make — they live in snapshot isolation 99% of the time and sprinkle FOR UPDATE on the handful of transactions that need stronger guarantees.

Serializable snapshot isolation (SSI)

Postgres 9.1+ ships true SERIALIZABLE via SSI — Serializable Snapshot Isolation. Instead of locking, it tracks read/write dependencies between concurrent transactions and aborts one if it detects a cycle that could have produced a non-serializable history. You don't write any locking code; you write your transaction as if it's the only one running, and the database aborts it with a serialization failure if it isn't.

The price is retry logic. SSI will refuse to commit some transactions that snapshot isolation would have allowed. Your app must catch the serialization failure, sleep briefly, and retry. The abort rate depends on your workload — a write-heavy app with hot rows might see double-digit percent retries; a mostly-read app sees none. Measure before you assume.

What the retry tax actually costs

The "measure before you assume" advice is easy to nod along to and hard to feel until you put numbers on it. The retry rate isn't a fixed property of SERIALIZABLE — it scales with how often two transactions touch the same hot rows at the same time, which scales with how long each transaction holds its read set open.

Say a transaction takes 5 ms to run, and you're pushing 2,000 contended writes/sec at a small set of hot rows. The conflict probability per transaction is roughly (other in-flight txns on the same hot set) × (window they overlap). Tighten or loosen that window and the retry tax moves with it:

The shape of the table is the lesson: retry rate grows super-linearly with transaction duration, because a longer transaction both conflicts with more peers and stays vulnerable longer. At 25% you're not paying a small tax — a quarter of your writes run twice, the abort-retry loop amplifies load exactly when you're already busy, and you can tip into a retry storm. The fix is almost never "switch off serializable"; it's "make the hot transaction shorter" — move the slow work (HTTP calls, large reads, recomputation) outside the BEGIN ... COMMIT.

Optimistic vs pessimistic — the real choice

Independent of the isolation level, you have a deeper architectural choice: optimistic or pessimistic concurrency.

Pessimistic assumes conflicts are common and locks rows before touching them. SELECT ... FOR UPDATE is the SQL knob; the database holds a row lock until commit, and concurrent writers block. Works well when conflicts are frequent and retries are expensive. The cost is reduced throughput under contention and the ever-present risk of deadlock.

Optimistic assumes conflicts are rare. You read a row with a version number, do work in memory, and on commit you UPDATE WHERE version = ?. If somebody else committed first, the row count is zero and you retry. No locks held during the long part of the transaction. Works beautifully when conflicts are rare; the application code must handle retry.

Optimistic is dominant in modern web stacks because most operations on most rows don't conflict — most users update their own profile, most posts are written once. The handful of contended endpoints (checkout, voting, inventory) either upgrade to pessimistic locking or move the contended state out of the row (counters, queues, materialized views updated asynchronously).

A real production failure that explains everything

A team I worked with shipped a coupon-redemption endpoint at READ COMMITTED. The code looked like this, abbreviated:

BEGIN;
  SELECT remaining_uses FROM coupons WHERE code = $1;
  -- application checks remaining_uses > 0
  UPDATE coupons SET remaining_uses = remaining_uses - 1 WHERE code = $1;
COMMIT;

A single-use promo code went viral. Twenty users redeemed it in the same second. Twenty transactions all read remaining_uses = 1. Each saw "1 > 0", each issued the update, each committed. The counter went to -19. Twenty users got a free product.

The fix — and there are three good ones — is to either (a) put the decrement inside the WHERE clause atomically: UPDATE coupons SET remaining_uses = remaining_uses - 1 WHERE code = $1 AND remaining_uses > 0 and check the affected row count, (b) hold a pessimistic lock with SELECT ... FOR UPDATE, or (c) raise the isolation level to serializable and accept a few retries. The team picked (a) because it required no app changes beyond one line.

The deeper lesson is that the bug wasn't in the database. The database did exactly what READ COMMITTED promises. The bug was in the mismatch between what the code looked like it was doing (an atomic check-and-decrement) and what it actually was (two unrelated statements at an isolation level that allows the world to change between them).

When to walk away

• Single-row writes only — every database guarantees these are atomic. You don't need transactions for "update the user's email".
• Strict serializability across regions — you're in distributed transaction territory. See [CONCEPT]consensus-raft for the protocols, and consider whether you can avoid the requirement.
• Throughput so high that any locking is fatal — your domain model is wrong. Re-shape the data so the hot path doesn't need transactions (event sourcing, idempotent commands, eventually- consistent aggregates). See [CONCEPT]event-sourcing and [CONCEPT]idempotency.

[CONCEPT]write-ahead-log is how the D in ACID is actually delivered. [CONCEPT]replication is how the I starts to leak once you read from replicas.