Md Nayeem Hossain | Software Engineer

Transactions and Isolation Levels Explained with Real Bugs#

Most developers learn that wrapping code in a transaction makes it safe. Then they ship a bug where two concurrent requests both succeed and the account balance goes negative — inside a transaction.

Transactions guarantee atomicity: all or nothing. What they guarantee about concurrency depends on the isolation level, and the default is weaker than most people assume.

The bug#

An affiliate requests a payout. The code checks the balance and then deducts it:

1await dataSource.transaction(async (tx) => {2  const wallet = await tx.findOneByOrFail(Wallet, { affiliateId });3  if (wallet.balance < amount) throw new BadRequestException("Insufficient balance");4 5  wallet.balance -= amount;6  await tx.save(wallet);7  await tx.insert(Payout, { affiliateId, amount });8});

Two payout requests for the full balance arrive at the same moment. Both read a balance of 100. Both pass the check. Both write 0 and insert a payout. The affiliate was paid twice.

This is a lost update, and PostgreSQL's default isolation level, Read Committed, allows it.

What each isolation level prevents#

Read Committed (PostgreSQL default): each statement sees data committed before that statement began. Prevents dirty reads, but two transactions can read the same row and overwrite each other's work.
Repeatable Read: the whole transaction sees one consistent snapshot. In PostgreSQL, if you try to update a row that another transaction changed after your snapshot, you get a serialization error instead of a silent overwrite.
Serializable: the result is as if transactions ran one after another. PostgreSQL detects dangerous patterns and aborts one transaction.

Higher isolation means fewer anomalies but more transactions failing with serialization errors, which you must retry.

Fix 1: let the database do the arithmetic#

The simplest fix for counters and balances is an atomic conditional update:

sql

1UPDATE wallets2SET balance = balance - $23WHERE affiliate_id = $1 AND balance >= $24RETURNING balance;

If zero rows are returned, the balance was insufficient. The check and the write happen in one statement, so there is no window between them. This is my first choice whenever the logic fits in SQL.

Fix 2: lock the row#

When the logic is too complex for one statement, take a row lock before reading:

1const wallet = await tx2  .getRepository(Wallet)3  .createQueryBuilder("w")4  .setLock("pessimistic_write")5  .where("w.affiliateId = :affiliateId", { affiliateId })6  .getOneOrFail();

That generates SELECT ... FOR UPDATE. The second request waits until the first commits, then reads the updated balance. Keep these transactions short, and always lock rows in a consistent order to avoid deadlocks.

Fix 3: optimistic concurrency#

For records edited by users — a document, a settings page — locking is too heavy. Add a version column and include it in the update:

sql

1UPDATE projects2SET title = $2, version = version + 13WHERE id = $1 AND version = $3;

If zero rows changed, someone else saved first. Return a conflict and let the user reload, instead of silently overwriting their colleague's changes.

Another real bug: write skew#

Rule: at least one doctor must be on call. Two doctors are on call, and both request to go off call at the same time. Each transaction checks "is anyone else on call?", sees one other doctor, and proceeds. Now nobody is on call.

No single row was overwritten, so row locks on the updated rows do not help. This is write skew. The fixes are Serializable isolation, locking the set of rows you read, or modelling the rule as a constraint the database can enforce.

Retrying serialization failures#

If you use Repeatable Read or Serializable, some transactions will fail with SQLSTATE 40001. That is expected behavior, not a bug, and the correct response is to retry the whole transaction:

1async function withRetry<T>(fn: () => Promise<T>, attempts = 3): Promise<T> {2  for (let i = 1; ; i++) {3    try {4      return await fn();5    } catch (err: any) {6      if (err.code !== "40001" || i === attempts) throw err;7      await sleep(20 * i + Math.random() * 20);8    }9  }10}

Retry the entire transaction function, never a single statement inside it.

Constraints are concurrency control too#

Unique constraints, check constraints and exclusion constraints are enforced by the database under any isolation level. A CHECK (balance >= 0) constraint would have turned the double payout into a failed transaction instead of a financial loss. Encode invariants in the schema whenever you can; it is the last line of defense.

Conclusion#

A transaction alone does not make concurrent code safe. Know that your database defaults to Read Committed, prefer atomic conditional updates, lock rows when you must read before writing, use version columns for user edits, retry serialization failures, and back everything with constraints. Concurrency bugs are rare in testing and common in production — design them out up front.