# 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

```sql
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

```sql
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](https://mermaid.live):

```mermaid
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

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/94cfaeed-2ccf-4362-840f-a774eb293554.png align="center")

---

## 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

```sql
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.

```sql
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** |

```sql
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):

```sql
-- 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.

```sql
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:**

```sql
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):

```sql
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 `SELECT`s. Always:

```sql
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:**

```sql
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.**

```sql
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.

```sql
-- 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):

```sql
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) |

```sql
-- 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.

```sql
-- 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:

```sql
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):

```sql
-- 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.
