Lesson 12 intermediate

Transactions and Concurrency

Group changes with BEGIN, COMMIT, ROLLBACK and savepoints; understand ACID and how MVCC lets readers and writers work without blocking each other; choose an isolation level; prevent lost updates with row locks; build a job queue with SKIP LOCKED; and deal with deadlocks, lock timeouts and idle transactions.

Many clients use Orbit at the same moment. Two of them may edit the same note, a background worker may process jobs while users create new ones, and a migration may alter a table under all of them. This lesson explains how Postgres keeps all of that correct, and the few tools you need to use it well.

ACID in one table

LetterPromiseHow Postgres keeps it
AtomicityAll changes of a transaction happen, or noneCommit is one record in the write-ahead log
ConsistencyConstraints hold after every transactionConstraints are checked on every write
IsolationConcurrent transactions do not see each other’s half-done workMVCC snapshots
DurabilityOnce committed, it survives a crashThe WAL is flushed to disk at commit

Transactions

Every statement runs in a transaction. Without BEGIN, each statement is its own transaction and commits immediately (autocommit). To group statements:

BEGIN;
INSERT INTO tags (name) VALUES ('ideas');
INSERT INTO note_tags (note_id, tag_id)
SELECT 3, id FROM tags WHERE name = 'ideas';
COMMIT;
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT

If anything fails in between, use ROLLBACK, and neither insert happened. After an error, Postgres refuses further statements in that transaction (current transaction is aborted, commands ignored until end of transaction block) until you roll back. In psql the prompt shows ! in that state.

Savepoints

A savepoint marks a point you can roll back to without losing the whole transaction:

BEGIN;
INSERT INTO tags (name) VALUES ('travel');
SAVEPOINT before_risky;
INSERT INTO tags (name) VALUES ('work');          -- fails: duplicate name
ROLLBACK TO SAVEPOINT before_risky;
COMMIT;                                           -- 'travel' is kept

This is exactly what psql’s ON_ERROR_ROLLBACK setting from lesson 3 does for you automatically.

MVCC: why readers never wait for writers

Postgres uses multi-version concurrency control. An UPDATE does not overwrite a row; it writes a new version of it and marks the old version as expired. Each transaction reads from a snapshot: the set of versions that were committed when its snapshot was taken.

The consequences are worth memorising:

  • Readers never block writers, and writers never block readers. A long report keeps reading the old versions while users update the table.
  • Only two transactions trying to change the same row wait for each other.
  • Old row versions pile up until VACUUM removes them once no snapshot can see them anymore (lesson 17). A transaction left open for hours prevents that cleanup.

Isolation levels

Isolation decides when your transaction takes its snapshot:

LevelSnapshotCan see
Read Committed (default)New snapshot for each statementChanges committed by others between your statements
Repeatable ReadOne snapshot for the whole transactionOnly what was committed when it started
SerializableLike Repeatable Read, plus detection of dangerous patternsResults as if transactions ran one after another

Watch the default level with two psql sessions side by side:

-- Session A                                   -- Session B
BEGIN;
SELECT plan FROM users WHERE id = 1;  -- free
                                               UPDATE users SET plan = 'pro' WHERE id = 1;
SELECT plan FROM users WHERE id = 1;  -- pro
COMMIT;

In Read Committed, the second SELECT sees Session B’s committed change. Start Session A with BEGIN ISOLATION LEVEL REPEATABLE READ; and both SELECTs return free: the transaction keeps its first snapshot. (Set Ana back to free afterwards with UPDATE users SET plan = 'free' WHERE id = 1;.)

Under Serializable, Postgres aborts one of two transactions whose combined effect could not have happened in any serial order:

ERROR:  could not serialize access due to read/write dependencies among transactions

Repeatable Read can fail with a similar serialization error when two transactions update the same row. Applications using these levels must retry the whole transaction when they get SQLSTATE 40001.

Lost updates and row locks

Two users open note 4 at the same time, both read the body, both save their edited version. With plain SELECT then UPDATE, the second save silently overwrites the first: a lost update.

The simplest fix is to make the update itself do the change, so it uses the current value:

UPDATE notes SET body = body || E'\nalso: use CONCURRENTLY for indexes' WHERE id = 4;

When you must read, decide in the application and then write, lock the row while you read it:

BEGIN;
SELECT body FROM notes WHERE id = 4 FOR UPDATE;
-- the application edits the text, then sends it back as a parameter
UPDATE notes SET body = $1 WHERE id = 4;
COMMIT;

Another transaction that runs SELECT ... FOR UPDATE on the same row now waits until the first commits, then sees the new version. Ordinary SELECTs are not blocked. For web apps where a user may keep a form open for minutes, prefer optimistic locking instead: add a version column and update with WHERE id = 4 AND version = 7. If the update reports UPDATE 0, someone else saved first.

A job queue with SKIP LOCKED

Background workers often take jobs from a table. With FOR UPDATE SKIP LOCKED, each worker grabs a job that no other worker has locked, without waiting:

CREATE TABLE jobs (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind       text NOT NULL,
  payload    jsonb NOT NULL DEFAULT '{}',
  status     text NOT NULL DEFAULT 'queued',
  created_at timestamptz NOT NULL DEFAULT now()
);

