PostgreSQL transactions and isolation: ACID without mystery

In short

A transaction groups several changes into one all-or-nothing action: after COMMIT every change is visible, after ROLLBACK or an error none is. The isolation level decides what a transaction sees of concurrent ones. PostgreSQL defaults to Read Committed, where each query sees fresh data, so a read-compute-write pattern loses updates. The defences are an atomic UPDATE, a SELECT … FOR UPDATE lock, a UNIQUE or EXCLUDE constraint, or the Serializable level with a retry on error 40001.

Deep diveLevel: IntermediateTransactions

After this you will be able to

wrap an operation in a transaction with BEGIN, COMMIT and ROLLBACK, and explain what happens after an error inside it
explain the difference between Read Committed, Repeatable Read and Serializable in PostgreSQL using two sessions
fix a lost update with an atomic UPDATE, SELECT … FOR UPDATE or a row version
prevent double bookings with an EXCLUDE constraint or a SERIALIZABLE transaction retried on error 40001

Know beforehand

Database design: relationships, keys and normalisation

Verified with

PostgreSQL 17 (17.11, postgres:17-alpine)Node.js 24 LTS (24.18)node-postgres (pg) 8.23TypeScript 7.0

Checked against docs: October 11, 2026

Contents

01ACID through a money transfer
02BEGIN, COMMIT, ROLLBACK
03Isolation levels and anomalies
04Lost updates and how to prevent them
05Double booking: three ways to stop it
06Retrying a transaction in the application
07What to choose, and exercises

ACID through a money transfer

Moving 300 tenge from Aigerim's account to Daniyar's takes two changes: a debit and a credit. If the server crashes between them, money disappears; if two transfers run at once, the balance can forget one of them. The PostgreSQL docs describe a transaction as bundling several steps into a single all-or-nothing operation whose intermediate states are invisible to others.

Property	What it guarantees	How PostgreSQL provides it
Atomicity	Either all of a transaction's changes apply or none do	`BEGIN` … `COMMIT` / `ROLLBACK`; an error aborts the whole transaction
Consistency	Data never breaks the schema's rules	Constraints: `CHECK`, `UNIQUE`, `FOREIGN KEY`, `EXCLUDE`
Isolation	Concurrent transactions interfere no more than the chosen level allows	MVCC (data snapshots) and isolation levels; row locks
Durability	A committed transaction survives a crash	The write-ahead log: changes reach permanent storage before COMMIT is acknowledged

Atomicity

What it guarantees: Either all of a transaction's changes apply or none do
How PostgreSQL provides it: BEGIN … COMMIT / ROLLBACK; an error aborts the whole transaction

Consistency

What it guarantees: Data never breaks the schema's rules
How PostgreSQL provides it: Constraints: CHECK, UNIQUE, FOREIGN KEY, EXCLUDE

Isolation

What it guarantees: Concurrent transactions interfere no more than the chosen level allows
How PostgreSQL provides it: MVCC (data snapshots) and isolation levels; row locks

Durability

What it guarantees: A committed transaction survives a crash
How PostgreSQL provides it: The write-ahead log: changes reach permanent storage before COMMIT is acknowledged

Keep them apart: PostgreSQL always gives you atomicity and durability, consistency only as far as the rules are written into the schema as constraints, and isolation is configurable, which is where most bugs hide. All the examples below run on PostgreSQL 17 in Docker, with real output.

bash

docker run -d --rm --name kb-pg-tranzakcii -e POSTGRES_PASSWORD=test postgres:17-alpine
docker exec -i kb-pg-tranzakcii psql -U postgres -f - < 00-setup.sql

# two terminals, two sessions: A and B
docker exec -it kb-pg-tranzakcii psql -U postgres

The bench: a PostgreSQL 17 container and two psql sessions. In the transcripts below, A=# and B=# mark commands of the first and second session. The password test is for a throwaway local container only

00-setup.sql

-- Show times in Almaty time (for new sessions)
ALTER DATABASE postgres SET timezone TO 'Asia/Almaty';

