Transaction isolation: when read committed quietly skips your row

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, phantom reads, lost updates, write skew

Postgres ships with READ COMMITTED as its default isolation level, and neither Django nor Rails will tell you. You read "ACID" on the marketing page, assume your update_balance() function is safe under concurrency, and ship. It isn't. READ COMMITTED allows lost updates, non-repeatable reads, phantoms, and write skew - and a banking app with two concurrent transfers can credit one account twice if the code does the natural read-then-write pattern, with Postgres committing both without a single error in the log.

The django update that lost rows

Picture the simplest function in any system that touches money. Read balance, add deposit, write back, commit.

def credit(account_id, amount):
    with transaction.atomic():
        acct = Account.objects.get(id=account_id)
        acct.balance += amount
        acct.save()

Two requests arrive simultaneously, each crediting 100. A reads balance = 500. B starts a millisecond later, reads balance = 500 too - A hasn't committed yet, so B can't see A's work. A computes 600, writes, commits. B computes 600, writes, commits. Final balance: 600. Customer credited 200, account moved by 100, one deposit is gone, no error anywhere.

This is the lost update anomaly, the most common production correctness bug in OLTP systems. Not "common in 1995 textbooks" - common right now, in Django apps shipping this week, because the ORM gives you a transaction and you assume the transaction did the locking. It didn't. Under READ COMMITTED, each statement sees the latest committed data, but two transactions can both read the same row, both compute new values from it, and both write back. The database serializes the writes - last writer wins. The arithmetic loses.

This bug is durable because it doesn't show up in load tests with one client, and it doesn't show up in unit tests at all. It only fires when two requests collide on the same row, which on most apps means it fires occasionally in production, looks like a "weird discrepancy", gets blamed on a flaky integration, and lives in the codebase for years. Evergreen #4 showed how the visibility horizon holds vacuum back; the same horizon is what makes snapshot isolation possible.

What the four ANSI isolation levels mean

The 1992 SQL standard defines four levels in terms of which anomalies they allow. The anomalies were the framework, the levels were the rungs.

The first is dirty read - reading data from a transaction that hasn't committed yet. If A writes balance = 0 and hasn't committed, B should not see balance = 0. If A rolls back, B made a decision on a value that never existed.

The second is non-repeatable read - reading the same row twice in one transaction and getting different values, because another transaction committed an UPDATE in between. Read balance, do some work, read balance again, it changed underneath you.

The third is phantom read - running the same range query twice and getting different rows back. SELECT * FROM orders WHERE user_id = 4711 returns 3 rows the first time, 4 rows the second time, because another transaction inserted a new order that matches your predicate.

The fourth, which ANSI missed and Berenson et al. pointed out in 1995, is write skew. Two transactions read overlapping data, each decides based on what the other can't see, both commit writes consistent with their own snapshot but together violate a constraint nobody enforced. Two doctors on call. Both transactions read "at least one doctor on call", both decide "I can go off-call because the other one's still there", both UPDATE themselves to off-call, both commit. Zero doctors on call.

The standard maps anomalies to levels mechanically:

• READ UNCOMMITTED: dirty reads allowed, plus everything below.

• READ COMMITTED: no dirty reads. Non-repeatable reads, phantoms, and write skew all allowed.

• REPEATABLE READ: no dirty reads, no non-repeatable reads. Phantoms allowed (in ANSI). Write skew allowed.

• SERIALIZABLE: no anomalies. Transactions behave as if they ran one after the other.

The catch is that "behave as if they ran one after the other" is a strong promise. To deliver it cheaply, real databases either lock aggressively or let transactions proceed optimistically and abort some at commit time when a conflict shows up. Postgres makes one bet, MySQL InnoDB makes another, Oracle makes a third. The level name is the same, the behavior isn't.

That's where most production bugs live - in the gap between "I asked for REPEATABLE READ" and "this database's definition of REPEATABLE READ". You have to know what your engine actually does.

How postgres actually implements these

Postgres has its own opinions, and they're worth memorising once instead of guessing every time.

READ UNCOMMITTED doesn't really exist - if you ask for it, you get READ COMMITTED. Dirty reads aren't possible on MVCC the way Postgres does it, because every row version carries the xmin of the transaction that wrote it, and readers skip versions whose xmin hasn't committed.

READ COMMITTED is the default. Every statement gets a fresh snapshot. Two statements in the same transaction can see different data because another transaction committed in between. Fast, and where lost updates and write skew quietly happen.

REPEATABLE READ in Postgres is actually snapshot isolation. The whole transaction sees one snapshot taken at the start, regardless of what commits later. This is stronger than ANSI requires - phantoms are allowed at this level by the standard, Postgres prevents them because the snapshot excludes newly-inserted rows from other transactions. You can still get write skew, because two transactions on two disjoint snapshots can each decide something the other isn't allowed to see.

SERIALIZABLE is implemented as SSI - Serializable Snapshot Isolation. Snapshot isolation plus a runtime dependency tracker that watches for dangerous patterns of read/write conflicts between concurrent transactions. When it spots one, it aborts the second transaction to commit with SQLSTATE 40001, could not serialize access due to read/write dependencies. SSI is cheap - no extra locks, no extra blocking - but it shifts the cost to commit-time aborts.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true;
-- application logic checks count >= 1
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
-- ERROR:  40001: could not serialize access due to read/write dependencies
-- among transactions

