Skip to main content

PostgreSQL Transactions & Concurrency: A Practitioner's Cheat Sheet

Updated 12 min readMarkdown
PostgreSQL Transactions & Concurrency: A Practitioner's Cheat Sheet

For people who already write SQL daily. No hand-holding, just the mechanics, the traps, and the queries I actually use.

One tiny schema throughout: customers own accounts, accounts move money via transfers. Small enough to hold in your head, rich enough to trigger every concurrency problem below.


0. Schema, Data, ER Diagram

DDL

CREATE SCHEMA IF NOT EXISTS bank;
SET search_path = bank;

CREATE TABLE customers (
    id          serial PRIMARY KEY,
    name        text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE accounts (
    id           serial PRIMARY KEY,
    customer_id  int NOT NULL REFERENCES customers(id),
    balance      numeric(12,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),  -- the "C" in ACID
    updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE transfers (
    id               bigserial PRIMARY KEY,
    from_account_id  int NOT NULL REFERENCES accounts(id),
    to_account_id    int NOT NULL REFERENCES accounts(id),
    amount           numeric(12,2) NOT NULL CHECK (amount > 0),
    status           text NOT NULL DEFAULT 'pending'
                     CHECK (status IN ('pending','done','failed')),
    created_at       timestamptz NOT NULL DEFAULT now(),
    CHECK (from_account_id <> to_account_id)
);

CREATE INDEX ON accounts (customer_id);
CREATE INDEX ON transfers (status) WHERE status = 'pending';  -- partial index for the queue pattern

Dummy data

INSERT INTO customers (name) VALUES ('Alice'), ('Bob'), ('Carol');

INSERT INTO accounts (customer_id, balance) VALUES
    (1, 1000.00),   -- id 1: Alice checking
    (1, 5000.00),   -- id 2: Alice savings
    (2,  750.00),   -- id 3: Bob
    (3, 2500.00),   -- id 4: Carol checking
    (3,  300.00);   -- id 5: Carol spare

INSERT INTO transfers (from_account_id, to_account_id, amount, status) VALUES
    (1, 3, 100.00, 'done'),
    (4, 1, 250.00, 'done'),
    (2, 4, 500.00, 'done'),
    (3, 5,  50.00, 'pending'),
    (4, 3, 120.00, 'pending'),
    (5, 1,  30.00, 'pending');

ER diagram

Paste into mermaid.live:

erDiagram
    CUSTOMERS ||--o{ ACCOUNTS : owns
    ACCOUNTS  ||--o{ TRANSFERS : "sends (from_account_id)"
    ACCOUNTS  ||--o{ TRANSFERS : "receives (to_account_id)"

    CUSTOMERS {
        int id PK
        text name
        timestamptz created_at
    }
    ACCOUNTS {
        int id PK
        int customer_id FK
        numeric balance "CHECK >= 0"
        timestamptz updated_at
    }
    TRANSFERS {
        bigint id PK
        int from_account_id FK
        int to_account_id FK
        numeric amount "CHECK > 0"
        text status "pending | done | failed"
        timestamptz created_at
    }

the diagram is


1. ACID, mapped to Postgres

Property What actually enforces it
Atomicity Transaction commits or aborts as a unit; aborted rows are just invisible tuples (no undo log, MVCC handles it)
Consistency Constraints (CHECK, FK, UNIQUE) validated at statement or commit time (DEFERRABLE). Your invariants are your job
Isolation MVCC snapshots + locks, governed by isolation level
Durability WAL flushed to disk before COMMIT returns (synchronous_commit = on, fsync = on)

Durability trade-off worth knowing: synchronous_commit = off risks losing the last few transactions on crash, but never corrupts data. Fine for logs and metrics, not for money.


2. BEGIN / COMMIT / ROLLBACK

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 3;
  INSERT INTO transfers (from_account_id, to_account_id, amount, status)
       VALUES (1, 3, 100, 'done');
COMMIT;

Things people forget:

  • Any error aborts the whole transaction. Further statements fail with current transaction is aborted until ROLLBACK.
  • Use SAVEPOINT for partial recovery. It's what ORMs do under the hood for nested transactions, and each subtransaction burns an XID, so don't loop thousands of them.
  • DDL is transactional in Postgres. BEGIN; ALTER TABLE ...; ROLLBACK; works, which makes safe migrations possible.
  • Autocommit is the default: every standalone statement is its own transaction.
BEGIN;
  UPDATE accounts SET balance = balance - 5000 WHERE id = 1;  -- violates CHECK
  -- ERROR: new row violates check constraint
ROLLBACK;

3. Isolation Levels

Postgres has three real levels (READ UNCOMMITTED behaves as READ COMMITTED).

Level Snapshot taken Prevents Failure mode
Read Committed (default) Per statement Dirty reads Non-repeatable reads, phantoms, lost updates (read-modify-write)
Repeatable Read Per transaction (first query) + non-repeatable reads, phantoms 40001 on concurrent update of same row; write skew possible
Serializable Per transaction + SSI Everything, including write skew 40001 serialization failures, you must retry
BEGIN ISOLATION LEVEL REPEATABLE READ;
  SELECT sum(balance) FROM accounts;   -- snapshot frozen here
  -- another session commits a transfer + new account
  SELECT sum(balance) FROM accounts;   -- same answer
COMMIT;

Lost update under Read Committed (app-side read-modify-write):

-- BAD: both sessions read 1000, both write 900
SELECT balance FROM accounts WHERE id = 1;          -- app computes balance - 100
UPDATE accounts SET balance = 900 WHERE id = 1;

-- GOOD: atomic in-place update, the row lock serializes writers
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

Write skew (only Serializable catches it): invariant "Alice's two accounts must total ≥ 1000". Two sessions each withdraw from a different account after checking the sum. Each passes under Repeatable Read. Under Serializable, SSI detects the rw-dependency cycle and aborts one with 40001.

Rule: Serializable is not slower magic. It's "correct unless you forget the retry loop."


4. MVCC: Why Readers Don't Block Writers

Every row version (tuple) carries xmin (creating XID) and xmax (deleting/locking XID). An UPDATE is insert-new-version + mark-old-version-dead.

SELECT ctid, xmin, xmax, id, balance FROM accounts WHERE id = 1;

BEGIN;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
SELECT ctid, xmin, xmax, id, balance FROM accounts WHERE id = 1;  -- new ctid, new xmin
ROLLBACK;

Consequences you must internalize:

  • Readers never block writers, writers never block readers. Writers still block writers on the same row.
  • Dead tuples accumulate until VACUUM reclaims them, so UPDATE-heavy tables bloat.
  • HOT updates (no indexed column changed, free space on the same page) skip index writes. Tune fillfactor on hot tables (e.g. accounts at 80-90).
  • XID wraparound is real: autovacuum freezing is not optional.

5. Row-Level Locking

Clause Blocks Use case
FOR UPDATE All other row locks You'll update/delete the row
FOR NO KEY UPDATE Most; allows FOR KEY SHARE Update non-key columns (what plain UPDATE takes)
FOR SHARE Updates/deletes Stable read, e.g. parent row while inserting children
FOR KEY SHARE Key changes/deletes Taken by FK checks

Modifiers: NOWAIT (fail immediately), SKIP LOCKED (skip locked rows), OF table (limit locking in joins).

Pessimistic transfer:

BEGIN;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- lock it
  -- app-side validation here
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 3;
COMMIT;

Job queue with SKIP LOCKED (workers never collide, no external broker needed):

BEGIN;
  SELECT id, from_account_id, to_account_id, amount
    FROM transfers
   WHERE status = 'pending'
   ORDER BY id
   LIMIT 10
   FOR UPDATE SKIP LOCKED;
  -- process, then:
  UPDATE transfers SET status = 'done' WHERE id = ANY(:processed_ids);
COMMIT;

6. Table-Level Locks

Eight modes, from weakest to strongest:

ACCESS SHARE → ROW SHARE → ROW EXCLUSIVE → SHARE UPDATE EXCLUSIVE → SHARE → SHARE ROW EXCLUSIVE → EXCLUSIVE → ACCESS EXCLUSIVE

The ones that matter in practice:

Statement Lock Blocks
SELECT ACCESS SHARE Only ACCESS EXCLUSIVE
INSERT/UPDATE/DELETE ROW EXCLUSIVE SHARE and stronger
VACUUM, CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE Itself, DDL
CREATE INDEX (plain) SHARE All writes
ALTER TABLE, DROP, TRUNCATE, VACUUM FULL ACCESS EXCLUSIVE Everything, including SELECT

The lock queue trap: an ALTER TABLE waiting behind one long transaction blocks every query behind it, even plain SELECTs. Always:

SET lock_timeout = '3s';
ALTER TABLE accounts ADD COLUMN currency char(3) DEFAULT 'USD';  -- retry on timeout

Prefer CREATE INDEX CONCURRENTLY, and add constraints as NOT VALID then VALIDATE CONSTRAINT separately.

Who's blocking whom:

SELECT a.pid, pg_blocking_pids(a.pid) AS blocked_by, a.state, a.wait_event_type, a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

7. Deadlocks

Classic: two transfers in opposite directions, locking rows in opposite order.

Session A Session B
BEGIN; BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1; UPDATE accounts SET balance = balance - 10 WHERE id = 3;
UPDATE accounts SET balance = balance + 10 WHERE id = 3; ⏳ waits on B UPDATE accounts SET balance = balance + 10 WHERE id = 1; 💥

After deadlock_timeout (1s default), Postgres picks a victim: ERROR: deadlock detected (40P01).

Fix: consistent lock ordering.

BEGIN;
  SELECT id FROM accounts WHERE id IN (1, 3) ORDER BY id FOR UPDATE;  -- always ascending
  UPDATE accounts SET balance = balance - 10 WHERE id = 1;
  UPDATE accounts SET balance = balance + 10 WHERE id = 3;
COMMIT;

Other mitigations: keep transactions short, retry on 40P01, and set log_lock_waits = on to see the pain before it becomes deadlocks.


8. Long-Running Transactions: The Silent Killer

An open transaction (even idle in transaction) holds back the xmin horizon. VACUUM can't remove any dead tuple newer than the oldest active snapshot, on any table in the database.

Effects: table/index bloat, slow scans, runaway disk, and in the worst case XID wraparound pressure. Orphaned replication slots and prepared transactions do the same thing.

-- Oldest offenders
SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

-- Dead tuples piling up?
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 5;

-- Slots holding the horizon back
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;

Guardrails (set per role/database, not globally if you run batch jobs):

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_user SET statement_timeout = '60s';

9. Read Replicas: Offload Reporting

Streaming replicas let you run heavy analytics without touching the primary's lock and CPU budget.

The catch is replication conflicts. Vacuum cleanup on the primary can conflict with a long query on the replica, which then gets cancelled (canceling statement due to conflict with recovery). Two knobs:

Setting (on replica) Trade-off
max_standby_streaming_delay Let queries run longer, but replica lag grows
hot_standby_feedback = on Replica tells primary its xmin, so no cancellations, but primary bloats (see §8)
-- On primary: lag per replica
SELECT client_addr, state, replay_lag FROM pg_stat_replication;

-- On replica: how stale am I?
SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;

Design rules: route reports and BI to replicas, keep read-your-writes paths on the primary (async replicas lag), and give reporting a dedicated replica if queries run for minutes.

-- Typical reporting query, safe to run on a replica
SELECT c.name, count(*) AS transfers, sum(t.amount) AS volume
FROM transfers t
JOIN accounts a ON a.id = t.from_account_id
JOIN customers c ON c.id = a.customer_id
WHERE t.status = 'done'
GROUP BY c.name;

10. Transaction Snapshots: Consistency Across Queries

Under Read Committed each statement gets a fresh snapshot, so two queries in one transaction can disagree. For multi-query consistency (reports, exports), use Repeatable Read:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
  SELECT sum(balance) FROM accounts;
  SELECT status, count(*) FROM transfers GROUP BY status;
  -- both see the exact same point in time
COMMIT;

READ ONLY DEFERRABLE + SERIALIZABLE waits for a safe snapshot, then runs with zero SSI overhead or abort risk. Ideal for long reports.

Sharing one snapshot across connections (how pg_dump -j and parallel exports work):

-- Session 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();            -- e.g. '00000003-0000001B-1'

-- Session 2..N
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';
-- identical view of the data as Session 1

Keep session 1 open until the others have imported. And mind §8: the longer the snapshot lives, the more it holds back vacuum.


Quick Reference

Problem Reach for
Lost update Atomic UPDATE ... SET x = x + n, or FOR UPDATE
Write skew / complex invariants SERIALIZABLE + retry loop
Worker queue FOR UPDATE SKIP LOCKED
Deadlocks Consistent lock ordering + 40P01 retry
Safe DDL lock_timeout, CONCURRENTLY, NOT VALID
Bloat Kill long/idle-in-transaction sessions, check slots, tune autovacuum
Heavy reports Replica + REPEATABLE READ READ ONLY
Consistent multi-query read Repeatable Read snapshot / pg_export_snapshot

Error codes to handle in app code: 40001 (serialization failure) and 40P01 (deadlock): retry the whole transaction with backoff. 55P03 (lock not available, from NOWAIT/lock_timeout): retry or fail fast.

No comments yet. Be the first to comment.