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 aborteduntilROLLBACK. - Use
SAVEPOINTfor 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
VACUUMreclaims them, so UPDATE-heavy tables bloat. - HOT updates (no indexed column changed, free space on the same page) skip index writes. Tune
fillfactoron hot tables (e.g.accountsat 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.
Comments
Join the discussionNo comments yet. Be the first to comment.