-- Accounts for the money-transfer examples
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
  id      integer PRIMARY KEY,
  owner   text          NOT NULL,
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Aigerim', 1000.00), (2, 'Daniyar', 500.00);

-- Salon staff and their bookings
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS masters;
CREATE TABLE masters (
  id   integer PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE bookings (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  master_id integer   NOT NULL REFERENCES masters (id),
  during    tstzrange NOT NULL,
  guest     text      NOT NULL
);
INSERT INTO masters VALUES (1, 'Stylist Aliya');

Accounts with CHECK (balance >= 0), salon staff and their bookings

BEGIN, COMMIT, ROLLBACK

Without an explicit BEGIN, every PostgreSQL statement runs in its own implicit transaction and commits itself: autocommit mode. To group statements, wrap them in BEGIN and COMMIT. If something goes wrong, ROLLBACK undoes everything since BEGIN.

01-atomicity.sql

-- Transfer 1500 from account 1 to account 2: account 1 holds only 1000
BEGIN;
UPDATE accounts SET balance = balance + 1500 WHERE id = 2;   -- the credit succeeds
UPDATE accounts SET balance = balance - 1500 WHERE id = 1;   -- the debit violates CHECK
SELECT balance FROM accounts WHERE id = 2;                   -- the transaction is already aborted
ROLLBACK;

SELECT id, owner, balance FROM accounts ORDER BY id;          -- nothing changed

-- A successful transfer of 300: both changes become visible only after COMMIT
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

SELECT id, owner, balance FROM accounts ORDER BY id;

A transfer that violates CHECK, and a successful transfer

terminal

$ docker exec -i kb-pg-tranzakcii sh -c 'psql -U postgres -a -f - 2>&1' < 01-atomicity.sql
-- Transfer 1500 from account 1 to account 2: account 1 holds only 1000
BEGIN;
BEGIN
UPDATE accounts SET balance = balance + 1500 WHERE id = 2;   -- the credit succeeds
UPDATE 1
UPDATE accounts SET balance = balance - 1500 WHERE id = 1;   -- the debit violates CHECK
psql:<stdin>:4: ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, Aigerim, -500.00).
SELECT balance FROM accounts WHERE id = 2;                   -- the transaction is already aborted
psql:<stdin>:5: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
ROLLBACK
SELECT id, owner, balance FROM accounts ORDER BY id;          -- nothing changed
 id |  owner  | balance
----+---------+---------
  1 | Aigerim | 1000.00
  2 | Daniyar |  500.00
(2 rows)

-- A successful transfer of 300: both changes become visible only after COMMIT
BEGIN;
BEGIN
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE 1
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
UPDATE 1
COMMIT;
COMMIT
SELECT id, owner, balance FROM accounts ORDER BY id;
 id |  owner  | balance
----+---------+---------
  1 | Aigerim |  700.00
  2 | Daniyar |  800.00
(2 rows)

Real output: -a echoes each command before its result

The 1500 credit went through, but the debit broke CHECK (balance >= 0). After an error the transaction enters the aborted state: every following command answers current transaction is aborted until ROLLBACK arrives. In the end the balances did not change, because the credit was undone too. That is atomicity.

SAVEPOINT name and ROLLBACK TO name undo part of a transaction; per the docs, it is the only way to continue a transaction after an error without rolling all of it back.
Many drivers and ORMs send BEGIN and COMMIT themselves. Check how your library does it before wrapping code in a second transaction.
Sequence values (serial, identity) are not rolled back: after ROLLBACK the numbering has a gap. That is normal, not a bug.

Isolation levels and anomalies

The SQL standard defines isolation levels through anomalies, effects that appear when transactions run at the same time. A dirty read sees another transaction's uncommitted changes. A nonrepeatable read gets a different value when it reads the same row again. A phantom read gets a different set of rows when it reruns a query with the same condition. A serialization anomaly is a result that matches no order of running the transactions one at a time.

