Transactions

BEGIN / COMMIT / ROLLBACK and what they actually do, plus savepoints, subtransactions and their hidden cost, the four timeouts that bound transaction lifetime, and two-phase commit (PREPARE TRANSACTION). Isolation-level semantics (READ COMMITTED / REPEATABLE READ / SERIALIZABLE) live in 42-isolation-levels.md; the snapshot/xmin model behind them lives in 27-mvcc-internals.md.

Table of Contents

• When to Use This Reference
• Mental Model
• Decision Matrix
• Syntax and Mechanics
  • Autocommit and Explicit Transactions
  • BEGIN / START TRANSACTION
  • COMMIT and ROLLBACK
  • END is COMMIT
  • AND CHAIN
  • SAVEPOINT / RELEASE / ROLLBACK TO
  • Subtransactions and Their Cost
  • SET TRANSACTION and Transaction Characteristics
  • DEFERRABLE SERIALIZABLE READ ONLY
  • SET TRANSACTION SNAPSHOT
  • The Five Timeouts
  • PREPARE TRANSACTION (2PC)
  • pg_prepared_xacts
  • Per-version Timeline
• Examples and Recipes
• Gotchas and Anti-patterns
• See Also
• Sources

When to Use This Reference

Reach for this file when you need to:

• Pick the right shape for an explicit transaction (BEGIN ... COMMIT vs autocommit, with-or-without AND CHAIN)
• Use savepoints correctly — and understand why per-row EXCEPTION blocks in PL/pgSQL loops are slow
• Configure timeouts that prevent a stuck transaction from holding xmin and blocking VACUUM
• Decide whether to enable two-phase commit (max_prepared_transactions) and how to clean up abandoned prepared transactions
• Diagnose idle in transaction sessions, prepared-transaction leaks, or subxact overflow
• Snapshot-share two parallel sessions for consistent multi-connection reads (SET TRANSACTION SNAPSHOT)

Cross-references: 27-mvcc-internals.md for xmin/xmax and the snapshot model, 28-vacuum-autovacuum.md for the idle-in-transaction → bloat link, 29-transaction-id-wraparound.md for the prepared-transaction → wraparound link, 42-isolation-levels.md for READ COMMITTED/REPEATABLE READ/SERIALIZABLE, 43-locking.md for what lock_timeout aborts, 08-plpgsql.md for EXCEPTION blocks creating subtransactions.

Mental Model

Five rules drive every transaction decision:

1. Without BEGIN, every statement is its own transaction. Verbatim: "By default (without BEGIN), PostgreSQL executes transactions in 'autocommit' mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done)."¹ An explicit BEGIN starts a transaction block; every statement until COMMIT or ROLLBACK is part of the same transaction with a single XID and snapshot.

2. Savepoints are subtransactions; subtransactions cost real money. Each SAVEPOINT (and each PL/pgSQL EXCEPTION block) starts a subtransaction. Verbatim from the internals chapter: "Up to 64 open subxids are cached in shared memory for each backend; after that point, the storage I/O overhead increases significantly due to additional lookups of subxid entries in pg_subtrans."² Beyond 64, pg_subtrans SLRU lookups dominate latency and an EXCEPTION-in-tight-loop becomes pathological. See Subtransactions and Their Cost.

3. PREPARE TRANSACTION is off by default and almost certainly should stay off. max_prepared_transactions = 0 is the default, which disables the feature entirely.³ Verbatim from PREPARE TRANSACTION: "PREPARE TRANSACTION is not intended for use in applications or interactive sessions. Its purpose is to allow an external transaction manager to perform atomic global transactions across multiple databases or other transactional resources. Unless you're writing a transaction manager, you probably shouldn't be using PREPARE TRANSACTION."⁴ And: "It is unwise to leave transactions in the prepared state for a long time. This will interfere with the ability of VACUUM to reclaim storage, and in extreme cases could cause the database to shut down to prevent transaction ID wraparound."⁴

4. Idle sessions inside an open transaction are catastrophic; idle sessions outside one are merely annoying. An open transaction holds an xmin that prevents VACUUM from reclaiming dead tuples cluster-wide. Verbatim: "Even when no significant locks are held, an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction; so remaining idle for a long time can contribute to table bloat."⁵ idle_in_transaction_session_timeout must be set in production. The PG14+ idle_session_timeout only kills sessions that are not in a transaction — it's less critical.

