Transaction Isolation Levels and the Subtle Bugs They Hide

A customer writes in: "I withdrew 30 and then 50, but my balance only dropped by 50." You check the code and it looks fine. The logs show both requests succeeded. You run it locally and it works every time.

The bug is not in any single code path. It lives in what one transaction can see while another runs at the same time, and that is exactly what transaction isolation levels decide. The defaults in PostgreSQL and MongoDB let two kinds of bugs straight through: lost updates and write skew.

This post shows you both bugs, why your tests never catch them, and how you fix each one in PostgreSQL and MongoDB.

Quick Answer

Transaction isolation levels control which concurrency problems a database prevents. PostgreSQL defaults to Read Committed, and MongoDB multi-document transactions give you snapshot isolation when you use readConcern: "snapshot". Neither default stops every lost update or every case of write skew. You fix lost updates with atomic updates or row locks, and you fix write skew with constraints, locks on the rows your decision reads, or PostgreSQL's Serializable level with automatic retries.

What Are Transaction Isolation Levels?

Isolation is the "I" in ACID. Perfect isolation means your transactions behave as if they ran one after another, even when they overlap. That is expensive, so the SQL standard defines four weaker levels, each by the anomalies it rules out:

• Dirty read: you see another transaction's change before it commits.
• Non-repeatable read: you read a row twice and get two different values.
• Phantom read: you run the same query twice and new rows appear.

The table has a gap. The 1995 paper A Critique of ANSI SQL Isolation Levels shows that it misses the two anomalies that do the most damage in application code: lost updates and write skew.

The Problem

Your database is less strict than you think. Here is how PostgreSQL and MongoDB behave out of the box:

Bug 1: The Lost Update

Here is what happened to that customer:

Balance = 100

Request A                         Request B
    │                                 │
    ├─ reads balance → 100            │
    │                                 │
    │                                 ├─ reads balance → 100
    │                                 │
    ├─ writes 100 - 30 = 70           │
    │                                 │
    │                                 ├─ writes 100 - 50 = 50
    │                                 │
    └─────────────────────────────────┴─→ Final balance: 50
                                          (should be 20)

Both requests do the math in application code, and Request B overwrites Request A. There is no error and no warning.

This happens in PostgreSQL at Read Committed. It also happens in MongoDB when you find() a document, change it in JavaScript and replaceOne() it back. If your ORM or ODM loads an object, changes a field and saves it, you have probably shipped this bug.

Bug 2: Write Skew

This one is harder to spot. A hospital needs at least one doctor on call. Alice and Bob are both on call, and both decide to go home at the same moment.

Alice's transaction                  Bob's transaction
        │                                    │
        ├─ "How many on call?" → 2           │
        │                                    ├─ "How many on call?" → 2
        │                                    │
        ├─ UPDATE Alice → off call           │
        │                                    ├─ UPDATE Bob → off call
        │                                    │
        ├─ COMMIT                            │
        │                                    ├─ COMMIT
        │                                    │
        └────────────────────────────────────┴─→ Doctors on call: 0

Each transaction sees 2 doctors and decides it is safe to leave. They change different rows, so neither overwrites the other, yet together they break the rule.

That is write skew: two transactions make valid-looking decisions from the same state, write different rows, and together violate a rule that spans both.

Snapshot isolation allows it. PostgreSQL's Repeatable Read allows it, and so do MongoDB transactions. MongoDB's own engineers say it plainly: your application has to manage write skew.

Repeatable Read is not Serializable. Repeatable Read gives you a stable snapshot, but it does not guarantee that concurrent transactions produce the same result as running them one at a time. The PostgreSQL isolation docs list serialization anomalies as possible at Repeatable Read, and write skew is exactly that.

The difference is what each transaction writes. In a lost update, both write the same row and one silently overwrites the other. In write skew, they write different rows, so nothing overlaps, yet together they break a rule that spans both.

Bug 3: MongoDB Reads Can Be Earlier Than You Think

Outside a transaction, MongoDB's default "local" read concern lets other clients see a write before it reaches a majority of the replica set. The MongoDB read isolation docs call this read uncommitted, and after a failover that data can be rolled back. For financial or user-facing state, choose your read and write concerns deliberately.

The Solution

Isolation is not correctness. Isolation controls how concurrent transactions interact. It does not enforce your business rules. "Balance must never go below 0" belongs in a constraint or an atomic update, not in the hope that your isolation level catches it.

Pick the fix that matches the bug:

To follow along, you need PostgreSQL with two psql sessions open to reproduce the races, and MongoDB running as a replica set with the official Node.js driver. MongoDB transactions need version 4.0 or later on a replica set and 4.2 or later on a sharded cluster, and they do not run on a standalone server.

Step 1: Check Your PostgreSQL Level

SHOW default_transaction_isolation;  -- 'read committed' by default

Step 2: Let the Database Do the Math

This one change removes most lost updates:

