Short answers to what readers ask most about this topic.
What database transaction isolation levels are and which to use. PostgreSQL READ COMMITTED, REPEATABLE READ and SERIALIZABLE, shown with real two-session output and a retry loop.
Matthews Wong
Software Engineer
Key Takeaway
Isolation levels decide which concurrency anomalies one transaction can observe from another. PostgreSQL defaults to READ COMMITTED, which blocks dirty reads. REPEATABLE READ adds one stable snapshot and blocks phantoms. SERIALIZABLE also blocks write skew. Use READ COMMITTED with row locks by default, and SERIALIZABLE with retries on error 40001 for rules spanning rows.
Picture two cashiers closing out the same customer balance at the same second. Each reads 100, each subtracts its own amount in application code, each writes the result back. One subtraction vanishes and nobody gets an error. That is a lost update, and it is the most common isolation bug in ordinary POS and ERP code.
This post answers the question directly: what the isolation levels are, what each one prevents in PostgreSQL, and which to pick. I ran every transcript below on a local PostgreSQL 16.15 with two psql sessions, so the output is pasted, not typed from memory. The rules come from the PostgreSQL manual, and the manual is quoted where it matters.
A dirty read is reading data another transaction has written but not committed. A non-repeatable read is reading the same row twice in one transaction and getting two values because someone committed in between. A phantom read is the same thing for a set of rows: the same WHERE clause returns a different row set the second time.
Write skew is the one most tutorials skip. Two transactions each read an overlapping set of rows, each decide the rule still holds, and each write to a different row. Neither overwrites the other, so no row-level conflict exists, yet the combined result breaks the rule. The manual calls the general class a serialization anomaly. This table shows which of the four can happen at each level in PostgreSQL.
| Anomaly | READ COMMITTED | REPEATABLE READ | SERIALIZABLE |
|---|---|---|---|
| Dirty read | Not possible | Not possible | Not possible |
| Non-repeatable read | Possible | Not possible | Not possible |
| Phantom read | Possible | Not possible in PostgreSQL | Not possible |
| Write skew (serialization anomaly) | Possible | Possible | Not possible |
Two PostgreSQL specifics change how you read the SQL standard. The manual says its READ UNCOMMITTED mode behaves like READ COMMITTED, so dirty reads cannot happen at any level. And it says its REPEATABLE READ does not allow phantom reads, which the standard would permit. Only three levels really exist.
READ COMMITTED is the default. Each statement sees a snapshot of everything committed before that statement began, so you never see half-finished work, but two statements in one transaction can see different worlds. Session 1 below runs the same query twice and gets 100, then 150, because session 2 committed in between.
-- Setup (PostgreSQL 16.15, two psql sessions)
CREATE TABLE accounts (id int PRIMARY KEY, balance int NOT NULL);
INSERT INTO accounts VALUES (1, 100);
-- READ COMMITTED: every statement takes a fresh snapshot
s1=# BEGIN ISOLATION LEVEL READ COMMITTED;
s1=# SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
s2=# UPDATE accounts SET balance = 150 WHERE id = 1; -- autocommit, so committed at once
s1=# SELECT balance FROM accounts WHERE id = 1; -- same transaction, same query
balance
---------
150 -- non-repeatable readThat behaviour is fine for a single statement and dangerous for read-then-write logic. The worked example: the balance is 100, one request takes 30 and another takes 50, so the right answer is 100 - 30 - 50 = 20. Both requests read 100 first, compute 70 and 50 in the application, and write. The last writer wins and the table ends at 50.
-- Lost update at READ COMMITTED: both read 100, both compute in the app, both write.
-- Intended result: 100 - 30 - 50 = 20.
s1=# BEGIN; s2=# BEGIN;
s1=# SELECT balance FROM accounts WHERE id = 1; -- 100
s2=# SELECT balance FROM accounts WHERE id = 1; -- 100
s1=# UPDATE accounts SET balance = 70 WHERE id = 1; -- app computed 100 - 30
s1=# COMMIT;
s2=# UPDATE accounts SET balance = 50 WHERE id = 1; -- app computed 100 - 50, stale read
s2=# COMMIT;
s1=# SELECT balance FROM accounts WHERE id = 1;
50 -- the 30 is goneNo error, no warning, and 30 is gone. The isolation level did exactly what READ COMMITTED promises. The mistake is the read-modify-write cycle spread over two round trips, which is why the next two sections are about closing that gap.
Yes in behaviour. A REPEATABLE READ transaction takes one snapshot at its first query and keeps it until it ends, so the second SELECT below still returns 150 after session 2 committed 200. The price shows up on write: if you try to update a row that changed since your snapshot, PostgreSQL refuses with SQLSTATE 40001 instead of silently overwriting it.
-- REPEATABLE READ: one snapshot, taken at the first query
s1=# BEGIN ISOLATION LEVEL REPEATABLE READ;
s1=# SELECT balance FROM accounts WHERE id = 1;
150
s2=# UPDATE accounts SET balance = 200 WHERE id = 1; -- committed by session 2
s1=# SELECT balance FROM accounts WHERE id = 1;
150 -- still the snapshot value
s1=# UPDATE accounts SET balance = balance + 10 WHERE id = 1;
ERROR: could not serialize access due to concurrent update
s1=# ROLLBACK; -- SQLSTATE 40001, retry the transactionThe same snapshot closes the phantom gap, which the SQL standard does not require at this level. A row inserted and committed by another session stays invisible, while the identical query at READ COMMITTED sees it immediately.
CREATE TABLE orders (id serial PRIMARY KEY, status text NOT NULL);
INSERT INTO orders (status) VALUES ('open'), ('open');
-- REPEATABLE READ: no phantom in PostgreSQL
s1=# BEGIN ISOLATION LEVEL REPEATABLE READ;
s1=# SELECT count(*) FROM orders WHERE status = 'open';
2
s2=# INSERT INTO orders (status) VALUES ('open');
s1=# SELECT count(*) FROM orders WHERE status = 'open';
2 -- the new row is invisible
s1=# COMMIT;
-- READ COMMITTED: phantom rows appear
s1=# BEGIN ISOLATION LEVEL READ COMMITTED;
s1=# SELECT count(*) FROM orders WHERE status = 'open';
3
s2=# INSERT INTO orders (status) VALUES ('open');
s1=# SELECT count(*) FROM orders WHERE status = 'open';
4 -- a phantom: the row set changedREPEATABLE READ therefore fixes the lost update for free, as long as your code retries on 40001. It does not fix write skew, which is the next-but-one section. Keep in mind that this protection is PostgreSQL behaviour. Other databases implement the same level name differently, so check the manual of the engine you run.
Lock the row before you read it. SELECT ... FOR UPDATE takes a row lock, so the second session waits at its own SELECT and, once the first commits, reads the new value 70 instead of the stale 100. Then its application arithmetic is correct: 70 - 50 = 20, matching the 100 - 30 - 50 we wanted.
-- Fix 1: lock the row before reading it
s1=# BEGIN; s2=# BEGIN;
s1=# SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 100, row locked
s2=# SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- blocks, no output yet
s1=# UPDATE accounts SET balance = 70 WHERE id = 1;
s1=# COMMIT; -- releases the lock
-- session 2 unblocks and returns 70
s2=# UPDATE accounts SET balance = 20 WHERE id = 1; -- app computed 70 - 50
s2=# COMMIT;
s1=# SELECT balance FROM accounts WHERE id = 1;
20 -- 100 - 30 - 50
-- Fix 2: no read at all. One statement, the database does the arithmetic.
UPDATE accounts SET balance = balance - 50 WHERE id = 1 AND balance >= 50 RETURNING balance;The cheaper fix is to remove the read. A single UPDATE accounts SET balance = balance - 50 WHERE balance >= 50 RETURNING balance does the arithmetic inside the database under the row lock. If a concurrent writer committed first, PostgreSQL re-checks the WHERE clause against the newest row version, so the balance check stays correct. Zero rows returned means the guard failed.
Use FOR UPDATE when the decision needs more than one column or table, and the single atomic UPDATE whenever one statement can express it. Both work at READ COMMITTED. The manual's explicit locking chapter lists the other modes, including FOR SHARE and SKIP LOCKED.
Take a rule that at least one cashier must stay on duty. Alice and Budi are both on duty. Each transaction counts the cashiers on duty, sees 2, decides it is safe to leave, and updates its own row. At REPEATABLE READ both commit and the count ends at 0. The rows they wrote are different, so row-level checks never fire.
CREATE TABLE shifts (cashier text PRIMARY KEY, on_duty boolean NOT NULL);
INSERT INTO shifts VALUES ('alice', true), ('budi', true);
-- Rule the app enforces: at least one cashier must stay on duty.
-- REPEATABLE READ: both transactions see 2 on duty, each removes a DIFFERENT row
s1=# BEGIN ISOLATION LEVEL REPEATABLE READ; s2=# BEGIN ISOLATION LEVEL REPEATABLE READ;
s1=# SELECT count(*) FROM shifts WHERE on_duty; -- 2
s2=# SELECT count(*) FROM shifts WHERE on_duty; -- 2
s1=# UPDATE shifts SET on_duty = false WHERE cashier = 'alice';
s2=# UPDATE shifts SET on_duty = false WHERE cashier = 'budi'; -- different row, no conflict
s1=# COMMIT;
s2=# COMMIT; -- both succeed
s1=# SELECT count(*) FROM shifts WHERE on_duty;
0 -- the rule is broken
-- Same script after resetting both rows to true, with BEGIN ISOLATION LEVEL SERIALIZABLE
-- in both sessions. The two SELECTs and two UPDATEs run exactly as above, then:
s1=# COMMIT; -- succeeds
s2=# 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.
s1=# SELECT cashier, on_duty FROM shifts ORDER BY cashier;
alice | f
budi | t -- one cashier is still on dutySERIALIZABLE catches it. PostgreSQL implements it with Serializable Snapshot Isolation, which tracks read and write dependencies between concurrent transactions without blocking them, and cancels one when a dangerous pattern forms. In the run above the second COMMIT failed with a serialization error, and the manual's HINT says the transaction might succeed if retried. A retry re-reads a count of 1 and correctly refuses.
If the rule can be expressed as a constraint, prefer the constraint. A unique index or an exclusion constraint enforces it at every isolation level with no retry loop. Reach for SERIALIZABLE when the invariant spans several rows and no constraint can say it.
The manual says applications at REPEATABLE READ and SERIALIZABLE must be prepared to retry on SQLSTATE 40001, and that deadlocks (40P01) are also worth retrying. The retry must re-run the complete transaction, including the logic that decides which SQL to issue, because PostgreSQL cannot do that safely for you. This wrapper does it with the node-postgres client.
import { Pool, PoolClient } from "pg";
type Level = "READ COMMITTED" | "REPEATABLE READ" | "SERIALIZABLE";
// 40001 serialization_failure, 40P01 deadlock_detected: the two codes PostgreSQL says to retry.
const RETRYABLE = new Set(["40001", "40P01"]);
const MAX_ATTEMPTS = 5;
const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));
export async function withTransaction<T>(
pool: Pool,
level: Level,
work: (client: PoolClient) => Promise<T>,
): Promise<T> {
for (let attempt = 1; attempt <= MAX_ATTEMPTS; attempt++) {
const client = await pool.connect();
try {
await client.query("BEGIN ISOLATION LEVEL " + level);
// The WHOLE unit of work runs again on a retry: reads, decisions and writes.
const result = await work(client);
await client.query("COMMIT"); // a serializable failure can surface HERE
return result;
} catch (err) {
await client.query("ROLLBACK").catch(() => undefined);
const code = (err as { code?: string }).code;
if (code && RETRYABLE.has(code) && attempt < MAX_ATTEMPTS) {
// Jitter stops two losers from colliding again in lockstep.
await sleep(Math.random() * 25 * 2 ** attempt);
continue;
}
throw err; // not retryable, or out of attempts
} finally {
client.release();
}
}
throw new Error("unreachable");
}
// Usage: the on-duty check and the update live inside the callback, so a retry re-reads.
await withTransaction(pool, "SERIALIZABLE", async (db) => {
const { rows } = await db.query("SELECT count(*)::int AS n FROM shifts WHERE on_duty");
if (rows[0].n < 2) throw new Error("last cashier must stay on duty");
await db.query("UPDATE shifts SET on_duty = false WHERE cashier = $1", [cashier]);
});Three details matter. COMMIT is inside the try block because a serializable failure can surface on commit, as it did above. Every attempt takes a fresh connection from the pool and releases it in finally. And the attempts are capped, since the manual states that a retry is not guaranteed to succeed under heavy contention.
Keep side effects out of the callback. Sending an email, charging a card or publishing a message inside a transaction that may run three times will do it three times. Do those after the wrapper returns, or use an outbox table written in the same transaction.
Start from what your transaction needs to be true, not from the level name. The table summarises the three PostgreSQL levels by the cost they put on your code.
| Level | What you must do | Failure mode | Use it for |
|---|---|---|---|
| READ COMMITTED | Lock rows or write atomic UPDATE statements yourself | Lost update and write skew if you forget | Default for most CRUD, single-statement writes, reports that tolerate drift |
| REPEATABLE READ | Retry on 40001 | Write skew still possible | Multi-query reads needing a consistent view, read-modify-write on single rows |
| SERIALIZABLE | Retry on 40001, keep transactions short | More cancelled transactions under contention | Invariants spanning rows that no constraint can express |
My default for a single-VPS Postgres behind a NestJS API is READ COMMITTED with atomic UPDATE statements, moving a specific code path up only when it needs it. Go through this checklist for each transaction that writes.
The rule I carry away: an isolation level is a contract about what a transaction may observe, not a lock. Pick the weakest level that keeps your invariant true, express the invariant as a constraint or one atomic statement where you can, and treat 40001 as a normal, retryable answer rather than a bug. Lost updates and write skew are silent, which is exactly why they are worth designing out on purpose.