The Bug in Your Code Might Actually Be an Isolation Level Problem

Key insight: the 'race condition' bug your team spent three days chasing might not be a race condition at all — it might just be the wrong isolation level.

A recent deep dive on [dev.to](https://dev.to/urvish_shah/database-isolation-levels-read-phenomena-an-extensive-deep-dive-4bm9) walks through the four classic SQL isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — and the read phenomena they allow or prevent: dirty reads, non-repeatable reads, and phantom reads. It's a topic every backend developer has technically studied once, usually for an interview, and then promptly forgotten because it never seemed to matter in practice.

It matters in practice constantly. It just doesn't announce itself.

That line is the whole article in one sentence, and it's worth sitting with. When you write SELECT balance FROM accounts WHERE id = 1, then do some logic, then UPDATE, you're implicitly assuming the balance you read is still true when you write. Under Read Committed — the default in Postgres, SQL Server, and Oracle — that assumption is false. Someone else can commit a change in between, and your write silently clobbers reality.

The three phenomena, concretely:

• Dirty read: you read data another transaction wrote but hasn't committed. If that transaction rolls back, you built logic on data that never officially existed.
• Non-repeatable read: you read a row twice in the same transaction and get two different committed values, because someone else updated and committed between your reads.
• Phantom read: you run the same query twice and get a different set of rows, because someone inserted or deleted rows matching your WHERE clause.

Here's where it gets interesting for anyone who's worked across multiple databases: the standard is not the implementation. Postgres's Repeatable Read is actually snapshot isolation and blocks phantom reads too — stricter than the SQL standard requires. Oracle refuses to allow dirty reads at all, even at its most permissive setting, because of how it implements MVCC. If you learned isolation levels from one database and assumed they transfer 1:1 to another, you've probably shipped a bug already.

A classic example — the double-spend / overselling problem:

1-- Transaction A                    -- Transaction B
2BEGIN;                              BEGIN;
3SELECT stock FROM items             SELECT stock FROM items
4  WHERE id = 42;  -- returns 1        WHERE id = 42;  -- returns 1
5-- app logic: stock > 0, proceed    -- app logic: stock > 0, proceed
6UPDATE items SET stock = 0          UPDATE items SET stock = 0
7  WHERE id = 42;                      WHERE id = 42;
8COMMIT;                             COMMIT;

Under Read Committed, both transactions read stock = 1, both think they're safe to sell, and you've now sold one item twice. Neither read was dirty, neither was a phantom — it's just that the isolation level never promised the row would stay valid between your read and your write. This is the exact shape of bug that becomes a support ticket titled "customer says they got charged for an item we didn't have."

The fix isn't always "crank isolation up to Serializable everywhere." That has real costs — more locking, more retries, more contention under load. The practical move is usually one of:

1-- Option 1: pessimistic locking
2BEGIN;
3SELECT stock FROM items WHERE id = 42 FOR UPDATE;
4-- other transactions now block here until this commits
5UPDATE items SET stock = stock - 1 WHERE id = 42;
6COMMIT;
7
8-- Option 2: optimistic concurrency with a version check
9UPDATE items SET stock = stock - 1, version = version + 1
10  WHERE id = 42 AND version = 7;
11-- if 0 rows affected, someone else won the race — retry

FOR UPDATE forces serialization on that specific row without escalating your whole transaction's isolation level. The version-check pattern avoids locking entirely and instead detects conflicts after the fact — better for high-contention, low-conflict-rate workloads like most web APIs.

What I'd add to the source material: isolation level bugs are especially nasty because they don't show up in single-threaded tests, code review, or staging environments with low traffic. They only appear when two transactions genuinely overlap in time, which means your test suite — running requests one at a time — will pass forever while production quietly corrupts data under real concurrent load. This is the same category of bug as a memory race condition in multithreaded code, except most backend developers don't think of their database calls as concurrent code at all. They think of the database as a black box that