Transactions, MVCC, and concurrency

A minimal transaction

BEGIN;

SELECT balance_cents
FROM accounts
WHERE id = $1
FOR UPDATE;

UPDATE accounts
SET balance_cents = balance_cents - $2
WHERE id = $1
  AND balance_cents >= $2;

COMMIT;

A transaction makes several statements one atomic unit. FOR UPDATE locks selected rows until commit or rollback; the application must still inspect the update count.

MVCC intuition

Multi-version concurrency control means readers normally do not block writers and writers normally do not block ordinary readers. Updates create new row versions. Autovacuum can reclaim old versions after no active snapshot can see them.

Long transactions delay that cleanup and increase bloat, WAL retention, and replication-lag risk. Never hold a transaction open across user think time, network retries, or unrelated external API calls.

Isolation levels

PostgreSQL treats READ UNCOMMITTED as READ COMMITTED.

Beyond the SQL standard's minimum, PostgreSQL REPEATABLE READ also prevents phantom reads, but serialization failures can still require a whole-transaction retry. Sequence changes such as nextval() are not rolled back, so gaps are normal.

Correct retry boundary

After serialization failure or deadlock, the current transaction cannot continue. Roll it back and replay the whole transaction, not just the final SQL statement.

begin
  run all reads and writes
  commit
on SQLSTATE 40001 or 40P01
  rollback
  retry whole unit with bounded exponential backoff

External effects inside the retry boundary must be idempotent. A transactional outbox is often safer for post-commit work.

Deadlocks and waits

Reduce deadlocks by locking resources in a consistent order, keeping transactions short, indexing lookup predicates, and setting appropriate lock_timeout and statement_timeout values.

SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';

SET LOCAL applies only to the current transaction.

Use the PostgreSQL 18 transaction-isolation documentation as the authoritative behavior reference.

Continue with MVCC and snapshot visibility for old row versions and long transactions, then lock waits and deadlocks for blocking diagnostics and safe intervention.