Transactions and Locking

Blocks and savepoints

Every statement runs in a transaction. Without BEGIN it is its own transaction that commits on success, which is autocommit.

BEGIN;                       -- or START TRANSACTION
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                      -- or END

Once a statement inside a block errors, the whole transaction is aborted and every further statement returns 25P02 current transaction is aborted. Only ROLLBACK or a ROLLBACK TO SAVEPOINT gets you out.

BEGIN;
INSERT INTO t VALUES (1);
SAVEPOINT sp1;
INSERT INTO t VALUES (1);          -- fails on a unique violation
ROLLBACK TO SAVEPOINT sp1;         -- transaction is usable again
RELEASE SAVEPOINT sp1;             -- drop the marker, keep the work
COMMIT;                            -- the first insert survives

Savepoints are internally subtransactions, and each one burns an XID. Thousands per transaction, which is what a per-row try/except in an ORM produces, causes a measurable performance cliff. psql has \set ON_ERROR_ROLLBACK on to wrap each statement in an implicit savepoint interactively.

Isolation levels

Four levels in the standard, three distinct behaviors in Postgres. Everything is snapshot based, so a reader never blocks a writer and a writer never blocks a reader.

READ UNCOMMITTED is accepted for compatibility and behaves identically to READ COMMITTED. Dirty reads never happen in Postgres at any level.

- READ COMMITTED takes a fresh snapshot per statement, so two SELECTs in one transaction can disagree. - REPEATABLE READ takes one snapshot for the whole transaction. Postgres blocks phantom reads here too, which is stricter than the standard requires. A write that hits a row changed since the snapshot fails with 40001. - SERIALIZABLE adds predicate locking (SSI) so any set of concurrent transactions is equivalent to running them one at a time. It never blocks extra, it aborts with 40001 instead.

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- or, as the first statement in a block
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
-- session default
SET default_transaction_isolation = 'repeatable read';
SELECT current_setting('transaction_isolation');

READ ONLY DEFERRABLE on a serializable transaction makes a long reporting query wait for a safe snapshot and then never abort, which is the right setting for a nightly report.

Retrying 40001

Under REPEATABLE READ or SERIALIZABLE, serialization failure is a normal outcome, not a bug. Any application using those levels must retry, and there is no way to avoid it. The whole transaction must be replayed, not just the failed statement.

import time, random, psycopg

def run(conn, work, tries=5):
    for attempt in range(tries):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                return work(conn)
        except psycopg.errors.SerializationFailure:
            if attempt == tries - 1:
                raise
            time.sleep((2 ** attempt) * 0.05 * (1 + random.random()))

Cap the retries, add jitter, and make the work function side-effect free outside the database, because it will run more than once. Deadlocks are detected after deadlock_timeout (default 1s), and the fix is to lock rows in a consistent order across all code paths.

Row locks and SKIP LOCKED

Explicit row locks in a SELECT reserve rows for the rest of the transaction.

BEGIN;
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;
UPDATE accounts SET balance = balance - $2 WHERE id = $1;
COMMIT;

Add a wait policy. They are mutually exclusive.

SELECT * FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;        -- error 55P03 if locked
SELECT * FROM jobs WHERE state = 'ready' FOR UPDATE SKIP LOCKED;  -- ignore locked rows
SELECT * FROM a JOIN b ON ... FOR UPDATE OF a;            -- lock one table's rows

SKIP LOCKED (9.5+) is what makes a correct queue possible in plain SQL. Many workers can poll the same table and never collide or block.

-- One worker claiming a batch of jobs
WITH claimed AS (
  SELECT id FROM jobs
  WHERE state = 'ready' AND run_after <= now()
  ORDER BY priority DESC, run_after
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs j
SET state = 'running', started_at = now(), attempts = j.attempts + 1
FROM claimed c
WHERE j.id = c.id
RETURNING j.*;

FOR UPDATE cannot be combined with GROUP BY, DISTINCT, UNION, or window functions, which is why the pattern is a CTE that locks plain rows and an outer update.

Table locks

Eight table-level modes. You rarely take one by hand, but knowing which DDL takes which is how you avoid taking an application down.

The practical rule: ACCESS EXCLUSIVE blocks reads. An ALTER TABLE that waits behind one long SELECT then queues every subsequent query behind itself, which is how a "one second" migration becomes an outage. Always run migrations with a short lock_timeout and retry.

SET lock_timeout = '3s';           -- fail fast instead of queueing behind a reader
ALTER TABLE orders ADD COLUMN note text;

BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;   -- only valid inside a block
COMMIT;

Advisory locks

Application-defined locks keyed by one bigint or two ints. Postgres does not interpret them, and they never touch a row.

-- Make sure only one instance runs a job, without a lock table
SELECT pg_try_advisory_xact_lock(hashtext('nightly-rollup')) AS got_it;

Prefer the _xact_ forms. A session-scope lock leaked by a crashed client survives until the connection dies, and a pooled connection can hand a stale lock to the next user.

Timeouts and unblocking

Set these per session, per role, or in postgresql.conf. Zero disables.

SET statement_timeout = '30s';
ALTER ROLE app_readonly SET statement_timeout = '10s';
ALTER DATABASE app SET idle_in_transaction_session_timeout = '60s';

Find what is blocking what. pg_blocking_pids() does the hard part.

SELECT blocked.pid                AS blocked_pid,
       blocked.wait_event_type,
       blocked.wait_event,
       now() - blocked.query_start AS blocked_for,
       blocked.query              AS blocked_query,
       blocking.pid               AS blocking_pid,
       blocking.state             AS blocking_state,
       blocking.query             AS blocking_query
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp
JOIN pg_stat_activity blocking ON blocking.pid = bp
ORDER BY blocked_for DESC;

-- What locks a single backend holds
SELECT locktype, relation::regclass, mode, granted
FROM pg_locks WHERE pid = 12345;

-- Long-running and idle-in-transaction sessions
SELECT pid, state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state <> 'idle' OR state = 'idle in transaction'
ORDER BY xact_age DESC NULLS LAST;

SELECT pg_cancel_backend(12345);      -- cancel the current query, polite
SELECT pg_terminate_backend(12345);   -- drop the connection, rolls back

Try pg_cancel_backend first. pg_terminate_backend kills the whole session and takes an optional millisecond timeout as a second argument (14+). A session stuck as idle in transaction is not running a query, so cancel does nothing and terminate is the only option.