5. There are five timeouts that bound transaction lifetime; they compose, not conflict. statement_timeout (per statement), lock_timeout (per lock acquisition), idle_in_transaction_session_timeout (idle inside a transaction), idle_session_timeout (idle outside a transaction, PG14+), and transaction_timeout (whole-transaction wall clock, PG17+). They interact: "If transaction_timeout is shorter or equal to idle_in_transaction_session_timeout or statement_timeout then the longer timeout is ignored."⁶

[!WARNING] Prepared transactions can break wraparound prevention A prepared transaction's xmin is held until COMMIT PREPARED or ROLLBACK PREPARED runs — even after the originating session disconnects. An abandoned prepared transaction blocks VACUUM forever. Verbatim: "If you have not set up an external transaction manager to track prepared transactions and ensure they get closed out promptly, it is best to keep the prepared-transaction feature disabled by setting max_prepared_transactions to zero."⁴ See 29-transaction-id-wraparound.md.

Decision Matrix

Three smell signals that you reached for the wrong tool:

• Per-row BEGIN/COMMIT inside a single business operation — each statement that needs to commit-or-rollback together belongs in one transaction. Repeatedly committing midway through fragments atomicity and quadruples WAL volume.
• Catching unique_violation per row in a PL/pgSQL FOREACH loop — each EXCEPTION block creates a subtransaction. At 1000 rows you get 1000 subxids, 16× the overflow threshold. Move to INSERT ... ON CONFLICT DO NOTHING (set-based), see 08-plpgsql.md Recipe 8.
• Setting statement_timeout in postgresql.conf — it kills pg_dump, REINDEX CONCURRENTLY, autovacuum, and every legitimate long-running maintenance. Use per-role or SET LOCAL instead.

Syntax and Mechanics

Autocommit and Explicit Transactions

PostgreSQL is autocommit by default (see Mental Model rule 1 for the verbatim quote). Some clients also do their own autocommit toggling. psql's \set AUTOCOMMIT off makes psql wrap every statement in an implicit BEGIN until COMMIT. JDBC's Connection.setAutoCommit(false), libpq's BEGIN issued at connection time, ORM frameworks (Hibernate's @Transactional, SQLAlchemy's Session, etc.) all do something similar. The server doesn't know or care which side started the transaction — once it sees a BEGIN (or its first statement under client-side autocommit-off mode), it opens a transaction block.

BEGIN / START TRANSACTION

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

START TRANSACTION [ transaction_mode [, ...] ]

transaction_mode :=
    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
  | READ WRITE | READ ONLY
  | [ NOT ] DEFERRABLE

BEGIN and START TRANSACTION are identical. Verbatim: "START TRANSACTION has the same functionality as BEGIN."¹ BEGIN is a PostgreSQL extension; START TRANSACTION is the SQL-standard form.¹

The WORK and TRANSACTION noise words are optional and equivalent. BEGIN;, BEGIN WORK;, and BEGIN TRANSACTION; are all the same.

BEGIN inside an existing transaction is a warning, not an error. Verbatim: "Issuing BEGIN when already inside a transaction block will provoke a warning message. The state of the transaction is not affected. To nest transactions within a transaction block, use savepoints (see SAVEPOINT)."¹ PostgreSQL has no nested-transaction syntax; the only way to nest is via savepoints.

COMMIT and ROLLBACK

COMMIT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]

ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]

COMMIT makes the transaction's changes visible to other sessions and durable. Verbatim: "COMMIT commits the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs."⁷ Whether durability requires a fsync at COMMIT time is controlled by synchronous_commit — see 33-wal.md.

ROLLBACK discards everything. Verbatim: "ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded."⁸

COMMIT and ROLLBACK outside a transaction block are warnings, not errors. Verbatim from COMMIT: "Issuing COMMIT when not inside a transaction does no harm, but it will provoke a warning message. COMMIT AND CHAIN when not inside a transaction is an error."⁷

END is COMMIT

END [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]

END is a PostgreSQL-only synonym for COMMIT. Verbatim: "END commits the current transaction. ... This command is a PostgreSQL extension that is equivalent to COMMIT."⁹ There is no END synonym for ROLLBACK. Don't use END in code that needs to be SQL-standard portable.

AND CHAIN

COMMIT AND CHAIN or ROLLBACK AND CHAIN immediately starts a new transaction with the same characteristics as the just-finished one. Verbatim: "If AND CHAIN is specified, a new transaction is immediately started with the same transaction characteristics (see SET TRANSACTION) as the just finished one. Otherwise, no new transaction is started."⁷

This is the right primitive for "loop over batches in one connection, each batch its own transaction, all batches with the same isolation level":

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT process_batch(1);
COMMIT AND CHAIN;
SELECT process_batch(2);
COMMIT AND CHAIN;
-- ... continues with REPEATABLE READ READ ONLY ...
COMMIT;

