Transactions & isolation: concurrent writers without lost updates

Moving 10 from account 1 to account 2 is two statements. If the server dies between them, 10 has vanished. A transaction bundles statements so that either all of them take effect or none do:

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;      -- both changes become visible to everyone, together

ROLLBACK instead of COMMIT throws both away. Without BEGIN, PostgreSQL wraps every single statement in its own transaction (an implicit BEGIN and, if it succeeds, COMMIT).

The guarantees go by ACID:

• Atomic — all of it or none of it.
• Consistent — constraints (NOT NULL, foreign keys, CHECK, UNIQUE) hold when it commits.
• Isolated — other sessions don't see your changes until you commit, and you see theirs only in the ways your isolation level allows (the rest of this unit).
• Durable — once COMMIT returns, the change survives a crash (with the default synchronous_commit = on).

An error poisons the whole transaction

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
INSERT INTO accounts VALUES (1, 0);
-- ERROR 23505: duplicate key value violates unique constraint "accounts_pkey"
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- ERROR 25P02: current transaction is aborted, commands ignored until end of transaction block
COMMIT;
-- the server answers ROLLBACK

After any error the transaction is aborted: every following statement is refused, and COMMIT rolls back — run from Node with pg, the COMMIT doesn't even throw; its result's command is ROLLBACK. So if your code catches an error inside a transaction, logs it and carries on, the "successful" commit at the end silently discarded everything. On error: roll back and retry or report — never continue.

When one step is genuinely allowed to fail, mark a savepoint and roll back to just that:

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SAVEPOINT before_insert;
INSERT INTO accounts VALUES (1, 0);              -- fails
ROLLBACK TO SAVEPOINT before_insert;             -- the transaction is usable again
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;                                          -- both updates commit

READ COMMITTED is PostgreSQL's default isolation level, and most application code runs under it without knowing. Two rules describe almost everything it does.

Rule 1: every statement takes a fresh snapshot

A statement sees the data as committed at the instant that statement starts (plus your own uncommitted changes). The next statement in the same transaction takes a new snapshot:

A: BEGIN;
A: SELECT balance FROM accounts WHERE id = 1;     -- 100
B: UPDATE accounts SET balance = 50 WHERE id = 1; -- B autocommits
A: SELECT balance FROM accounts WHERE id = 1;     -- 50  (same transaction, new answer)
A: COMMIT;

That's a non-repeatable read, and READ COMMITTED allows it. Never assume a value you SELECTed earlier in the transaction is still true when you write.

You never see another transaction's uncommitted work, though — no dirty reads at any level in PostgreSQL (asking for READ UNCOMMITTED gets you READ COMMITTED).

Rule 2: a write to a row someone else is changing waits, then re-checks

When an UPDATE, DELETE or SELECT … FOR UPDATE reaches a row that another open transaction has already changed, it waits for that transaction to finish. If the other one commits, the waiting statement takes the new version of the row, re-evaluates its WHERE clause against it, and applies its change to that version if it still matches (or skips the row if it doesn't).

That rule is why the one-statement fix from the simple tier works:

A: BEGIN;
A: UPDATE posts SET comment_count = comment_count + 1 WHERE id = 1;  -- 5 → 6, row locked
B: UPDATE posts SET comment_count = comment_count + 1 WHERE id = 1;  -- waits…
A: COMMIT;
B:                                     -- …re-reads the committed row (6), writes 7

And it's why a guarded update is safe — the guard is re-checked against the row as it is now:

-- stock is 3; both want 2
A: BEGIN;
A: UPDATE products SET stock = stock - 2 WHERE id = 1 AND stock >= 2 RETURNING stock;  -- 1
B: UPDATE products SET stock = stock - 2 WHERE id = 1 AND stock >= 2 RETURNING stock;  -- waits
A: COMMIT;
B:                                     -- re-checks: stock is 1, 1 >= 2 is false → UPDATE 0

UPDATE 0 is the answer "not enough stock" — check the row count in your code. A CHECK (stock >= 0) constraint is the backstop for any writer that forgets the guard.

What READ COMMITTED does not protect

It re-checks the row being written, not the other rows you read to decide what to write. The PostgreSQL manual says it plainly: an updating command can see the effects of concurrent updates on the rows it's updating, but not their effects on other rows. So:

• read-modify-write in the application (read 5, compute 6, write 6) still loses updates — the UPDATE re-checks its WHERE, but the value it writes was computed from the old read;
• a decision based on other rows ("is anyone else on call?", "does this email exist yet?") can be invalidated by a concurrent transaction and nothing notices.

The next tier is the toolkit for both.

Row locks and version checks protect the row you write. Some rules are about other rows.

Write skew: both checks pass, the rule breaks

Rule: at least one doctor stays on call. Alice and Bob are both on call; each asks to go off duty at the same moment, through the same code:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM on_call WHERE on_duty;                      -- 2, so it's safe to leave
UPDATE on_call SET on_duty = false WHERE doctor = 'alice';       -- (Bob's session: 'bob')
COMMIT;

Run two sessions side by side — both checks, then both updates, then both commits — and both commit: zero doctors on call. Each transaction's check was true in its own snapshot, and they wrote different rows, so no row lock or version check ever collided. READ COMMITTED and REPEATABLE READ both allow it (tested). Under SERIALIZABLE the same interleaving ends with:

B: COMMIT;
ERROR 40001: could not serialize access due to read/write dependencies among transactions

SERIALIZABLE guarantees the result matches some one-at-a-time order of the committed transactions. It tracks what each transaction read, notices that each one's read was invalidated by the other's write, and fails one so the invariant survives (one doctor stayed on call). Note the error can arrive at COMMIT, not only at a statement — and where it arrives depends on the timing: had A committed before B's UPDATE, that UPDATE would have been the statement that failed.

Check-then-insert: the same bug with INSERT

A: SELECT count(*) FROM signups WHERE email = 'ada@x.io';   -- 0
B: SELECT count(*) FROM signups WHERE email = 'ada@x.io';   -- 0
A: INSERT …;  B: INSERT …;  A: COMMIT;  B: COMMIT;          -- two Adas

Every "does it exist? no → create it" in application code has this race. Here the right fix isn't an isolation level — it's a constraint. With UNIQUE (email), B's insert waits for A, then fails with 23505 (under SERIALIZABLE, after a check that read the key, it fails with 40001 instead). Either way the duplicate never commits — at every isolation level and for every writer, including ones that skip the check. Reach for SERIALIZABLE when the invariant can't be written as a constraint (the on-call count can't be a UNIQUE or CHECK).

Retrying correctly

The manual's guidance, in practice:

• Always retry 40001 (serialization_failure); consider retrying 40P01 (deadlock_detected) too. Treat 23505 as retryable only when your logic chose the conflicting value from a read (it may also be a permanent error).
• Retry the complete transaction, including the reads and the application logic that decided what to write. Replaying just the failed statement replays the stale decision. That's why PostgreSQL offers no automatic retry.
• Bound it. A few attempts with a short random back-off; then surface the error.
• Keep side effects out. An email sent or an HTTP call made inside the transaction will happen once per attempt. Do them after COMMIT.