UPDATE accounts
SET balance = balance - 30
WHERE id = 1 AND balance >= 30;

The read and the write happen in one statement, so there is no gap for another request to slip into.

Step 3: Lock Rows When Logic Sits in Between

If you run fraud checks between the read and the write, lock the row first with explicit locking:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- your checks here
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
COMMIT;

For the doctors, lock every row the decision reads, not just the one you change:

BEGIN;
SELECT id
FROM doctors
WHERE shift_id = 42 AND on_call = true
ORDER BY id
FOR UPDATE;
-- continue only if at least 2 rows came back
UPDATE doctors SET on_call = false WHERE id = 7;
COMMIT;

The ORDER BY id matters. Two transactions that lock the same rows in different orders can deadlock, so always lock in a consistent order.

Step 4: Or Go Serializable and Retry

PostgreSQL's Serializable level catches write skew for you. It aborts one of the conflicting transactions with SQLSTATE 40001 ("could not serialize access"), and your job is to retry it. Repeatable Read raises the same error when you modify a row another transaction changed, so the same retry loop covers both:

async function withSerializableRetry(pool, work, maxAttempts = 5) {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await work(client);
      await client.query("COMMIT");
      return result;
    } catch (err) {
      await client.query("ROLLBACK");
      if (err.code !== "40001" || attempt === maxAttempts) throw err;
      // random jitter so retries under contention don't collide again
      await new Promise((r) => setTimeout(r, Math.random() * 20 * 2 ** attempt));
    } finally {
      client.release();
    }
  }
}

Step 5: Use Atomic Operators in MongoDB

Single-document updates are atomic in MongoDB, so use $inc instead of read-then-write:

const res = await accounts.updateOne(
  { _id: accountId, balance: { $gte: 30 } },
  { $inc: { balance: -30 } }
);
// res.modifiedCount === 0 means the balance was too low

Step 6: Lock a Shared Document to Stop Write Skew in MongoDB

MongoDB has no FOR UPDATE. The pattern described on MongoDB's own blog is a deliberate write to a shared document that represents the resource your decision depends on. Because both transactions write the same document, MongoDB detects a write conflict and aborts one of them, and withTransaction retries it.

const { ObjectId } = require("mongodb");

const session = client.startSession();
try {
  await session.withTransaction(async () => {
    // Both Alice and Bob write here, so they conflict
    const lock = await shifts.updateOne(
      { _id: shiftId },
      { $set: { lock: new ObjectId() } },
      { session }
    );
    // No matching document means no lock was taken
    if (lock.matchedCount !== 1) throw new Error("Shift not found");

    const onCall = await doctors.countDocuments(
      { shiftId, onCall: true },
      { session }
    );
    if (onCall < 2) throw new Error("Someone has to stay on call");

    await doctors.updateOne(
      { _id: doctorId },
      { $set: { onCall: false } },
      { session }
    );
  }, { readConcern: { level: "snapshot" }, writeConcern: { w: "majority" } });
} finally {
  await session.endSession();
}

When Bob's retry runs, it sees only one doctor left and stops. Two mistakes break this pattern quietly:

• A missing session. Any call without { session } runs outside the transaction. Pass it to every operation, and do not use Promise.all inside the callback.
• Swallowed errors. withTransaction retries TransientTransactionError and UnknownTransactionCommitResult only if the error reaches it. A try/catch inside the callback that hides the write conflict turns the retry off.

Trade-offs

Every fix here costs something, and some of them are the wrong tool for your workload:

• Atomic updates only work when the whole change fits in one statement. Once your logic needs a value from another table or an external service, you are back to locks or Serializable.
• FOR UPDATE blocks other writers on those rows until you commit. A slow check inside the transaction becomes a queue, and it does not protect rows another transaction inserts while you hold the lock.
• Serializable adds no blocking beyond Repeatable Read, according to the PostgreSQL docs, but it aborts more transactions as contention rises. It only protects transactions that also run at Serializable, so a single code path left at Read Committed reopens the hole.
• A MongoDB lock field makes every transaction for that shift take turns on one document. For a hot resource that many requests touch per second, that document becomes the bottleneck, and an atomic $inc on a counter is the better design.
• Long transactions hold old snapshots open, which delays PostgreSQL's VACUUM and puts pressure on MongoDB's cache. MongoDB aborts transactions after 60 seconds by default, so keep the work inside them short.

Stay with the defaults when each write is a single atomic statement or a single-document update, and a constraint or unique index already guards the rule. If a constraint can express your rule, use it instead of any of the above, because it holds at every isolation level.

Conclusion

On Monday, search your codebase for read-modify-write patterns (an ORM load, a field change, a save) on money, inventory and scheduling data, and turn each one into an atomic update. Where a rule spans several rows or documents, lock what the decision reads, or move that path to PostgreSQL Serializable with a retry loop. Then add a test that fires parallel requests at those endpoints, because single-user tests never show these bugs. If balances or bookings still drift under load, Contact us.