AND NO CHAIN is the default (no new transaction). It exists for symmetry with the SQL standard; you almost never write it explicitly.

SAVEPOINT / RELEASE / ROLLBACK TO

SAVEPOINT savepoint_name

RELEASE [ SAVEPOINT ] savepoint_name

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

SAVEPOINT opens a named subtransaction. Verbatim: "A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint."¹⁰

RELEASE SAVEPOINT name discards the savepoint name but keeps the work done inside it (merges it into the parent transaction or savepoint). Verbatim: "RELEASE SAVEPOINT releases the named savepoint and all active savepoints that were created after the named savepoint, and frees their resources. All changes made since the creation of the savepoint that didn't already get rolled back are merged into the transaction or savepoint that was active when the named savepoint was created."¹¹

ROLLBACK TO SAVEPOINT name undoes work done after the savepoint and leaves the savepoint open so you can try again. Verbatim: "Roll back all commands that were executed after the savepoint was established and then start a new subtransaction at the same transaction level. The savepoint remains valid and can be rolled back to again later, if needed."¹²

The keyword SAVEPOINT after RELEASE or ROLLBACK TO is optional in PostgreSQL: RELEASE foo; and ROLLBACK TO foo; work, but the SQL standard requires the keyword.¹¹¹²

Same-name shadowing differs from the SQL standard. Verbatim: "SQL requires a savepoint to be destroyed automatically when another savepoint with the same name is established. In PostgreSQL, the old savepoint is kept, though only the more recent one will be used when rolling back or releasing."¹⁰ A second SAVEPOINT foo doesn't drop the first; releases happen in LIFO order. This bites scripts that assume one-savepoint-per-name semantics.

Cursor caveat. Verbatim: "Any cursor that is opened inside a savepoint will be closed when the savepoint is rolled back. ... A cursor whose execution causes a transaction to abort is put in a cannot-execute state, so while the transaction can be restored using ROLLBACK TO SAVEPOINT, the cursor can no longer be used."¹²

Subtransactions and Their Cost

A subtransaction is started by either:

• An explicit SAVEPOINT command, or
• PL/pgSQL's BEGIN ... EXCEPTION WHEN ... END; block (each EXCEPTION block opens an implicit subtransaction so that on error the block's work can be rolled back without aborting the surrounding transaction)

Verbatim from the internals chapter: "Subtransactions are started inside transactions, allowing large transactions to be broken into smaller units. Subtransactions can commit or abort without affecting their parent transactions. ... Subtransactions can be started explicitly using the SAVEPOINT command, but can also be started in other ways, such as PL/pgSQL's EXCEPTION clause."²

Subxids only consume resources if they write. Verbatim: "Read-only subtransactions are not assigned subxids, but once they attempt to write, they will be assigned one. This also causes all of a subxid's parents, up to and including the top-level transaction, to be assigned non-virtual transaction ids."²

The 64-subxact threshold. This is the canonical operational rule. Verbatim: "The more subtransactions each transaction keeps open (not rolled back or released), the greater the transaction management overhead. Up to 64 open subxids are cached in shared memory for each backend; after that point, the storage I/O overhead increases significantly due to additional lookups of subxid entries in pg_subtrans."²

Operational consequences:

• More than 64 active subxids per backend pushes lookups into the pg_subtrans SLRU. SLRU contention shows up as SubtransSLRU wait events in pg_stat_activity.wait_event.
• The threshold is per-backend, not cluster-wide; the SLRU is shared so contention from many backends compounds.
• Releasing or rolling back a savepoint frees its subxid. The threshold counts active subxids, not lifetime created.
• PL/pgSQL EXCEPTION blocks always create subxacts — even when they catch no exception in a particular iteration — so a loop of 1000 iterations with an EXCEPTION block creates 1000 subxids in sequence (released as each iteration completes the inner BEGIN block).

[!NOTE] PostgreSQL 16 PG16 added pg_stat_get_backend_subxact() to report per-backend subxid usage live. Verbatim release note: "Add function pg_stat_get_backend_subxact() to report on a session's subtransaction cache (Dilip Kumar)."¹³ Pre-PG16 the only way to detect overflow was watching SubtransSLRU wait events.

SET TRANSACTION and Transaction Characteristics

SET TRANSACTION transaction_mode [, ...]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

transaction_mode :=
    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
  | READ WRITE | READ ONLY
  | [ NOT ] DEFERRABLE

SET TRANSACTION changes the current transaction's characteristics. Verbatim: "The SET TRANSACTION command sets the characteristics of the current transaction. It has no effect on any subsequent transactions. SET SESSION CHARACTERISTICS sets the default transaction characteristics for subsequent transactions of a session."¹⁴