Level	Dirty read	Nonrepeatable	Phantoms	Serialization anomaly
Read Uncommitted	not in PostgreSQL	possible	possible	possible
Read Committed (default)	no	possible	possible	possible
Repeatable Read	no	no	not in PostgreSQL	possible
Serializable	no	no	no	no

Read Uncommitted

Dirty read: not in PostgreSQL
Nonrepeatable: possible
Phantoms: possible
Serialization anomaly: possible

Read Committed (default)

Dirty read: no
Nonrepeatable: possible
Phantoms: possible
Serialization anomaly: possible

Repeatable Read

Dirty read: no
Nonrepeatable: no
Phantoms: not in PostgreSQL
Serialization anomaly: possible

Serializable

Dirty read: no
Nonrepeatable: no
Phantoms: no
Serialization anomaly: no

After Table 13.1 of the PostgreSQL 17 documentation

PostgreSQL really implements three levels: Read Uncommitted behaves like Read Committed. In Read Committed each query sees a snapshot taken when that query starts; in Repeatable Read the snapshot is taken at the transaction's first query. Two sessions show the difference: B changes the balance and A reads it twice.

terminal

A=# BEGIN;
BEGIN
A=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
 1000.00
(1 row)
B=# UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- autocommit
UPDATE 1
A=# SELECT balance FROM accounts WHERE id = 1;  -- same read, different answer
 balance
---------
 1050.00
(1 row)
A=# COMMIT;
COMMIT
A=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
 1050.00
(1 row)
B=# UPDATE accounts SET balance = balance + 50 WHERE id = 1;
UPDATE 1
A=# SELECT balance FROM accounts WHERE id = 1;  -- the snapshot does not change
 balance
---------
 1050.00
(1 row)
A=# COMMIT;
COMMIT
A=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
 1100.00
(1 row)

Real output of two psql sessions: commands were sent one at a time, A and B are separate connections

In Read Committed the second read inside one transaction returned 1050 instead of 1000: a nonrepeatable read. In Repeatable Read transaction A keeps seeing 1050 even though B has already written 1100; A sees the new value only in its next transaction. For reports that read many related tables that is useful, since every figure comes from the same moment.

Lost updates and how to prevent them

The most common bug in applications is read, compute in code, write the number back. Two transactions read a balance of 1000; the first writes 900, the second 800. Both succeed, and one debit is gone.

terminal

A=# BEGIN;
BEGIN
A=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
 1000.00
(1 row)
B=# BEGIN;
BEGIN
B=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
 1000.00
(1 row)
A=# UPDATE accounts SET balance = 900.00 WHERE id = 1;  -- 1000 − 100, computed in the app
UPDATE 1
B=# UPDATE accounts SET balance = 800.00 WHERE id = 1;  -- 1000 − 200
-- B is waiting for another transaction to finish
A=# COMMIT;
COMMIT
-- B got its answer:
UPDATE 1
B=# COMMIT;
COMMIT
A=# SELECT balance FROM accounts WHERE id = 1;  -- 300 was debited, the balance fell by 200
 balance
---------
  800.00
(1 row)

Real output: Read Committed does not protect against a lost update

B waited for A to commit (PostgreSQL never lets two transactions change one row at the same time), but then wrote its own number, computed from a stale read. There are four working fixes:

An atomic UPDATE
Compute in the database: SET balance = balance - 200. The second transaction waits for the first and applies the subtraction to the already updated row; check it in the exercise.
SELECT … FOR UPDATE
Lock the row when reading: the second transaction waits on its own SELECT and then gets the new value.
Optimistic locking
Keep a version number and update with WHERE version = …; zero updated rows means a conflict.
Repeatable Read or Serializable
The database detects the conflict itself and aborts the second transaction with code 40001; the application retries.

terminal

A=# BEGIN;
BEGIN
A=# SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
 balance
---------
 1000.00
