Your Database Transactions Aren't Doing What You Think

I had a bug once that took two days to reproduce in staging and another day to understand. Two concurrent requests were both reading the same balance, both deciding the user had enough credit, and both deducting it. The user got twice what they should have.

Everything was wrapped in a transaction. I had checked.

The transaction wasn't the problem. The isolation level was. And I had never once thought about isolation levels because I assumed "transaction = atomic = safe." That assumption was wrong.

What a Transaction Actually Guarantees

ACID. Developers learn this acronym and think they understand transactions. Let's be honest about what each letter actually means in practice.

Atomicity, all operations in the transaction succeed, or none do. If you update two rows and the second fails, the first rolls back. This is what most people think of when they say "transaction."

Consistency, the database moves from one valid state to another. Constraints, foreign keys, check constraints, all enforced at commit time.

Isolation, transactions don't interfere with each other. This is the part most people get wrong, because isolation is not a binary. It's a spectrum.

Durability, once committed, the data survives crashes. Basically: WAL logging.

Isolation is where your concurrent bugs live. And the default isolation level in most databases is not what you'd want if you thought about it.

The Four Isolation Levels

SQL standard defines four isolation levels, each allowing progressively more anomalies:

PostgreSQL default: READ COMMITTED. MySQL (InnoDB) default: REPEATABLE READ.

Most applications run at READ COMMITTED and never think about it. This means two specific anomalies are possible in your application right now.

The Non-Repeatable Read

You read a row inside a transaction. Another transaction updates that row and commits. You read the same row again in your still-open transaction, and you get the updated value. The same query returned different results within a single transaction.

-- Transaction A (READ COMMITTED)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- returns: 500

-- Transaction B (commits between A's two reads)
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Transaction A continues
SELECT balance FROM accounts WHERE id = 1;
-- returns: 200  ← different result, same transaction
COMMIT;

In most application code this manifests as: you read data, do some logic based on it, read related data, and by the time you're making a decision, the first read is stale. You're making decisions based on data that no longer exists.

The Phantom Read

You query for all rows matching a condition. Another transaction inserts a new row that matches your condition and commits. You run the same query again in your still-open transaction, and you get an extra row that wasn't there before.

-- Transaction A (REPEATABLE READ)
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 42 AND status = 'pending';
-- returns: 2

-- Transaction B inserts a new pending order and commits

-- Transaction A continues
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
-- returns: 3 rows  ← phantom appeared

This breaks any logic that assumes "if the count was N when I started, it's still N." Approval workflows, quota checks, inventory limits, all vulnerable to phantom reads.

My Actual Bug: The Lost Update

Back to the credit bug. Here's what happened:

// Both requests ran this function simultaneously
async function deductCredit(userId: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    const user = await tx.user.findUnique({ where: { id: userId } });

    if (user.credits < amount) {
      throw new Error('Insufficient credits');
    }

    await tx.user.update({
      where: { id: userId },
      data: { credits: user.credits - amount },
    });
  });
}

Two concurrent requests, both at READ COMMITTED:

1. Request A reads: credits = 100
2. Request B reads: credits = 100 (A hasn't committed yet)
3. Request A: 100 >= 50, deducts, writes 50, commits
4. Request B: 100 >= 80, deducts, writes 20, commits

The check passed for both because both read the value before either updated it. The user ended up with 20 credits instead of 100 - 50 - 80 = deficit (which should have been rejected).

This is called a lost update. Transaction A's write got overwritten by Transaction B. Both transactions "succeeded." The data is wrong.

The Fixes (Pick the Right One)

Option 1: Optimistic locking with a version column

// Add a version column to your schema
// version Int @default(1)

async function deductCredit(userId: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    const user = await tx.user.findUnique({ where: { id: userId } });

    if (user.credits < amount) throw new Error('Insufficient credits');

    const updated = await tx.user.updateMany({
      where: { id: userId, version: user.version }, // only update if version matches
      data: { credits: user.credits - amount, version: user.version + 1 },
    });

    if (updated.count === 0) {
      throw new Error('Concurrent modification, retry');
    }
  });
}

If two requests read the same version, only one can successfully update it. The other gets count === 0 and retries. No data corruption.

Option 2: Pessimistic locking with SELECT FOR UPDATE

// PostgreSQL: lock the row so no other transaction can read-then-update it simultaneously
const user = await tx.$queryRaw`
  SELECT * FROM users WHERE id = ${userId} FOR UPDATE
`;

FOR UPDATE locks the row until your transaction commits. Concurrent requests trying to lock the same row will wait. Serializes access at the row level. Simpler logic, lower throughput under high concurrency.

Option 3: Atomic update without reading first

For the specific case of incrementing/decrementing:

// Don't read-then-write. Just write conditionally.
const updated = await prisma.user.updateMany({
  where: { id: userId, credits: { gte: amount } },
  data: { credits: { decrement: amount } },
});

if (updated.count === 0) throw new Error('Insufficient credits');

The database does the check and the update atomically. No read-then-write race condition because you never read. This works for simple numeric operations. For complex business logic where you need to read first, use Option 1 or 2.

About SERIALIZABLE

"Why not just set the isolation level to SERIALIZABLE and be done with it?"

You can. PostgreSQL's SERIALIZABLE implementation is actually excellent, it uses Serializable Snapshot Isolation (SSI), which is optimistic and doesn't block reads. In theory: just use it.

In practice, a few things:

SSI introduces serialization failures. When Postgres detects that two concurrent transactions would produce a non-serializable outcome, it aborts one with error ERROR: could not serialize access due to read/write dependencies among transactions. Your application must catch this and retry. Most apps don't handle this.

It's still not magic for distributed systems. SERIALIZABLE guarantees serial ordering within one database. If your "transaction" spans a database call, a Redis write, and a third-party API call, SERIALIZABLE on the database doesn't make the whole thing atomic.

Most ORMs don't expose it cleanly. In Prisma:

await prisma.$transaction(async (tx) => {
  // ...
}, {
  isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
});

You'll need retry logic around this:

async function withSerializableRetry<T>(fn: () => Promise<T>): Promise<T> {
  for (let i = 0; i < 5; i++) {
    try {
      return await fn();
    } catch (e: any) {
      if (e.code === 'P2034') continue; // Prisma serialization failure code
      throw e;
    }
  }
  throw new Error('Transaction failed after retries');
}

SERIALIZABLE is a real option. It's just not a drop-in fix you can set and forget.

What You Should Actually Do

For most applications:

1. Stay on READ COMMITTED, the default is fine for reads that don't drive concurrent writes.
2. Use atomic updates where possible, don't read-then-write for numeric operations.
3. Use SELECT FOR UPDATE for read-then-decide-then-write patterns on critical data (credits, inventory, seats).
4. Use optimistic locking for high-contention entities where you want writes to retry rather than block.
5. Reserve SERIALIZABLE for complex invariants where explicit locking is unwieldy, implement retry logic.

The biggest mistake is running READ COMMITTED and assuming a transaction makes your concurrent logic correct. It doesn't. The isolation level defines what concurrent transactions can see of each other. Understanding that gap is the difference between correct concurrent code and a subtle production bug that only appears at 3am under load.