The four isolation levels. Verbatim from PG16:

• READ COMMITTED — "A statement can only see rows committed before it began. This is the default."¹⁴
• REPEATABLE READ — "All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction."¹⁴
• SERIALIZABLE — "All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error."¹⁴
• READ UNCOMMITTED — accepted but treated as READ COMMITTED. Verbatim: "The SQL standard defines one additional level, READ UNCOMMITTED. In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED."¹⁴

Full semantics for each level live in 42-isolation-levels.md.

Isolation can only be set at the start of a transaction. Verbatim: "The transaction isolation level cannot be changed after the first query or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE, FETCH, or COPY) of a transaction has been executed."¹⁴ In practice this means: put SET TRANSACTION ISOLATION LEVEL ... (or use BEGIN ISOLATION LEVEL ...) before anything else.

Default isolation is READ COMMITTED. Set via default_transaction_isolation. Verbatim: "This parameter controls the default isolation level of each new transaction. The default is 'read committed'."¹⁵ The SQL standard's default is SERIALIZABLE.¹⁴

Read-only mode. SET TRANSACTION READ ONLY (or BEGIN READ ONLY, or default_transaction_read_only = on). Verbatim: "A read-only SQL transaction cannot alter non-temporary tables. ... The default is off (read/write)."¹⁶ Useful for analytics workloads that should never accidentally write, and as a defense-in-depth measure for read-replica connections.

DEFERRABLE SERIALIZABLE READ ONLY

The DEFERRABLE flag only matters in combination with SERIALIZABLE READ ONLY. Verbatim: "The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups."¹⁴

The canonical use case: a long-running analytics report that needs serializable semantics but cannot tolerate being aborted with serialization_failure.

BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
-- waits briefly for a "safe" snapshot, then runs without abort risk
SELECT large_report();
COMMIT;

SET TRANSACTION SNAPSHOT

Two parallel sessions can share an exact snapshot. Session A exports the snapshot, session B imports it.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();         -- returns e.g. '00000003-00000007-1'

-- Session B (within 60s or so)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-00000007-1';
-- now sees exactly what session A sees

Verbatim restrictions: "The SET TRANSACTION SNAPSHOT command allows a new transaction to run with the same snapshot as an existing transaction."¹⁴ "SET TRANSACTION SNAPSHOT can only be executed at the start of a transaction, before the first query or data-modification statement."¹⁴

This is how pg_dump --jobs=N keeps every parallel dump worker on the same MVCC snapshot.

The Five Timeouts

statement_timeout. Verbatim: "Abort any statement that takes more than the specified amount of time. ... A value of zero (the default) disables the timeout."¹⁷ "Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions."¹⁷

Pre-PG13 behavior difference for simple-query protocol: "If multiple SQL statements appear in a single simple-Query message, the timeout is applied to each statement separately. (PostgreSQL versions before 13 usually treated the timeout as applying to the whole query string.)"¹⁷

lock_timeout. Verbatim: "Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object. The time limit applies separately to each lock acquisition attempt. ... A value of zero (the default) disables the timeout."¹⁸ "Unlike statement_timeout, this timeout can only occur while waiting for locks. Note that if statement_timeout is nonzero, it is rather pointless to set lock_timeout to the same or larger value, since the statement timeout would always trigger first."¹⁸

The right shape for online DDL: SET LOCAL lock_timeout = '500ms' before any ALTER TABLE that takes ACCESS EXCLUSIVE so the migration doesn't queue behind a long-running transaction.

idle_in_transaction_session_timeout. Verbatim: "Terminate any session that has been idle (that is, waiting for a client query) within an open transaction for longer than the specified amount of time. ... A value of zero (the default) disables the timeout."⁵ "This option can be used to ensure that idle sessions do not hold locks for an unreasonable amount of time. Even when no significant locks are held, an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction; so remaining idle for a long time can contribute to table bloat."⁵

Set it to 1min or 5min for OLTP workloads.

idle_session_timeout (PG14+). Verbatim: "Terminate any session that has been idle (that is, waiting for a client query), but not within an open transaction, for longer than the specified amount of time. ... A value of zero (the default) disables the timeout."¹⁹

Less urgent than idle_in_transaction_session_timeout. Verbatim: "Unlike the case with an open transaction, an idle session without a transaction imposes no large costs on the server, so there is less need to enable this timeout."¹⁹ But: "Be wary of enforcing this timeout on connections made through connection-pooling software or other middleware, as such a layer may not react well to unexpected connection closure."¹⁹