-- each worker, in a loop:
BEGIN;
SELECT id, kind, payload FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- do the work, then:
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

If a worker crashes, its transaction rolls back, the lock disappears and another worker picks the job up. Add a partial index ON jobs (id) WHERE status = 'queued' so finding the next job stays fast.

Table locks and migrations

Most statements take light table-level locks that do not conflict with each other. ALTER TABLE, DROP, TRUNCATE and plain CREATE INDEX take strong ones. The dangerous part is the queue: if a long-running query holds a light lock, your ALTER TABLE waits for it, and every new query on the table waits behind your ALTER. A two-second migration can take the site down for minutes.

Always set a lock timeout in migrations, and retry if it fires:

SET lock_timeout = '3s';
ALTER TABLE notes ADD COLUMN archived boolean NOT NULL DEFAULT false;
ERROR:  canceling statement due to lock timeout

That error is good news: the migration gave up instead of blocking everyone. Try again a moment later.

Deadlocks

Transaction 1 locks row A and wants row B; transaction 2 locks row B and wants row A. Neither can continue. Postgres detects this after deadlock_timeout (one second by default) and cancels one of them:

ERROR:  deadlock detected

Prevent deadlocks by touching rows in a consistent order (for example, always by ascending id) and keeping transactions short. Treat the error like a serialization failure: retry.

Finding who is blocking whom

SELECT pid, usename, state, wait_event_type, now() - xact_start AS xact_age,
       pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_age DESC;

pg_blocking_pids lists the sessions holding the locks a session is waiting for. If one of them is idle in transaction, an application opened a transaction and forgot about it. End it with SELECT pg_terminate_backend(pid);, and protect the server with:

ALTER DATABASE orbit SET idle_in_transaction_session_timeout = '5min';

Quick reference

TaskSQL
Group statementsBEGIN; ... COMMIT;
UndoROLLBACK;
Partial undoSAVEPOINT s; ... ROLLBACK TO SAVEPOINT s;
Stricter isolationBEGIN ISOLATION LEVEL REPEATABLE READ;
Lock rows you will changeSELECT ... FOR UPDATE;
Queue consumer... FOR UPDATE SKIP LOCKED
Fail fast on locksSET lock_timeout = '3s';
Who blocks whompg_blocking_pids(pid)
Kill forgotten transactionsidle_in_transaction_session_timeout

Exercise

A nightly report runs a 20-minute SELECT on notes. What happens to users who update notes during that time?

  1. Their updates wait until the report finishes
  2. The report fails with a serialization error
  3. Their updates proceed normally; the report keeps reading the row versions from its snapshot
  4. Postgres pauses the report while updates run
Show answer

Answer: C. With MVCC, readers and writers do not block each other. The long transaction does delay VACUUM cleanup of old row versions, so keep reports on a replica if they run for hours.

Exercise

Several workers take jobs from the same table. Which clause lets each one grab a different job without waiting?

  1. FOR UPDATE SKIP LOCKED
  2. FOR SHARE
  3. LOCK TABLE jobs IN EXCLUSIVE MODE
  4. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Show answer

Answer: A. SKIP LOCKED ignores rows locked by other transactions, so each worker locks the first job nobody else is holding. A crashed worker’s lock is released on rollback.

Exercise

A migration’s ALTER TABLE hangs and the whole application stops responding. What is the most likely cause?

  1. ALTER TABLE always locks the entire database
  2. The table is too large to alter
  3. Autovacuum is running on the table
  4. ALTER TABLE is waiting behind a long transaction, and every new query on the table queues behind it
Show answer

Answer: D. The strong lock request waits for the current holder and blocks everything after it. Set lock_timeout so the migration gives up quickly, then retry.

Key takeaways

  • Transactions make groups of changes atomic; savepoints allow partial rollback.
  • MVCC keeps row versions so readers and writers never block each other; VACUUM cleans up old versions.
  • Read Committed takes a new snapshot per statement; Repeatable Read and Serializable keep one per transaction and may require retries.
  • Prevent lost updates with in-place updates, SELECT ... FOR UPDATE or a version column; build queues with SKIP LOCKED.
  • Set lock_timeout for migrations and idle_in_transaction_session_timeout for forgotten transactions.

FAQ

Which isolation level should my application use?

Read Committed, the default, for most work. Use Repeatable Read for reports that must see one consistent snapshot, and Serializable when correctness depends on complex invariants and you can retry transactions.

Does BEGIN slow things down?

No. Every statement is already a transaction. Grouping many inserts into one transaction is actually faster, because the commit, the expensive part, happens once.

Are DDL statements transactional?

Yes, in Postgres almost all of them are: CREATE TABLE, ALTER TABLE and DROP can be rolled back. A few commands, such as CREATE INDEX CONCURRENTLY, VACUUM and CREATE DATABASE, cannot run inside a transaction block.

What is an advisory lock?

A lock on an application-defined number, not on a row or table. SELECT pg_try_advisory_lock(42); is a simple way to make sure only one instance of a cron job runs at a time.

Why does my connection pooler complain about SET and transactions?

In transaction pooling mode (PgBouncer), consecutive transactions may run on different server connections, so session state such as SET, prepared statements or advisory locks can get lost. Use SET LOCAL inside the transaction, or session pooling for clients that need session state.

↑ ↓ navigate Enter open Esc close