“The Money Was There When I Checked” — Transaction Isolation Levels and the Bugs Each One Lets Through

Tags: Transactions · Isolation Levels · ACID · MVCC · Write Skew · Locking · PostgreSQL · MySQL · Reading time: ~13 min · Category: Database → Query Optimization

Every company gets this ticket eventually. "We sold 3 tickets but only had 1." "This customer was refunded twice." You open the code and it looks right — it reads the stock, checks it's above zero, decrements it, all inside a transaction. It works a hundred times locally. It only breaks in production, only under load.

The code isn't wrong. The assumption is. "Inside a transaction" does not mean "alone." It means you're running at an isolation level — a setting you probably never chose. And every level except the strictest comes with a list of bugs it is officially allowed to let through.

There are five bugs. Learn their names once and this stops being mysterious forever.

First, the gap

A shop keeps stock in one notebook. Two cashiers, Ana and Ben, are serving two customers who both want the last hoodie.

Ana reads the notebook: stock: 1. She turns to bag the hoodie. Ben reads it: stock: 1. He turns to bag one too. Ana writes stock: 0. Ben writes stock: 0.

Two hoodies sold, one existed. And notice: nothing recorded that anything went wrong. No error, no exception, no log line. Just wrong data that looks completely normal.

That's the signature of every bug below. And they all live in the same place — the gap between reading something and acting on it. An isolation level is just an answer to one question: what is another transaction allowed to do in that gap?

The five bugs

BEGIN;
SELECT SUM(total) FROM orders;      -- ₹4,20,000
--   ← 40 new orders commit right here
SELECT SUM(qty) FROM order_items;   -- includes those 40
COMMIT;

BEGIN;
SELECT COUNT(*) FROM bookings WHERE room = 4 AND slot = '10:00';  -- 0, free ✓
--   ← someone else runs the same check, also sees 0
INSERT INTO bookings VALUES (4, '10:00', 'Ana');
COMMIT;                                    -- and so do they

const u = await repo.findOne(id);   // reads credits = 100
u.credits -= 10;                    // ← the gap. a network round trip.
await repo.save(u);                 // writes 90, on top of someone else's 90

Write skew, step by step

A hospital rota with one rule: at least one doctor must stay on call. Two doctors are on call. Both feel ill. Both hit the button in the same second.

-- Both run this exact code, at the same moment.
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = true;   -- 2, so leaving is fine ✓
UPDATE doctors SET on_call = false WHERE name = 'A'; -- Txn A writes row A
COMMIT;                                              -- Txn B writes row B

Here's why a snapshot doesn't save you. Snapshot isolation watches for two transactions writing the same row. These two never do. The damage comes from the combination, and the only level that reasons about combinations is SERIALIZABLE.

Five bugs, four settings

✅ = can't happen · ❌ = allowed to happen to you

Look at the READ COMMITTED row. That's where most production systems are sitting right now, and that level permits four of the five bugs. Nobody chose that. It's the default, and database vendors tune defaults for throughput, not for your ledger.

The trap: the names lie

The same words in your SQL mean different things on different databases. This one catches teams during migrations.

The fixes, cheapest first

1. Close the gap

The bug exists because there's a gap between reading and writing. Delete the gap and the bug is gone — at every level, with no retries and no config:

-- Instead of: read stock, check it in the app, write it back
UPDATE inventory
SET stock = stock - 1
WHERE sku = 'HOODIE-M' AND stock > 0;
-- 0 rows affected ⇒ sold out. Handle it in the app.

The check and the write happen in one statement, holding a row lock the whole time. This one line fixes more real concurrency bugs than every isolation level combined.

2. Let the database enforce the rule

CREATE UNIQUE INDEX ON bookings (room, slot);   -- double-booking now impossible
ALTER TABLE inventory ADD CHECK (stock >= 0);   -- oversell fails loudly instead

Constraints work at every isolation level, cost nothing to read, and can't be bypassed by code someone writes next year.

3. Lock the row you're about to change

BEGIN;
SELECT stock FROM inventory WHERE sku = 'HOODIE-M' FOR UPDATE;  -- others wait here
-- ... application logic ...
UPDATE inventory SET stock = stock - 1 WHERE sku = 'HOODIE-M';
COMMIT;

4. Raise the level

For a report that must balance, one snapshot fixes it:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(total) FROM orders;        -- both queries see
SELECT SUM(qty) FROM order_items;     -- the exact same instant
COMMIT;

For a rule spanning rows, SERIALIZABLE is the only option — and it needs a retry wrapper:

async function withSerializable(fn, maxAttempts = 5) {
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      await db.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await fn();
      await db.query("COMMIT");
      return result;
    } catch (err) {
      await db.query("ROLLBACK");
      // 40001 = conflict · 40P01 = deadlock
      const retryable = err.code === "40001" || err.code === "40P01";
      if (!retryable || attempt === maxAttempts) throw err;
      await sleep(2 ** attempt * 10 + Math.random() * 50);   // backoff + jitter
    }
  }
}

Four traps

• Your ORM already opened a transaction. Most frameworks wrap each request at the default level. So find() → change → save() is running at READ COMMITTED whether you thought about it or not — a lost update waiting for traffic.
• Isolation sticks to the connection. SET SESSION ... leaks into whatever request takes that pooled connection next. Set it per transaction (BEGIN ISOLATION LEVEL ...) instead.
• None of this crosses a replica or a service. Read from a replica and you get lag on top of everything here (read-your-writes). Split the rule across two services and SERIALIZABLE in each guarantees nothing about the pair (life beyond distributed transactions).
• Your tests will never find these. Every bug here needs two transactions interleaved at a precise instant. A single-threaded test suite produces exactly zero of them. Encode the rule as a constraint, or write a test that runs two real concurrent transactions.

The takeaway

1. Find out what level you're on. One query. If it's READ COMMITTED — and it probably is — the level allows four of the five bugs above to happen to your users. None of them appears as an error.
2. Write skew is the gap that matters. Everything below SERIALIZABLE permits it, including Oracle's level literally named SERIALIZABLE. Any rule of the form "the total across these rows must stay under X" is exposed, and it fails silently.
3. Close the gap before you widen the guarantee. An atomic UPDATE, a UNIQUE index, or a CHECK fixes most of this outright, at any level, with no retries. Save SERIALIZABLE for rules that can't be written any other way — and ship the retry wrapper in the same commit.

Isolation is a negotiation your database offers you, and staying silent is still an answer. Someone chose your default. It just wasn't you, and they'd never seen your data.