Transactions, isolation and locking cheat sheet

Write skew under REPEATABLE READ

Both transactions commit and nobody is on call. PostgreSQL's REPEATABLE READ is snapshot isolation: each transaction reads its own snapshot, sees two doctors on call, and updates a different row, so there is no write-write conflict to detect. This anomaly is write skew. SERIALIZABLE catches it and aborts one transaction with SQLSTATE 40001; the app must retry it.

What ACID really promises

Atomicity: a transaction's writes all happen or none do. Consistency: the transaction moves the data from one valid state to another, but 'valid' is defined by your constraints and code, so it is partly the application's job. Isolation: concurrent transactions don't see each other's half-done work, to the degree the isolation level promises. Durability: once COMMIT returns, the change survives a crash, because it is in the write-ahead log on disk.

Isolation levels in PostgreSQL

The standard defines READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, so dirty reads never happen. READ COMMITTED, the default, takes a new snapshot per statement. REPEATABLE READ keeps one snapshot for the whole transaction, which also blocks phantoms but still allows write skew. SERIALIZABLE adds conflict tracking and aborts transactions with 40001 when the result couldn't come from some serial order.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SHOW transaction_isolation;
COMMIT;

The lost update problem

Both requests read the same old value, say 10, and both write 11, so one increment disappears. Under PostgreSQL's default READ COMMITTED nothing stops it, because each write looks valid on its own. Fix it by letting the database do the arithmetic in one statement (SET n = n + 1), by locking the row with SELECT ... FOR UPDATE, or with optimistic concurrency: a version column checked in the UPDATE's WHERE clause.

CREATE TABLE docs (
  id int PRIMARY KEY,
  body text,
  version int NOT NULL DEFAULT 1
);
INSERT INTO docs VALUES (1, 'v1 text', 1);
-- Writer A read version 1 and saves first: 1 row updated
UPDATE docs SET body = 'A edit', version = version + 1
WHERE id = 1 AND version = 1;
-- B also read version 1: 0 rows updated, so B reloads and retries
UPDATE docs SET body = 'B edit', version = version + 1
WHERE id = 1 AND version = 1;
SELECT body, version FROM docs;

SELECT FOR UPDATE and SKIP LOCKED

FOR UPDATE takes a row lock on every row the query returns; other transactions that try to update or lock those rows wait until you commit or roll back. Plain readers are not blocked. Adding SKIP LOCKED makes a second worker skip rows another worker has locked instead of waiting, so many workers can each claim a different job with ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED.

Optimistic vs pessimistic locking

Pessimistic locking takes the lock first (SELECT ... FOR UPDATE) and makes others wait, which is good when conflicts are frequent and work is short. Optimistic concurrency doesn't lock; it checks at write time that nobody changed the row (a version or timestamp in the WHERE clause) and retries on conflict, which is good when conflicts are rare or the edit spans user think time, like a form open for minutes.

Retrying serialization failures

Roll back and run the whole transaction again from the start, including its reads, because the decision it made was based on data that's now invalid. Wrap the transaction in a function and retry it a few times with a short randomised backoff. Never retry just the failed statement, and make sure nothing outside the database, like an email or HTTP call, happened inside the attempt.

MVCC, dead tuples and VACUUM

PostgreSQL never updates a row in place. An UPDATE writes a new row version and marks the old one with the transaction id that replaced it; a DELETE just marks it. Each transaction sees the versions its snapshot allows, so readers never block writers. Old versions nobody can see any more are dead tuples; VACUUM, usually autovacuum, makes their space reusable and freezes old transaction ids before the 32-bit counter wraps around.

Deadlocks and lock ordering

A deadlock is a cycle: transaction A holds a lock B needs while B holds one A needs. PostgreSQL checks for cycles after waiting deadlock_timeout (1 second by default) and aborts one transaction with SQLSTATE 40P01; the other proceeds. Prevent most deadlocks by always locking rows in the same order, for example by sorting ids before updating, keeping transactions short, and retrying the victim.

Why long transactions hurt

It holds its locks for the whole time, so writers to those rows queue behind it, and DDL queues behind those writers. It also pins its snapshot: VACUUM can't remove row versions newer than the oldest open snapshot, so tables bloat across the whole database. The usual culprit is a session left 'idle in transaction' by code that began a transaction and then waited on something else.

SET idle_in_transaction_session_timeout = '30s';
SET lock_timeout = '3s';
SET statement_timeout = '15s';
SHOW lock_timeout;

Errors inside a transaction

After any error the whole transaction is marked aborted: every later statement fails with SQLSTATE 25P02 until you ROLL BACK, and COMMIT turns into a rollback. To recover from an expected error without losing earlier work, set a SAVEPOINT before the risky statement and ROLLBACK TO SAVEPOINT if it fails. Drivers use this for nested transaction blocks.

import psycopg

with psycopg.connect(autocommit=True) as conn:
    conn.execute("CREATE TEMP TABLE users (email text PRIMARY KEY)")
    with conn.transaction():
        conn.execute("INSERT INTO users VALUES ('a@x.io')")
        try:
            with conn.transaction():  # SAVEPOINT
                conn.execute("INSERT INTO users VALUES ('a@x.io')")
        except psycopg.errors.UniqueViolation:
            pass  # ROLLBACK TO SAVEPOINT; the outer work survives
        conn.execute("INSERT INTO users VALUES ('b@x.io')")
    row = conn.execute("SELECT count(*) FROM users").fetchone()
    print(row)

Advisory locks

Advisory locks are locks on an application-chosen number rather than a row. The database enforces them but gives them no meaning, so you use them to serialise work that isn't one row: run a migration once, make sure only one instance runs a cron job, or rebuild one customer's cache at a time. pg_advisory_xact_lock(key) releases at transaction end; pg_try_advisory_lock returns false instead of waiting.

Where transactions begin and end in code

One transaction per business operation: everything that must succeed or fail together, and nothing else. Open it as late as possible, close it as early as possible, and keep it in one place, usually the service layer, not scattered across repositories. Never do slow or irreversible things inside it: HTTP calls, sending email, publishing to a broker, or waiting for user input.

Two-phase commit and why services avoid it

A coordinator asks every participant to prepare: each writes everything durably and promises it can commit. If all say yes, the coordinator tells them to commit; if any says no, all roll back. It gives atomicity across databases, but a participant that has prepared must hold its locks until it hears the decision, so a coordinator crash can block them indefinitely. Services usually prefer sagas with compensation, or an outbox plus idempotent consumers.