Transactions and MVCC basics

Isolation levels in practice, why readers never block writers, and what vacuum is really cleaning up.

Transactions and isolation levels

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  SAVEPOINT after_debit;
  UPDATE ledger SET note = 'transfer' WHERE ref = 'T-9';
  ROLLBACK TO SAVEPOINT after_debit;   -- undo only the last change
COMMIT;

-- tighten isolation for one transaction only
BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT sum(balance) FROM accounts;
COMMIT;

What MVCC actually does

Every row version carries the transaction that created it and the transaction that deleted it. A statement reads the newest version that is visible to its snapshot, so a SELECT never waits for a UPDATE and an UPDATE never waits for a SELECT. Readers and writers stop blocking each other; writers still block writers on the same row.

• An update writes a new row version and marks the old one dead. The old version stays on disk until no snapshot can see it.
• VACUUM reclaims those dead versions for reuse. autovacuum does this in the background; if it falls behind, tables and indexes bloat.
• Transaction IDs are 32-bit and wrap around. VACUUM FREEZE marks old rows so the cycle stays safe; a server that has never vacuumed will eventually refuse writes.
• A single long-running transaction — including an idle one holding a snapshot — prevents cleanup of every dead version created since it began. That is how one forgotten session grows the disk.
• Deadlocks are detected in about a second and one transaction is aborted; the survivor continues normally.

Locks and queues

-- hand-off patterns that avoid lost updates
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;          -- block others
SELECT balance FROM accounts WHERE id = 1 FOR NO KEY UPDATE;   -- weaker, keeps FK reads open

-- pull work without two workers taking the same row
WITH next AS (
  SELECT id FROM jobs WHERE status = 'queued'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs j SET status = 'running', started_at = now()
FROM next WHERE j.id = next.id
RETURNING j.id;

-- application-level lock that is not tied to a table row
SELECT pg_try_advisory_lock(42);
SELECT pg_advisory_unlock(42);

• Take locks in a consistent order across your code; most deadlocks come from two paths acquiring the same two rows in opposite order.
• SKIP LOCKED turns a table into a work queue without a separate broker — it is the standard idiom for job workers.
• Set statement_timeout and idle_in_transaction_session_timeout so a stuck client cannot pin a snapshot forever.
• DDL is transactional here: BEGIN; ALTER TABLE ...; ROLLBACK; really undoes the change. The exception is CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block.

FAQ

Related

psql and the basics of SQL JSONB and arrays

Last refreshed 2026-09-18.