[!NOTE] PostgreSQL 14 Verbatim release note: "Add server parameter idle_session_timeout to close idle sessions (Li Japin). This is similar to idle_in_transaction_session_timeout."²⁰

transaction_timeout (PG17+). Verbatim from PG17: "Terminate any session that spans longer than the specified amount of time in a transaction. The limit applies both to explicit transactions (started with BEGIN) and to an implicitly started transaction corresponding to a single statement. ... A value of zero (the default) disables the timeout."⁶ "Setting transaction_timeout in postgresql.conf is not recommended because it would affect all sessions."⁶

The interaction rule: "If transaction_timeout is shorter or equal to idle_in_transaction_session_timeout or statement_timeout then the longer timeout is ignored."⁶

The prepared-transaction exception. Verbatim: "Prepared transactions are not subject to this timeout."⁶ Prepared transactions are detached from any session, so per-session timeouts cannot fire.

[!NOTE] PostgreSQL 17 Verbatim release note: "Add server variable transaction_timeout to restrict the duration of transactions (Andrey Borodin, Japin Li, Junwang Zhao, Alexander Korotkov)."²¹

PREPARE TRANSACTION (2PC)

PREPARE TRANSACTION transaction_id

COMMIT PREPARED transaction_id

ROLLBACK PREPARED transaction_id

Verbatim: "PREPARE TRANSACTION prepares the current transaction for two-phase commit. After this command, the transaction is no longer associated with the current session; instead, its state is fully stored on disk, and there is a very high probability that it can be committed successfully, even if a database crash occurs before the commit is requested."⁴

After PREPARE TRANSACTION, the transaction is detached from any session and waits for either COMMIT PREPARED or ROLLBACK PREPARED. Verbatim: "Once prepared, a transaction can later be committed or rolled back with COMMIT PREPARED or ROLLBACK PREPARED, respectively. Those commands can be issued from any session, not only the one that executed the original transaction."⁴

The GID. The argument to PREPARE TRANSACTION is the global transaction identifier (GID). Verbatim: "An arbitrary identifier that later identifies this transaction for COMMIT PREPARED or ROLLBACK PREPARED. The identifier must be written as a string literal, and must be less than 200 bytes long. It must not be the same as the identifier used for any currently prepared transaction."⁴

What a prepared transaction cannot have done. Verbatim: "It is not currently allowed to PREPARE a transaction that has executed any operations involving temporary tables or the session's temporary namespace, created any cursors WITH HOLD, or executed LISTEN, UNLISTEN, or NOTIFY."⁴

Disabled by default. Verbatim from the GUC: "max_prepared_transactions (integer) — Sets the maximum number of transactions that can be in the 'prepared' state simultaneously (see PREPARE TRANSACTION). Setting this parameter to zero (which is the default) disables the prepared-transaction feature. This parameter can only be set at server start."³

Standby implication. Verbatim: "When running a standby server, you must set this parameter to the same or higher value than on the primary server. Otherwise, queries will not be allowed in the standby server."³

Permission to commit/rollback prepared. Verbatim from COMMIT PREPARED: "To commit a prepared transaction, you must be either the same user that executed the transaction originally, or a superuser. But you do not have to be in the same session that executed the transaction."²² The same rule applies to ROLLBACK PREPARED.²³

Cannot run inside a transaction block. Verbatim: "This command cannot be executed inside a transaction block. The prepared transaction is committed immediately."²²

Storage of long-lived prepared transactions. Verbatim from the internals chapter on 2PC: "In general, this prepared state is intended to be of very short duration, but external availability issues might mean transactions stay in this state for an extended interval. Short-lived prepared transactions are stored only in shared memory and WAL. Transactions that span checkpoints are recorded in the pg_twophase directory."²⁴

pg_prepared_xacts

Verbatim: "The view pg_prepared_xacts displays information about transactions that are currently prepared for two-phase commit. ... pg_prepared_xacts contains one row per prepared transaction. An entry is removed when the transaction is committed or rolled back."²⁵

Columns:

Verbatim note: "When the pg_prepared_xacts view is accessed, the internal transaction manager data structures are momentarily locked, and a copy is made for the view to display."²⁵ Don't poll it at high frequency.

Per-version Timeline

Examples and Recipes

1. Baseline production timeouts

Per-role timeouts are the right granularity: they don't affect superuser maintenance, autovacuum, or pg_dump.

-- Application role (interactive web app)
ALTER ROLE webapp SET statement_timeout = '30s';
ALTER ROLE webapp SET lock_timeout = '5s';
ALTER ROLE webapp SET idle_in_transaction_session_timeout = '60s';

