Transactions and isolation levels, from ACID to write skew

HOW IT FITS TOGETHER

KEY TERMS

ACID

Atomicity: all writes commit or none do. Consistency: constraints (FK, UNIQUE, CHECK) hold at commit. Isolation: concurrent transactions do not see each other's partial work, to a degree set by the isolation level. Durability: once COMMIT returns, the data survives a crash (WAL fsync). Isolation is the only one with a dial, and the default is not the strongest setting.

MVCC

Updates create a new row version instead of overwriting; each transaction sees the versions committed before its snapshot. Readers never block writers and writers never block readers. Writers still block writers on the same row, and old versions accumulate until VACUUM (PostgreSQL) or purge (InnoDB undo logs) can remove them, which a long-open transaction prevents.

The anomalies

Dirty read: seeing uncommitted data. Non-repeatable read: the same row read twice gives different values. Phantom: the same predicate query returns a different set of rows. Lost update: two read-modify-write cycles and one overwrites the other. Write skew: two transactions read an overlapping set, each writes a different row, and together they break an invariant neither broke alone (the last two doctors both go off call).

PostgreSQL levels

Read Committed (default) takes a new snapshot per statement; an UPDATE that hits a concurrently changed row waits, then re-evaluates its WHERE on the new version. Repeatable Read is snapshot isolation: one snapshot per transaction, and writing a row someone else changed since raises 40001 "could not serialize access due to concurrent update". Read Uncommitted behaves as Read Committed.

Serializable (SSI) and retries

PostgreSQL's Serializable is Repeatable Read plus predicate-lock tracking (SIReadLock) that never blocks but aborts one transaction when read/write dependencies form a cycle. Failures always use SQLSTATE 40001 and may surface at COMMIT, so the whole transaction, including its reads, must be retried. Keep transactions short, mark read-only ones READ ONLY, and prefer index scans: sequential scans take relation-level predicate locks and raise the failure rate.

Explicit row locks

SELECT ... FOR UPDATE locks the returned rows until the transaction ends, so a competing FOR UPDATE or UPDATE waits. FOR NO KEY UPDATE is weaker and does not block inserts that reference the row by foreign key. NOWAIT errors instead of waiting; SKIP LOCKED silently omits locked rows, which is wrong for general reads but ideal for job queues.

Optimistic concurrency

Store a version (integer or updated_at) with each row, send it to the client, and write with UPDATE ... WHERE id = $1 AND version = $2. Zero rows updated means someone else won; return 409 or 412 and let the user or code re-read. It holds no locks across user think time and maps to HTTP ETag / If-Match.

IN YOUR STACK

-- Transfer: lock both rows, always in id order, so two opposite
-- transfers cannot deadlock (A locks 1 then 2; B also locks 1 then 2).
BEGIN;
SELECT id, balance
FROM accounts
WHERE id IN ($1, $2)
ORDER BY id
FOR UPDATE;
-- application checks the from-account balance here, then:
UPDATE accounts SET balance = balance - $3 WHERE id = $1;
UPDATE accounts SET balance = balance + $3 WHERE id = $2;
COMMIT;

-- Often no explicit lock is needed: make the check part of the write.
UPDATE accounts
SET balance = balance - $2
WHERE id = $1 AND balance >= $2
RETURNING balance;            -- zero rows = insufficient funds

-- Fail fast instead of queuing behind a long holder.
SELECT * FROM accounts WHERE id = $1 FOR UPDATE NOWAIT;  -- 55P03 if locked

import { Pool } from "pg";
const pool = new Pool();

export class VersionConflict extends Error {}

// The client sent back the version it loaded (e.g. in an If-Match header).
export async function saveDocument(id: string, body: string, expectedVersion: number) {
  const { rows } = await pool.query<{ version: number }>(
    `UPDATE documents
       SET body = $2, version = version + 1, updated_at = now()
     WHERE id = $1 AND version = $3
     RETURNING version`,
    [id, body, expectedVersion],
  );
  if (rows.length === 0) throw new VersionConflict(`document ${id} changed`); // -> 409 / 412
  return rows[0].version; // new ETag
}

import { Pool, type PoolClient } from "pg";
const pool = new Pool();
const RETRYABLE = new Set(["40001", "40P01"]); // serialization failure, deadlock

export async function serializable<T>(work: (c: PoolClient) => Promise<T>, attempts = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect(); // one client for the whole transaction
    try {
      await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await work(client);
      await client.query("COMMIT"); // SSI conflicts can surface here too
      return result;
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {});
      const code = (err as { code?: string }).code;
      if (!code || !RETRYABLE.has(code) || attempt === attempts) throw err;
      await new Promise((r) => setTimeout(r, 2 ** attempt * 10 + Math.random() * 25));
    } finally {
      client.release();
    }
  }
}