The 40001 SQLSTATE is the contract. Any code that opens a SERIALIZABLE transaction must catch it and replay the whole transaction from the start - not just the failed statement, since the snapshot was taken at BEGIN. New BEGIN, fresh snapshot, redo the whole thing. If you don't have retry logic, you don't have SERIALIZABLE - you have a database that occasionally fails the request and tells your user something went wrong.

When you actually need serializable

Most apps don't. READ COMMITTED with proper SELECT FOR UPDATE row locks covers maybe 95% of real-world cases. The natural pattern is: lock the rows you're about to modify, then read-modify-write, then commit. As long as you lock everything you read for-decision, lost updates and write skew are blocked because the second transaction blocks on the lock until the first commits, then re-reads, then sees the fresh value.

SERIALIZABLE matters when row-level locks aren't enough. Three signals:

You can't enumerate the rows to lock ahead of time. The doctors-on-call example is canonical - the constraint is "at least one doctor on call", which depends on the count of rows matching a predicate, not on a specific row. Row locks can't protect a predicate. SSI's dependency tracker has predicate locks built in.

The access pattern is complicated enough that getting locking right by hand is error-prone. Six tables, three indirections, a constraint that spans them. Either reason through every lock-acquisition order yourself, or ask the database to figure it out and abort conflicting transactions. The second is easier to get right.

Correctness matters more than throughput. Banking, financial reconciliation, inventory with reservations - anything where a wrong commit is worse than a slow one. Trade-off is real: every transaction can fail with a serialization error, every retry costs latency, and on hot rows retry rate can spike. If you can't make your transactions retry-safe, you can't use SERIALIZABLE. That alone disqualifies a lot of codebases.

The select for update pattern done right

SELECT FOR UPDATE is the workhorse. It acquires a row-level write lock on every row the query returns, holds it until the transaction commits or rolls back, and blocks any other transaction that tries to lock or update those rows. The order-of-operations rule is: lock before you read for decision, not after.

The lost-update bug from earlier fixes with one line:

BEGIN;
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;
-- now this row is locked. application computes new balance.
UPDATE accounts SET balance = $2 WHERE id = $1;
COMMIT;

Transaction B's SELECT ... FOR UPDATE blocks behind A's lock until A commits, then sees the fresh balance and computes correctly. Two writes, both correct, no anomaly. The cost is that B waits.

FOR UPDATE also takes locks on rows referenced by foreign keys, which surprises you on schemas with a lot of FK fanout. If you're just modifying a non-key column, FOR NO KEY UPDATE is the lighter variant - same protection against concurrent writers, weaker lock on FK references.

Two modifiers worth knowing. SKIP LOCKED makes the lock attempt skip rows another transaction already locked, instead of waiting. This is the queue-consumer pattern: ten workers each grab the next available job without stepping on each other.

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Each worker quietly skips rows other workers are processing. No coordination, no Redis, no Kafka. Just Postgres.

NOWAIT makes the lock attempt fail immediately if the row is already locked. Useful for fail-fast paths where blocking would be worse than reporting "busy, try again".

The trap is locking after you've already read. If you do SELECT balance ... ; UPDATE ... without FOR UPDATE, you've gone back to the lost-update case. The lock has to be acquired during the read, not afterwards. pg_locks joined against pg_stat_activity shows you what's locked and who's holding it when contention bites.

Common mistakes

A few patterns that come up over and over in concurrency tickets:

• Assuming "ACID" means serializable. ACID is a marketing umbrella. The "I" is whatever isolation level you actually configured, and the default is the weakest useful one.

• Doing read-then-update without FOR UPDATE. The classic lost-update path. If you read a value to compute a new one, lock the row during the read.

• Wrapping logic in BEGIN; ... COMMIT; and assuming Postgres will serialize for you. It won't. Transactions don't lock rows just because they're transactions.

• Catching SQLSTATE 40001 and logging it instead of retrying. The whole point of SERIALIZABLE is that the database tells you when to retry - if you don't, you've taken the cost and gotten none of the benefit.

• Mixing isolation levels in one connection pool. A pool that hands out READ COMMITTED and SERIALIZABLE sessions interchangeably is a debugging nightmare. Set the default per-database or per-role, not per-session.

• Holding row locks across application logic - HTTP calls, queue publishes, long computations. Locks held that long cause deadlocks under load and turn into incidents. Lock late, commit fast.

• Using LOCK TABLE because SELECT FOR UPDATE felt insufficient. Table locks block every reader and writer. Almost never the right answer; advisory_lock for application-level mutual exclusion is usually what you actually wanted.

Storage was Evergreen #4, planner #5, concurrency today. Next: JSONB - when the document column is the right call, when it's a documented mistake, and how the same MVCC machinery that powers snapshot isolation makes JSONB updates rewrite the whole document every time you touch one key. The isolation level you picked is the top of the iceberg. What it costs in dead tuples and bloat is the next layer down.
-->