-- PG14+: catch leaked connections
ALTER ROLE webapp SET idle_session_timeout = '15min';

-- PG17+: bound any transaction's wall-clock duration
ALTER ROLE webapp SET transaction_timeout = '5min';

-- Batch role: longer statement timeout, same idle limits
ALTER ROLE batchjobs SET statement_timeout = '1h';
ALTER ROLE batchjobs SET lock_timeout = '30s';
ALTER ROLE batchjobs SET idle_in_transaction_session_timeout = '60s';

These apply at connection time; existing sessions are unaffected until reconnect. Use SET (session-scope) or SET LOCAL (transaction-scope) inside an open session to change them per-operation.

2. Online DDL with lock_timeout

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '10s';

ALTER TABLE big_table ADD COLUMN new_col integer NULL;

COMMIT;

The SET LOCAL form resets at COMMIT/ROLLBACK, so it doesn't bleed into the next operation. If the ALTER TABLE cannot acquire ACCESS EXCLUSIVE within 500ms (because a long-running transaction is holding ACCESS SHARE), it aborts cleanly instead of blocking the table. Retry the migration on a quieter window.

3. Diagnose idle-in-transaction sessions

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    state_change,
    now() - state_change AS idle_for,
    backend_xmin,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change ASC;

The leftmost (oldest state_change) row is the one holding the xmin horizon back. Terminate it with SELECT pg_terminate_backend(<pid>); after confirming. Better: set idle_in_transaction_session_timeout so this never lingers in the first place.

4. Subtransaction overflow audit

PG16+ via pg_stat_get_backend_subxact():

SELECT
    a.pid,
    a.usename,
    a.application_name,
    a.state,
    s.subxact_count,
    s.subxact_overflow,
    a.query
FROM pg_stat_activity a
CROSS JOIN LATERAL pg_stat_get_backend_subxact(a.backend_xid) s
WHERE s.subxact_count > 32
ORDER BY s.subxact_count DESC;

subxact_overflow = true means this backend has overflowed past 64 subxids; lookups now hit pg_subtrans SLRU. Cross-reference 08-plpgsql.md for the EXCEPTION-block-in-loop anti-pattern that produces this.

Pre-PG16 audit (less precise):

SELECT pid, wait_event_type, wait_event, COUNT(*) AS sample_count
FROM pg_stat_activity
WHERE wait_event LIKE 'SubtransSLRU%'
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

5. SAVEPOINT for retry-on-conflict

BEGIN;
INSERT INTO orders (...) VALUES (...);

SAVEPOINT before_inventory;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'A123' AND qty > 0;
-- Did the inventory check fail (zero rows updated)?
DO $$
BEGIN
    IF NOT FOUND THEN
        ROLLBACK TO SAVEPOINT before_inventory;
        INSERT INTO backorders (...) VALUES (...);
    END IF;
END $$;
RELEASE SAVEPOINT before_inventory;

COMMIT;

The ROLLBACK TO SAVEPOINT discards the failed UPDATE but keeps the original INSERT. The savepoint is then released, merging the recovered state into the parent transaction.

6. SET LOCAL for one-statement overrides

BEGIN;
SET LOCAL statement_timeout = '0';            -- disable for this txn only
SET LOCAL lock_timeout = '0';
CLUSTER big_table USING big_table_pkey;
COMMIT;

SET LOCAL is transaction-scoped — the timeouts reset to the session value at COMMIT. Without LOCAL, the SET would persist for the rest of the session.

7. Snapshot-share two parallel readers

-- Session A: opens snapshot, exports its identifier
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();          -- e.g. '00000003-00000007-1'
-- Session A continues with its work...

-- Session B (different connection, within ~60s):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-00000007-1';
-- B now sees exactly what A sees
COPY (SELECT * FROM big_table) TO STDOUT;
COMMIT;

This is the mechanism pg_dump uses for --jobs > 1. The exported snapshot expires when the exporting transaction commits or rolls back.

8. DEFERRABLE serializable read-only for reports

BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
SELECT
    customer_id,
    SUM(amount) AS lifetime_value
FROM transactions
GROUP BY customer_id;
-- ... rest of multi-query report ...
COMMIT;

The session may block briefly at the start (waiting for a "safe" snapshot the SSI machinery can confirm needs no abort risk). Once it begins, it runs without the per-tuple overhead of serializable and is immune to serialization-failure aborts.

9. Audit and clean up prepared transactions

-- Inventory all currently-prepared transactions:
SELECT gid, prepared, owner, database, age(prepared) AS age
FROM pg_prepared_xacts
ORDER BY prepared ASC;