(1 row)
B=# BEGIN;
BEGIN
B=# SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- B is waiting for another transaction to finish
A=# UPDATE accounts SET balance = 900.00 WHERE id = 1;
UPDATE 1
A=# COMMIT;
COMMIT
-- B got its answer:
 balance
---------
  900.00
(1 row)
B=# UPDATE accounts SET balance = 700.00 WHERE id = 1;  -- 900 − 200
UPDATE 1
B=# COMMIT;
COMMIT
A=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
  700.00
(1 row)

Real output: FOR UPDATE makes the second transaction wait and read the current balance

FOR UPDATE locks the selected rows until the transaction ends: other transactions cannot modify, delete or lock them. If waiting is not an option there is NOWAIT, which raises an error instead, and SKIP LOCKED, which skips locked rows. The docs note that SKIP LOCKED gives an inconsistent view of the data and suits job queues rather than general work.

03-optimistic.sql

-- Optimistic locking: a version number instead of holding a lock
ALTER TABLE accounts ADD COLUMN version integer NOT NULL DEFAULT 0;

-- Both clients have read the row: balance = 1000.00, version = 0
SELECT balance, version FROM accounts WHERE id = 1;

-- The first one saves: the version matches
UPDATE accounts SET balance = 900.00, version = version + 1
WHERE id = 1 AND version = 0;

-- The second writes with a stale version: 0 rows means a conflict; re-read and decide again
UPDATE accounts SET balance = 800.00, version = version + 1
WHERE id = 1 AND version = 0;

SELECT balance, version FROM accounts WHERE id = 1;

Optimistic locking: check the UPDATE result instead of holding a lock

terminal

$ docker exec -i kb-pg-tranzakcii sh -c 'psql -U postgres -a -f - 2>&1' < 03-optimistic.sql
 balance | version
---------+---------
 1000.00 |       0
(1 row)

UPDATE 1
UPDATE 0
 balance | version
---------+---------
  900.00 |       1
(1 row)

Output trimmed to results: the second UPDATE touched 0 rows, so the application must report a conflict

terminal

A=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
 1000.00
(1 row)
B=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
 1000.00
(1 row)
A=# UPDATE accounts SET balance = 900.00 WHERE id = 1;
UPDATE 1
B=# UPDATE accounts SET balance = 800.00 WHERE id = 1;
-- B is waiting for another transaction to finish
A=# COMMIT;
COMMIT
-- B got its answer:
ERROR:  could not serialize access due to concurrent update
B=# \echo :LAST_ERROR_SQLSTATE
40001
B=# ROLLBACK;  -- the app retries the whole transaction
ROLLBACK
A=# SELECT balance FROM accounts WHERE id = 1;
 balance
---------
  900.00
(1 row)

Real output: under Repeatable Read the same scenario ends in serialization error 40001

Double booking: three ways to stop it

Booking a salon appointment: before inserting, the application checks that the interval is free. Two customers press Book at the same moment, both checks see an empty slot, both inserts succeed. The rows are different, so row locks never kick in. This anomaly is called write skew.

terminal

A=# BEGIN;
BEGIN
A=# SELECT count(*) FROM bookings WHERE master_id = 1 AND during && '[2026-10-12 10:00+05, 2026-10-12 11:00+05)';
 count
-------
     0
(1 row)
B=# BEGIN;
BEGIN
B=# SELECT count(*) FROM bookings WHERE master_id = 1 AND during && '[2026-10-12 10:00+05, 2026-10-12 11:00+05)';
 count
-------
     0
(1 row)
A=# INSERT INTO bookings (master_id, during, guest) VALUES (1, '[2026-10-12 10:00+05, 2026-10-12 11:00+05)', 'Guest A');
INSERT 0 1
B=# INSERT INTO bookings (master_id, during, guest) VALUES (1, '[2026-10-12 10:00+05, 2026-10-12 11:00+05)', 'Guest B');
INSERT 0 1
A=# COMMIT;
COMMIT
B=# COMMIT;
COMMIT
A=# SELECT guest, during FROM bookings ORDER BY id;
  guest  |                       during