// Write skew guard: at least one doctor must remain on call.
await serializable(async (c) => {
  const { rows } = await c.query("SELECT count(*)::int AS n FROM shifts WHERE day = $1 AND on_call", [day]);
  if (rows[0].n < 2) throw new Error("last doctor on call");
  await c.query("UPDATE shifts SET on_call = false WHERE day = $1 AND doctor_id = $2", [day, doctorId]);
});

-- Claim up to 10 due jobs that no other worker holds, in one short transaction.
WITH next AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending' AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs AS j
SET status = 'running', locked_by = $1, locked_at = now(), attempts = j.attempts + 1
FROM next
WHERE j.id = next.id
RETURNING j.id, j.payload, j.attempts;

-- Process outside the transaction, then mark done (idempotently).
UPDATE jobs SET status = 'done' WHERE id = $1 AND locked_by = $2;

-- Reaper: requeue jobs whose worker died mid-flight.
UPDATE jobs SET status = 'pending', locked_by = NULL
WHERE status = 'running' AND locked_at < now() - interval '10 minutes';

import { Prisma, PrismaClient } from "../prisma/generated/client";
const prisma = new PrismaClient({ adapter }); // driver adapter, e.g. @prisma/adapter-pg

export async function bookSeat(eventId: string, userId: string) {
  for (let attempt = 1; attempt <= 5; attempt++) {
    try {
      return await prisma.$transaction(
        async (tx) => {
          const taken = await tx.booking.count({ where: { eventId } });
          const event = await tx.event.findUniqueOrThrow({ where: { id: eventId } });
          if (taken >= event.capacity) throw new Error("sold out");
          return tx.booking.create({ data: { eventId, userId } });
        },
        {
          isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
          maxWait: 2000, // wait for a connection (default 2000 ms)
          timeout: 5000, // max transaction run time (default 5000 ms)
        },
      );
    } catch (e) {
      const conflict = e instanceof Prisma.PrismaClientKnownRequestError && e.code === "P2034";
      if (!conflict || attempt === 5) throw e; // P2034: write conflict or deadlock
    }
  }
  throw new Error("unreachable");
}

from decimal import Decimal
from django.db import transaction
from django.db.models import F

def withdraw(account_id: int, amount: Decimal) -> None:
    with transaction.atomic():
        # Lock is taken when the queryset is evaluated (.get), held until the block exits.
        account = Account.objects.select_for_update().get(pk=account_id)
        if account.balance < amount:
            raise InsufficientFunds()
        account.balance -= amount
        account.save(update_fields=["balance"])

def withdraw_atomic(account_id: int, amount: Decimal) -> None:
    # Same guarantee in one statement, no lock held in Python.
    updated = Account.objects.filter(pk=account_id, balance__gte=amount).update(
        balance=F("balance") - amount
    )
    if updated == 0:
        raise InsufficientFunds()

def claim_jobs(limit: int = 10) -> list[Job]:
    with transaction.atomic():
        jobs = list(Job.objects.select_for_update(skip_locked=True)
                    .filter(status="pending").order_by("run_at")[:limit])
        Job.objects.filter(pk__in=[j.pk for j in jobs]).update(status="running")
        return jobs

WHEN TO REACH FOR WHAT

WHERE IT BITES

• Assuming the default level is safe for read-modify-write. PostgreSQL defaults to Read Committed and MySQL to Repeatable Read, and neither stops a SELECT-then-UPDATE lost update; MySQL does not even raise an error.
• Raising the isolation level without a retry loop. Repeatable Read and Serializable turn races into 40001 errors, which become 500s unless a central wrapper retries the whole transaction.
• Calling external services inside a transaction. Locks and snapshots are held for the duration of the call, retries replay the side effect, and a timeout leaves the call done but the transaction rolled back. Use an outbox.
• Leaving connections idle in transaction. They hold locks and block VACUUM; set idle_in_transaction_session_timeout and make sure every code path commits or rolls back.
• Locking rows in inconsistent order across code paths, which produces deadlocks (40P01). Lock in a deterministic order such as primary key.
• Mixing plain and locking reads in one MySQL Repeatable Read transaction: plain SELECTs read the snapshot while FOR UPDATE and UPDATE read the latest committed rows, so the transaction sees two different states.

WHEN IT BREAKS IN PRODUCTION

• Concurrent writes fail with "deadlock detected"
• A retry creates two payments
• The database and the queue disagree after a failure
• One bad message blocks or floods the queue
• A background job runs on every replica
• Requests hang waiting for a database connection

SOURCES

• PostgreSQL 18: Transaction Isolation (opens in new tab)Checked 2026-11-06
• PostgreSQL 18: SELECT (locking clause) (opens in new tab)Checked 2026-11-06
• MySQL 8.4: InnoDB Transaction Isolation Levels (opens in new tab)Checked 2026-11-06
• Prisma ORM 7: Transactions and batch queries (opens in new tab)Checked 2026-11-06
• Django: select_for_update() (opens in new tab)Checked 2026-11-06
• node-postgres: Transactions (opens in new tab)Checked 2026-11-06