-- If a prepared transaction is older than your tolerance (e.g. >1h),
-- the transaction coordinator likely died. After verifying with the app:
ROLLBACK PREPARED 'gid_value_from_query';

Until you commit-or-rollback a prepared transaction, its xmin blocks VACUUM cluster-wide. The cleanup is COMMIT PREPARED 'gid'; or ROLLBACK PREPARED 'gid'; — must be run by the original user or a superuser.

10. Disable PREPARE TRANSACTION entirely

-- postgresql.conf or ALTER SYSTEM
ALTER SYSTEM SET max_prepared_transactions = 0;
-- Requires server restart to take effect.

If you have no external transaction manager (XA coordinator, distributed-tx middleware), set this to 0 and keep it there. A stale prepared transaction is one of the few ways a single mistake can take down a cluster via wraparound.

11. Bound a maintenance command with transaction_timeout (PG17+)

BEGIN;
SET LOCAL transaction_timeout = '10min';
SET LOCAL lock_timeout = '30s';
VACUUM (VERBOSE, ANALYZE) target_table;
COMMIT;

transaction_timeout aborts the whole transaction (statement and idle time combined) once 10min of wall clock elapses, regardless of whether statement_timeout has fired. Note: prepared transactions are not subject to this timeout (they can't be — they're not attached to any session).

12. AND CHAIN for repeated read-only batches

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
INSERT INTO archive_buffer SELECT * FROM source WHERE batch_id = 1;
COMMIT AND CHAIN;

INSERT INTO archive_buffer SELECT * FROM source WHERE batch_id = 2;
COMMIT AND CHAIN;

-- ... no need to re-state REPEATABLE READ READ ONLY each iteration ...
COMMIT;

The AND CHAIN form preserves isolation level, read-only flag, and deferrable flag from the just-committed transaction.

13. Catalog overview of the four timeouts at session level

SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN (
    'statement_timeout',
    'lock_timeout',
    'idle_in_transaction_session_timeout',
    'idle_session_timeout',
    'transaction_timeout'
)
ORDER BY name;

context = 'user' for all five — they can be set per-session, per-role, per-database, or cluster-wide. source shows where the current value came from (default / configuration file / role / database / session).

Gotchas and Anti-patterns

1. statement_timeout in postgresql.conf kills your maintenance. It applies to every session including pg_dump, autovacuum's manual VACUUM, REINDEX CONCURRENTLY, and any cron-scheduled job. Set it per-role on the application role instead.

2. SET without LOCAL persists for the session. SET statement_timeout = '0' inside a transaction bleeds into every subsequent transaction on that connection. Use SET LOCAL when you want transaction scope.

3. SAVEPOINT foo; SAVEPOINT foo; keeps both — PostgreSQL extension to the standard. SQL says the first foo should be destroyed; PG keeps it. Releases happen in LIFO order. Scripts ported from other DBs may misbehave.

4. RELEASE cannot run when the transaction is in an aborted state. Verbatim: "It is not possible to release a savepoint when the transaction is in an aborted state; to do that, use ROLLBACK TO SAVEPOINT."¹¹ You must roll back to the savepoint first, then release.

5. A PL/pgSQL EXCEPTION block in a tight loop blows past 64 subxids fast. 1000 iterations = 1000 subxids (each released, but pressure builds). Wait events tell the story: SubtransSLRU. Move to set-based DML or INSERT ... ON CONFLICT DO NOTHING. Cross-reference 08-plpgsql.md gotcha #9.

6. idle_in_transaction_session_timeout = 0 (default) is dangerous for OLTP. A client that hangs (network drop, client deadlock, debugger) keeps a transaction open forever; xmin is pinned; VACUUM can't reclaim dead tuples. Set this. Always.

7. idle_session_timeout confuses connection poolers. Verbatim warning: "Be wary of enforcing this timeout on connections made through connection-pooling software or other middleware, as such a layer may not react well to unexpected connection closure."¹⁹ If the pool keeps idle connections open longer than your timeout, the pool will get surprise disconnects. Coordinate with pool settings.

8. transaction_timeout skips prepared transactions. Verbatim: "Prepared transactions are not subject to this timeout."⁶ A leaked prepared transaction is invisible to all session-level timeouts and must be cleaned up via pg_prepared_xacts audits.

9. statement_timeout = 5s, transaction_timeout = 1s → only transaction_timeout matters. Verbatim: "If transaction_timeout is shorter or equal to idle_in_transaction_session_timeout or statement_timeout then the longer timeout is ignored."⁶