---------+-----------------------------------------------------
 Guest A | ["2026-10-12 10:00:00+05","2026-10-12 11:00:00+05")
 Guest B | ["2026-10-12 10:00:00+05","2026-10-12 11:00:00+05")
(2 rows)

Real output: under Read Committed both customers are booked for the same time

We ran the same scenario under Repeatable Read and got the same result, two bookings: snapshots do not help because each transaction writes its own row. The docs say outright that business rules enforced at this level are unlikely to work correctly without explicit locks. Three approaches do protect you.

The first is Serializable. PostgreSQL tracks read/write dependencies between transactions and aborts one when the result matches no serial order:

terminal

A=# BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
A=# SELECT count(*) FROM bookings WHERE master_id = 1 AND during && '[2026-10-12 10:00+05, 2026-10-12 11:00+05)';
 count
-------
     0
(1 row)
B=# BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
B=# SELECT count(*) FROM bookings WHERE master_id = 1 AND during && '[2026-10-12 10:00+05, 2026-10-12 11:00+05)';
 count
-------
     0
(1 row)
A=# INSERT INTO bookings (master_id, during, guest) VALUES (1, '[2026-10-12 10:00+05, 2026-10-12 11:00+05)', 'Guest A');
INSERT 0 1
B=# INSERT INTO bookings (master_id, during, guest) VALUES (1, '[2026-10-12 10:00+05, 2026-10-12 11:00+05)', 'Guest B');
INSERT 0 1
A=# COMMIT;
COMMIT
B=# COMMIT;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
A=# SELECT guest, during FROM bookings ORDER BY id;
  guest  |                       during
---------+-----------------------------------------------------
 Guest A | ["2026-10-12 10:00:00+05","2026-10-12 11:00:00+05")
(1 row)

Real output: the second transaction is aborted at COMMIT with a serialization failure; the SQLSTATE is 40001

The second is locking a shared parent row. Every booking for a staff member first locks that member's row in masters, so the checks run strictly one after another:

terminal

A=# BEGIN;
BEGIN
A=# SELECT id FROM masters WHERE id = 1 FOR UPDATE;
 id
----
  1
(1 row)
B=# BEGIN;
BEGIN
B=# SELECT id FROM masters WHERE id = 1 FOR UPDATE;
-- B is waiting for another transaction to finish
A=# SELECT count(*) FROM bookings WHERE master_id = 1 AND during && '[2026-10-12 10:00+05, 2026-10-12 11:00+05)';
 count
-------
     0
(1 row)
A=# INSERT INTO bookings (master_id, during, guest) VALUES (1, '[2026-10-12 10:00+05, 2026-10-12 11:00+05)', 'Guest A');
INSERT 0 1
A=# COMMIT;
COMMIT
-- B got its answer:
 id
----
  1
(1 row)
B=# SELECT count(*) FROM bookings WHERE master_id = 1 AND during && '[2026-10-12 10:00+05, 2026-10-12 11:00+05)';  -- the slot is taken
 count
-------
     1
(1 row)
B=# ROLLBACK;  -- the app replies "slot taken"
ROLLBACK

Real output: B waits on FOR UPDATE and, once unblocked, sees the slot is taken

The third is an EXCLUDE constraint. It generalises UNIQUE: it forbids two rows for which every listed operator returns true. Here that means the same staff member (=) and overlapping intervals (&&). The database itself performs the check, so no code path can forget it.

02-exclude.sql

-- Forbid overlapping bookings for the same staff member at the database level
CREATE EXTENSION IF NOT EXISTS btree_gist;   -- needed for "=" on integer inside GiST
ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (master_id WITH =, during WITH &&);

An exclusion constraint; the btree_gist extension ships with the standard contrib modules

terminal

