Database Transactions Explained: ACID, Isolation Levels, and Race Conditions

"Wrap it in a transaction" is correct advice that's often misunderstood. A transaction guarantees your changes land together or not at all. It does not, by default, guarantee that two concurrent requests can't both read the same balance and both spend it. Understanding the difference prevents some of the nastiest bugs in production apps.

What a transaction is

A transaction groups statements into one unit:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 100);
COMMIT;

If anything fails before COMMIT — an error, a crash, a lost connection — the database rolls everything back. No money disappears halfway.

In application code, use your ORM's transaction helper so every statement runs on the same connection:

await prisma.$transaction(async (tx) => {
  await tx.account.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } })
  await tx.account.update({ where: { id: 2 }, data: { balance: { increment: 100 } } })
  await tx.transfer.create({ data: { fromId: 1, toId: 2, amount: 100 } })
})

ACID, practically

Atomicity — all or nothing (above).
Consistency — constraints (foreign keys, CHECK, UNIQUE) hold at commit. Put invariants in the schema where you can: CHECK (balance >= 0) is enforced no matter what code runs.
Isolation — how much concurrent transactions can see of each other. This is where the surprises are.
Durability — once committed, it survives a crash.

Isolation levels in PostgreSQL

Level	What it prevents	Notes
Read Committed (default)	Reading uncommitted data	Each statement sees data committed before it began
Repeatable Read	Also: data changing between statements in your transaction	Snapshot of the whole transaction; concurrent conflicting updates fail with a serialization error
Serializable	All anomalies — result equals some serial order	May abort transactions with a serialization failure; you must retry

(PostgreSQL treats Read Uncommitted as Read Committed.)

The race conditions transactions don't stop by default

Lost update (read-modify-write)

// Two concurrent requests, each in its own transaction, Read Committed
const acct = await tx.account.findUnique({ where: { id } })   // both read 100
await tx.account.update({ where: { id }, data: { balance: acct.balance - 80 } })
// both write 20 — one withdrawal is lost, and 160 was spent from 100

Both transactions read before either wrote. Wrapping it in a transaction doesn't help at Read Committed.

Fixes, simplest first:

Atomic update in SQL — let the database do the arithmetic and check the condition:
```
UPDATE accounts SET balance = balance - 80
WHERE id = $1 AND balance >= 80
RETURNING balance;
```
Zero rows returned means insufficient funds. No race: the row lock taken by UPDATE serialises the two.

Lock the row when you read it if you need logic in between:

SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;
-- other transactions block here until you commit

Optimistic concurrency — a version column, and UPDATE … WHERE id = $1 AND version = $2; zero rows means someone else changed it, so reload and retry or report a conflict. Good for user-facing edits.

Check-then-insert

const exists = await tx.user.findUnique({ where: { email } })
if (!exists) await tx.user.create({ data: { email } })   // two requests both create

Fix: a UNIQUE constraint, and handle the violation (or use INSERT … ON CONFLICT). Constraints are the only race-proof check.

Write skew

Two doctors both check "is at least one other doctor on call?" — both see yes, both go off call. Each transaction updated a different row, so row locks don't conflict. Only Serializable isolation (or explicitly locking the set being checked) prevents this class of bug.

Serializable: the retry contract

Serializable isolation is the easiest to reason about — write code as if it runs alone — at the cost of occasional aborts with SQLSTATE 40001 (serialization failure). Your code must retry the whole transaction:

async function withSerializableRetry<T>(fn: () => Promise<T>, attempts = 5): Promise<T> {
  for (let i = 0; ; i++) {
    try {
      return await fn()
    } catch (e: any) {
      const retryable = e.code === "40001" || e.code === "40P01" // serialization / deadlock
      if (!retryable || i >= attempts - 1) throw e
      await new Promise((r) => setTimeout(r, 10 * 2 ** i + Math.random() * 10))
    }
  }
}

Keep transactions short, so conflicts are rare.

Deadlocks

Transaction A locks row 1 then wants row 2; B locks row 2 then wants row 1. PostgreSQL detects this and aborts one with 40P01. Prevent it by locking rows in a consistent order (e.g. by ID), and retry on deadlock.

Mistakes that cause outages

Long transactions. Holding a transaction open while calling an external API, sending email, or waiting for a user holds locks and blocks vacuum cleanup. Keep network calls outside transactions. (Need "commit and then notify reliably"? See The Transactional Outbox Pattern.)
"Idle in transaction" connections from code paths that BEGIN and never commit on error. Set idle_in_transaction_session_timeout.
Statements outside the transaction. Using the global client instead of the tx handle inside an ORM transaction callback — the statement runs on a different connection, outside the transaction.
Transactions through a transaction-mode pooler with session features like SET or advisory locks. (Postgres Connection Pooling.)
Big migrations in one transaction that lock hot tables. (Postgres Migrations on Large Tables.)

A checklist for money-like data

Invariants enforced by constraints (CHECK, UNIQUE, foreign keys)
Balance changes are atomic SQL updates with conditions, or use FOR UPDATE
Idempotency keys for operations clients may retry
No network calls inside transactions
Serializable (with retries) where write skew is possible
Tests that run the same operation concurrently and check the totals

EasySpawn gives each project a managed Postgres in the same workspace as your app, so Claude Code can write concurrency tests that hammer an endpoint in parallel and prove the totals still add up. See how it works or join the waitlist.