10. Pre-PG13 statement_timeout over a multi-statement simple-Query message timed the whole message. PG13+ times each statement separately. If you carried forward an old statement_timeout from a PG12 cluster, behavior changed silently in PG13.¹⁷

11. Default isolation level is READ COMMITTED, not SERIALIZABLE. The SQL standard says SERIALIZABLE; PostgreSQL says READ COMMITTED. If your application requires a stronger guarantee, set default_transaction_isolation = serializable on the role.¹⁵

12. Isolation cannot be changed after the first statement. SET TRANSACTION ISOLATION LEVEL ... after a single SELECT errors out. Use BEGIN ISOLATION LEVEL ... instead.¹⁴

13. READ UNCOMMITTED is silently READ COMMITTED. Verbatim: "In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED."¹⁴ No dirty reads ever happen in PG; this is intentional.

14. DEFERRABLE is meaningless without both SERIALIZABLE and READ ONLY. Verbatim: "The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY."¹⁴

15. pg_export_snapshot() requires the exporting transaction to stay open. As soon as session A commits, the exported snapshot expires and session B's SET TRANSACTION SNAPSHOT errors with ERROR: invalid snapshot identifier.

16. BEGIN already inside a transaction is a warning, not an error. It doesn't open a nested transaction. If you want nesting, you need SAVEPOINT. Drivers that issue spurious BEGINs leak warnings into logs.¹

17. PREPARE TRANSACTION cannot follow LISTEN/NOTIFY/WITH HOLD cursor/temp-table activity. Verbatim: "It is not currently allowed to PREPARE a transaction that has executed any operations involving temporary tables or the session's temporary namespace, created any cursors WITH HOLD, or executed LISTEN, UNLISTEN, or NOTIFY."⁴

18. COMMIT PREPARED cannot run inside a transaction block. Verbatim: "This command cannot be executed inside a transaction block."²² You can't wrap it in BEGIN; COMMIT PREPARED '...'; COMMIT; — it must be at the top level.

19. GID must be a string literal, ≤ 200 bytes. Verbatim: "The identifier must be written as a string literal, and must be less than 200 bytes long."⁴ Numeric GIDs get cast to string in error messages, but you must always use string syntax.

20. Standby must have max_prepared_transactions ≥ primary's. Verbatim: "When running a standby server, you must set this parameter to the same or higher value than on the primary server. Otherwise, queries will not be allowed in the standby server."³ A standby with a smaller value won't accept connections.

21. A prepared transaction's xmin is held until commit/rollback prepared. Even after the originating session disconnects, the xmin lives in pg_twophase (if it spanned a checkpoint) and continues to block VACUUM. Cross-reference 29-transaction-id-wraparound.md.

22. END is a PG-only synonym for COMMIT — there is no END for ROLLBACK. Code that uses END; to commit is non-portable. COMMIT; is the safe choice.⁹

23. ROLLBACK outside a transaction is a warning, not an error. Defensive code that "always rolls back" between operations litters logs with warnings. Driver-level autocommit handling is preferable.⁸

See Also

• 27-mvcc-internals.md — tuple xmin/xmax, snapshot xip[], why long-running transactions block VACUUM
• 28-vacuum-autovacuum.md — the operational consequence of an idle-in-transaction session pinning xmin
• 29-transaction-id-wraparound.md — prepared transactions and the wraparound danger
• 42-isolation-levels.md — full semantics of READ COMMITTED / REPEATABLE READ / SERIALIZABLE including SSI
• 43-locking.md — what lock_timeout aborts; the full lock-conflict matrix
• 44-advisory-locks.md — transaction-scoped vs session-scoped advisory locks
• 08-plpgsql.md — EXCEPTION blocks creating subtransactions; the loop anti-pattern
• 07-procedures.md — procedures and their ability to issue COMMIT/ROLLBACK inside the procedure body
• 33-wal.md — synchronous_commit and the durability/latency tradeoff at COMMIT time
• 58-performance-diagnostics.md — pg_stat_activity columns including state, wait_event, backend_xmin
• 64-system-catalogs.md — pg_prepared_xacts, pg_locks
• 74-logical-replication.md — two-phase commit decoding (PG14+) and --enable-two-phase (PG18+)
• 80-connection-pooling.md — pool-mode interaction with idle_session_timeout
• 45-listen-notify.md — LISTEN/NOTIFY incompatibility with PREPARE TRANSACTION (gotcha #17); commit-delivery semantics
• 46-roles-privileges.md — ALTER ROLE … SET syntax for applying per-role timeout defaults (Recipe 1)
• 81-pgbouncer.md — transaction-mode pooling and LISTEN/prepared-statement limitations

Sources