A=# BEGIN;
BEGIN
A=# INSERT INTO bookings (master_id, during, guest) VALUES (1, '[2026-10-12 10:00+05, 2026-10-12 11:00+05)', 'Guest A');
INSERT 0 1
B=# BEGIN;
BEGIN
B=# INSERT INTO bookings (master_id, during, guest) VALUES (1, '[2026-10-12 10:30+05, 2026-10-12 11:30+05)', 'Guest B');
-- B is waiting for another transaction to finish
A=# COMMIT;
COMMIT
-- B got its answer:
ERROR:  conflicting key value violates exclusion constraint "bookings_no_overlap"
DETAIL:  Key (master_id, during)=(1, ["2026-10-12 10:30:00+05","2026-10-12 11:30:00+05")) conflicts with existing key (master_id, during)=(1, ["2026-10-12 10:00:00+05","2026-10-12 11:00:00+05")).
B=# \echo :LAST_ERROR_SQLSTATE
23P01
B=# ROLLBACK;
ROLLBACK

Real output: the overlapping booking is rejected with code 23P01 without any check in the application

Retrying a transaction in the application

A serialization failure is a routine signal, not an outage. The docs require applications on Repeatable Read and Serializable to be ready to retry the whole transaction, including the logic that decided which queries to send. Serialization failures always carry code 40001; deadlocks (40P01) are worth retrying too. PostgreSQL has no automatic retry, because the database cannot know which application logic has to run again.

bash

npm init -y
npm pkg set type=module
npm install pg@8
npm install -D typescript @types/pg @types/node@24
npx tsc -p .
DATABASE_URL=postgres://postgres:YOUR_PASSWORD@127.0.0.1:5432/postgres node --test test/booking.test.ts

A scratch project: Node.js 24 runs .ts files directly and tsc only checks types. YOUR_PASSWORD is the password of your test database

tsconfig.json

{
  "compilerOptions": {
    "noEmit": true,
    "target": "esnext",
    "module": "nodenext",
    "rewriteRelativeImportExtensions": true,
    "erasableSyntaxOnly": true,
    "verbatimModuleSyntax": true,
    "strict": true,
    "skipLibCheck": true,
    "types": ["node"]
  },
  "include": ["src", "test"]
}

TypeScript settings for running .ts without a build step

src/tx.ts

import type { Pool, PoolClient } from "pg";
import { setTimeout as sleep } from "node:timers/promises";

// 40001 is serialization_failure, 40P01 is deadlock_detected: both are safe to retry
const RETRYABLE = new Set(["40001", "40P01"]);

type Options = { maxAttempts?: number; onRetry?: (attempt: number, code: string) => void };

/** Runs fn in a SERIALIZABLE transaction and retries the whole thing on a serialization failure. */
export async function serializable<T>(pool: Pool, fn: (client: PoolClient) => Promise<T>, opts: Options = {}): Promise<T> {
  const maxAttempts = opts.maxAttempts ?? 5;
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await fn(client);
      await client.query("COMMIT"); // a 40001 error can arrive here too
      return result;
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {});
      const code = (err as { code?: string }).code ?? "";
      if (!RETRYABLE.has(code) || attempt >= maxAttempts) throw err;
      opts.onRetry?.(attempt, code);
      await sleep(Math.random() * 20 * attempt); // a small random spread between retries
    } finally {
      client.release();
    }
  }
}

A wrapper: SERIALIZABLE, ROLLBACK and a retry on 40001/40P01 with a cap on attempts

src/booking.ts

import type { Pool } from "pg";
import { serializable } from "./tx.ts";

export type BookResult = "booked" | "taken";

/** Books a guest with a staff member if the interval [from, to) is free. */
export function bookSlot(pool: Pool, masterId: number, from: string, to: string, guest: string, onRetry?: (attempt: number, code: string) => void): Promise<BookResult> {
  return serializable(pool, async (client) => {
    const busy = await client.query(
      "SELECT 1 FROM bookings WHERE master_id = $1 AND during && tstzrange($2, $3)",
      [masterId, from, to],
    );
    if (busy.rowCount) return "taken";
    await client.query(
      "INSERT INTO bookings (master_id, during, guest) VALUES ($1, tstzrange($2, $3), $4)",
      [masterId, from, to, guest],
    );
    return "booked";
  }, { onRetry });
}

