Clearing Idle-in-Transaction Sessions Before a Migration

The migration timed out on its lock again, and the blocker is the same shape every time: a session in state idle in transaction, owned by the application role, with an xact_start several minutes old and a last query that was a trivial SELECT. Nothing is running. The application opened a transaction, read a row, and then — waiting on an HTTP call, a message queue, a slow template render — simply stopped talking to the database without committing. That session holds its locks for as long as the transaction stays open, and your ALTER TABLE cannot get past it. This guide shows how to find these sessions before a migration, how to clear them under an explicit policy rather than with ad-hoc kills, and how to make the database clean them up on its own. It is the pre-flight step from DDL Lock Management & Timeouts.

Anatomy of a Leaked Transaction Timeline of one request handler. It begins a transaction and selects a row in the first 50 milliseconds, then calls an external payment API for 12 seconds while the transaction stays open, then commits. During those 12 seconds the session is idle in transaction and holds ACCESS SHARE; a migration arriving at 3 seconds waits until the commit. Anatomy of a Leaked Transaction migration wants ACCESS EXCLUSIVE Handler DB work Session state idle in transaction · ACCESS SHARE held External API call payment provider round trip Migration waits for the handler to commit 0 s 2 s 4 s 6 s 8 s 10 s 12 s 14 s database work lock held while idle migration waiting
The session does 50 ms of database work but holds its lock for 12 s, because the transaction stays open across a network call.

Symptom / Error Signatures

In pg_stat_activity the blocker looks like this:

  pid  | usename |        state        |   xact_age   | state_age    | query
-------+---------+---------------------+--------------+--------------+-----------------------------------------
 38112 | app     | idle in transaction | 00:07:41.118 | 00:07:41.090 | SELECT * FROM orders WHERE id = $1
 38520 | app     | idle in transaction | 00:00:12.407 | 00:00:12.380 | SELECT balance FROM accounts WHERE ...

state_age (computed as now() - state_change) is the key column: it tells you how long the session has been idle, and when it is almost equal to xact_age, the transaction did its database work in the first few milliseconds and has been sitting idle ever since. The migration side reports ERROR: canceling statement due to lock timeout with pg_blocking_pids pointing at one of these pids, as shown in finding the blocking session with pg_blocking_pids.

A related state, idle in transaction (aborted), means a statement failed inside the transaction and the client never issued ROLLBACK. It holds its locks just the same.

Root Cause Analysis

PostgreSQL releases table locks only at transaction end. An ACCESS SHARE lock taken by a one-row SELECT inside BEGIN survives until COMMIT or ROLLBACK, however long the client waits before sending it. The database cannot distinguish “the client is thinking” from “the client has forgotten”, so it keeps the lock.

The usual application-side causes are:

Cause Where it comes from Fix in code
Transaction wraps a network call handler begins a transaction, then calls an API or queue commit before external I/O; re-read afterwards if needed
Framework opens a transaction per request ATOMIC_REQUESTS in Django, @Transactional on a controller scope transactions to the unit of work, not the request
Autocommit disabled in a driver psycopg2 default, JDBC setAutoCommit(false) enable autocommit for read-only paths
Exception path skips rollback error handler returns without ROLLBACK use context managers / finally blocks
Interactive sessions an engineer’s psql or IDE left open inside BEGIN role-level idle timeout for humans
Same Handler, Two Transaction Scopes Two panels comparing a request handler. Left, the transaction spans an external API call, so the session is idle in transaction for the whole call and holds its lock. Right, the handler commits before the call and opens a short second transaction afterwards, so no lock is held during the network wait. Same Handler, Two Transaction Scopes Transaction spans the API call BEGIN; SELECT order call payment API (12 s) UPDATE order; COMMIT lock held for 12 s Commit before external I/O BEGIN; SELECT order; COMMIT call payment API (12 s), no transaction open BEGIN; UPDATE … WHERE status = 'pending'; COMMIT locks held for milliseconds
Moving the COMMIT above the network call does not change what the handler does — it changes how long the database has to hold its locks.

Transaction-mode poolers such as PgBouncer do not help here: they return a server connection to the pool only when the transaction ends, so a leaked transaction pins its server connection and its locks just as it would without the pooler.

Immediate Mitigation

1. List candidates immediately before the migration. Filter to sessions idle in transaction longer than your threshold, and show which relations they hold locks on so you only touch the ones that matter.

-- PostgreSQL 10+ · read-only · run as a role with pg_read_all_stats
SELECT a.pid, a.usename, a.application_name, a.client_addr,
       now() - a.xact_start   AS xact_age,
       now() - a.state_change AS idle_for,
       array_agg(DISTINCT l.relation::regclass) FILTER (WHERE l.relation IS NOT NULL) AS locked_relations