Booking: the check and the insert inside one serializable transaction

test/booking.test.ts

import { test, before, after, beforeEach } from "node:test";
import assert from "node:assert/strict";
import pg from "pg";
import { bookSlot } from "../src/booking.ts";
import { serializable } from "../src/tx.ts";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
// warm-up: open all 10 connections up front so the transactions really run in parallel
before(() => Promise.all(Array.from({ length: 10 }, () => pool.query("SELECT pg_sleep(0.05)"))));
after(() => pool.end());
beforeEach(() => pool.query("DELETE FROM bookings"));

const FROM = "2026-10-12 10:00+05";
const TO = "2026-10-12 11:00+05";

test("20 simultaneous attempts to take one slot: exactly one booking", async () => {
  let retries = 0;
  const results = await Promise.all(
    Array.from({ length: 20 }, (_, i) => bookSlot(pool, 1, FROM, TO, `guest-${i}`, () => retries++)),
  );
  const { rows } = await pool.query("SELECT count(*)::int AS n FROM bookings");
  console.log({ booked: results.filter((r) => r === "booked").length, taken: results.filter((r) => r === "taken").length, retries });
  assert.equal(results.filter((r) => r === "booked").length, 1);
  assert.equal(rows[0].n, 1);
});

test("without retries some requests fail with 40001", async () => {
  const settled = await Promise.allSettled(
    Array.from({ length: 20 }, (_, i) =>
      serializable(pool, async (c) => {
        const busy = await c.query("SELECT 1 FROM bookings WHERE master_id = 1 AND during && tstzrange($1, $2)", [FROM, TO]);
        if (busy.rowCount) return "taken";
        await c.query("INSERT INTO bookings (master_id, during, guest) VALUES (1, tstzrange($1, $2), $3)", [FROM, TO, `guest-${i}`]);
        return "booked";
      }, { maxAttempts: 1 }),
    ),
  );
  const codes = settled.filter((s) => s.status === "rejected").map((s) => (s.reason as { code?: string }).code);
  const { rows } = await pool.query("SELECT count(*)::int AS n FROM bookings");
  console.log({ failed: codes.length, codes: [...new Set(codes)] });
  assert.ok(codes.length > 0);
  assert.ok(codes.every((c) => c === "40001"));
  assert.equal(rows[0].n, 1);
});

Tests on the built-in node:test: 20 parallel attempts with and without retries

terminal

$ node --test test/booking.test.ts
{ booked: 1, taken: 19, retries: 9 }
✔ 20 simultaneous attempts to take one slot: exactly one booking (107.0385ms)
{ failed: 9, codes: [ '40001' ] }
✔ without retries some requests fail with 40001 (11.4972ms)
ℹ tests 2
ℹ pass 2
ℹ fail 0
ℹ duration_ms 251.7244

Real output: with retries 9 transactions restarted and answered taken; without retries 9 failed with 40001

Keep transactions short: no HTTP calls, emails or waiting for the user inside. The docs advise against leaving connections idle in transaction and point to the idle_in_transaction_session_timeout setting.
External side effects belong after a successful COMMIT, otherwise a retry sends the email twice.
Cap the number of attempts and add a random pause: under heavy contention the first retry is not guaranteed to succeed.
Retry 23505 and 23P01 with care: they may signal a permanent conflict rather than a transient one.
The example is simplified: in production, release a connection whose link broke with an error (client.release(err)) so the pool does not reuse it.

What to choose, and exercises

Task	Solution	Cost
Change a number relative to its current value	An atomic `UPDATE … SET x = x - $1`	Almost free
Read, decide in code and write one row	`SELECT … FOR UPDATE` or a row version	Waiting, or a client-side retry
Uniqueness or non-overlap	`UNIQUE` / `EXCLUDE`	An index on writes; handling `23505`/`23P01`
A complex rule across several rows and tables	`SERIALIZABLE` + retry	Retries under contention, small tracking overhead
A consistent report over many tables	`REPEATABLE READ` (or `SERIALIZABLE READ ONLY DEFERRABLE`)	The snapshot is held until the transaction ends

Change a number relative to its current value

Solution: An atomic UPDATE … SET x = x - $1
Cost: Almost free

Read, decide in code and write one row

Solution: SELECT … FOR UPDATE or a row version
Cost: Waiting, or a client-side retry

Uniqueness or non-overlap

Solution: UNIQUE / EXCLUDE
Cost: An index on writes; handling 23505/23P01

A complex rule across several rows and tables

Solution: SERIALIZABLE + retry
Cost: Retries under contention, small tracking overhead

A consistent report over many tables

Solution: REPEATABLE READ (or SERIALIZABLE READ ONLY DEFERRABLE)
Cost: The snapshot is held until the transaction ends

01BEGIN
02Reads and checks
03Changes
04COMMIT
05Side effects

A short transaction: everything external happens after COMMIT

RepeatReproduce the lost update

Open two psql sessions and repeat the scenario from the lost-update section. Then, in both sessions, replace writing a number with an atomic subtraction (balance - 100 and balance - 200). What balance do you get?

Hint

The second session still waits for the first; look at which value it applies the subtraction to.

Solution

700.00: the second transaction waited for the first to commit and subtracted 200 from the already updated 900. Our run gave the same result.

SQL

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- A
UPDATE accounts SET balance = balance - 200 WHERE id = 1;  -- B

ModifyCrossing transfers

Session A transfers from account 1 to account 2 and session B from account 2 to account 1; each first debits its own account, then credits the other one. Run the steps in turn and explain the error. How do you change the transactions to avoid it?

Hint

Per the docs, the best defence against deadlocks is to acquire locks in the same order everywhere.

Solution

One of the sessions gets ERROR: deadlock detected (code 40P01): each holds the row the other is waiting for. The fix is to lock both rows in the same order at the start of both transactions; then the second one simply waits for the first.

SQL

BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Next steps: query optimisation with EXPLAIN (slow queries stretch transactions and raise conflict rates), and schema design with constraints that protect the data without relying on code.

How this was verified3 · passed1 · not runchecked 11 October 2026

“Run” means the code was executed in a scratch project or container and the output in the article comes from that run. “Partial” means it was checked against stubs instead of external services. “Not run” means it needs a device, an account or a key; the steps were checked against the official documentation. The verification project files and run logs are kept in the InterLAB archive.

Transactions and isolation: ACID without the mystery

ACID through a money transfer

BEGIN, COMMIT, ROLLBACK

Isolation levels and anomalies

Lost updates and how to prevent them

Double booking: three ways to stop it

Retrying a transaction in the application

What to choose, and exercises

Sources

Web Application Development

Database design: relationships, keys and normalisation

Indexes: speeding up queries without slowing down writes

Query optimisation: EXPLAIN ANALYZE in practice

Backend application layers: controller, service, repository

System design by example: a booking service and scaling it

Got a project?
We'll scope and estimate it — for free.

01ACID through a money transfer

02BEGIN, COMMIT, ROLLBACK

03Isolation levels and anomalies

04Lost updates and how to prevent them

05Double booking: three ways to stop it

06Retrying a transaction in the application

07What to choose, and exercises

Sources

Web Application Development

Database design: relationships, keys and normalisation

Indexes: speeding up queries without slowing down writes

Query optimisation: EXPLAIN ANALYZE in practice

Backend application layers: controller, service, repository

System design by example: a booking service and scaling it

Got a project?We'll scope and estimate it — for free.

ACID through a money transfer

BEGIN, COMMIT, ROLLBACK

Isolation levels and anomalies

Lost updates and how to prevent them

Double booking: three ways to stop it

Retrying a transaction in the application

What to choose, and exercises

Got a project?
We'll scope and estimate it — for free.