FROM pg_stat_activity a
LEFT JOIN pg_locks l ON l.pid = a.pid AND l.granted
WHERE a.state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - a.state_change > interval '30 seconds'
GROUP BY a.pid, a.usename, a.application_name, a.client_addr, a.xact_start, a.state_change
ORDER BY idle_for DESC;

2. Terminate only sessions that match the written policy. A reasonable policy: application roles, idle in transaction for more than 30 seconds, holding a lock on a table the migration will alter. Never terminate replication, backup or superuser sessions from a migration script.

-- PostgreSQL · requires pg_signal_backend · WARNING: rolls back each matched transaction
SELECT pid, pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity a
WHERE a.usename = 'app'
  AND a.state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - a.state_change > interval '30 seconds'
  AND EXISTS (SELECT 1 FROM pg_locks l
              WHERE l.pid = a.pid AND l.granted AND l.relation = 'orders'::regclass);

3. Run the migration within a few seconds, with a lock timeout. A new leak can appear at any moment, so the gap between clearing and migrating should be seconds, and the migration still needs its lock_timeout in case one does.

-- PostgreSQL · migration role · immediately after step 2
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN fulfilment_region text;
COMMIT;
-- ROLLBACK PATH: ALTER TABLE orders DROP COLUMN IF EXISTS fulfilment_region;

4. Log what you terminated. Record pid, application_name, client_addr and last query for each terminated session, and file a bug against the owning service. Each one is a latent incident in the application.

Permanent Fix / Long-Term Pattern

Let the database enforce the policy continuously. idle_in_transaction_session_timeout (PostgreSQL 9.6+) terminates any session that stays idle inside a transaction longer than the configured value. Set it per role so that application roles get a tight limit and humans get a looser one, and leave replication and maintenance roles alone.

-- PostgreSQL 9.6+ · superuser or role owner · applies to new sessions of each role
ALTER ROLE app       SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE analyst   SET idle_in_transaction_session_timeout = '10min';
-- PostgreSQL 14+ also offers idle_session_timeout for sessions idle outside a transaction.
-- ROLLBACK PATH: ALTER ROLE app RESET idle_in_transaction_session_timeout;

Pair the setting with application fixes so the timeout is a safety net rather than a source of errors: when it fires, the client receives FATAL: terminating connection due to idle-in-transaction timeout on its next statement, which is visible in application logs and points directly at the leaking code path. Treat those log lines as bugs. The same discipline removes a whole class of migration failure, and it also protects against the MySQL equivalent described in diagnosing “Waiting for table metadata lock”. Pipelines can add a pre-flight gate that fails the deploy if long idle transactions exist on the target table, in the same way blocking deploys on failed migration dry runs stops unsafe changes before they start.

Pre-Flight Gate for Leaked Transactions Pipeline with stages and gates. Build migration, then a gate checking for idle transactions older than 30 seconds on target tables, then terminate by policy, then a gate checking lock_timeout is set, then apply migration. Failing gates stop the deploy. Pre-Flight Gate for Leaked Transactions Plan migration target tables known idle? idle txn > 30 s Terminate by policy app role only, logged timeout lock_timeout set? Apply DDL retry on 55P03 leak outside policy: page owner reject migration fail
Clearing blockers belongs in the pipeline, next to the lock-timeout check — not in an engineer's shell history at deploy time.

Verification Checklist

Frequently Asked Questions

Will idle_in_transaction_session_timeout break my application? It will surface bugs that were already there. A request handler that holds a transaction open for longer than the timeout gets a connection error on its next statement. Choose a value above your longest legitimate idle gap, fix the handlers that trip it, and then tighten it.

Why not just use pg_cancel_backend on idle sessions? Because there is nothing to cancel. Cancel interrupts a running statement; an idle-in-transaction session is not running one, so cancel has no effect and the transaction and its locks remain. You need pg_terminate_backend or the timeout setting.

Does PgBouncer’s transaction mode prevent idle-in-transaction sessions? No. In transaction mode PgBouncer keeps the server connection assigned to the client for the whole transaction, so a client that stalls inside a transaction pins that connection and its locks. PgBouncer’s idle_transaction_timeout setting can close such clients from the pooler side, which is a useful complement.

Is it safe to terminate an idle transaction that has written rows? Its uncommitted writes are rolled back, which the application will see as a failed request. That is usually acceptable for a transaction idle for minutes, but check pg_stat_activity.backend_xid — a non-null value means the transaction has written — and prefer to contact the owning team for anything that looks like a batch job.

Related Articles