PostgreSQL idle in transaction — Long Sessions Holding Locks

Find PostgreSQL sessions stuck in idle in transaction, measure how long they hold locks and snapshots, then enforce transaction timeouts and fix application boundaries.

intermediatedebuggingpostgresqltransactions

Symptoms

Queries that normally finish instantly block behind a session doing nothing
pg_stat_activity shows state idle in transaction with an old state_change
Autovacuum cannot remove dead tuples and table bloat grows
Locks remain granted long after the request that created them returned

Possible causes

Application code opens a transaction and waits on an external API or user input before COMMIT
An ORM session begins a transaction on first query and is returned to the pool without rollback
A connection pool checkout is held while rendering a response or writing a file
A migration or psql session ran BEGIN and the operator walked away

Diagnosis steps

List the oldest idle transactions

psql -U postgres -c "SELECT pid, usename, application_name, client_addr, state, now() - state_change AS idle_for, now() - xact_start AS xact_age, left(query, 80) AS last_query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' ORDER BY xact_start LIMIT 20;"

Interpretation

A large xact_age with a small idle_for means a long-running transaction; a large idle_for means the client stopped sending statements and left the transaction open.

See which locks those sessions hold

psql -U postgres -c "SELECT a.pid, a.state, l.locktype, l.relation::regclass, l.mode, l.granted FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid WHERE a.state LIKE 'idle in transaction%' ORDER BY a.pid;"

Interpretation

AccessExclusiveLock or ShareLock held by an idle session explains blocked DDL and writes. granted = false shows waiters behind it.

Identify the blocking chain for a waiting query

psql -U postgres -c "SELECT pid, pg_blocking_pids(pid) AS blockers, wait_event_type, wait_event, left(query, 60) FROM pg_stat_activity WHERE wait_event_type = 'Lock';"

Interpretation

The blockers array names the idle PIDs to terminate or fix. Empty arrays mean the wait is not a lock wait.

Check whether a timeout is configured

psql -U postgres -c "SHOW idle_in_transaction_session_timeout;" -c "SHOW statement_timeout;"

Interpretation

A value of 0 disables the guard. With a timeout set, the server terminates the session and logs a FATAL message instead of leaving it open.

Root cause

The application began a transaction and never issued COMMIT or ROLLBACK, usually because it waited on a network call or returned a pooled connection while the transaction was still open. PostgreSQL keeps the transaction snapshot and every lock it acquired until the session ends, so an idle client can block writers and prevent vacuum from cleaning up.

Fix

Set idle_in_transaction_session_timeout at the role or database level, wrap external calls outside the transaction boundary, and make every code path commit or roll back before returning the connection. Configure the pool to roll back on release and add a statement_timeout so a single query cannot hold the transaction open indefinitely.

Risk note

The timeout terminates the whole session, which rolls back the transaction and can surprise code that assumed the socket stayed alive. Set it above legitimate batch runtimes and test retry behavior before enabling it in production.

Verify the fix

Re-run the pg_stat_activity query after the fix and confirm no row shows idle in transaction older than the configured timeout. Run a blocked DDL statement and verify it completes without waiting on an idle PID.

An idle client is still a transaction

idle in transaction means PostgreSQL received BEGIN and at least one statement, then stopped hearing from the client without a COMMIT or ROLLBACK. The backend keeps the transaction snapshot and all locks, so a session that looks harmless can block ALTER TABLE, hold row locks, and keep autovacuum from reclaiming dead tuples.

SELECT pid, state, now() - state_change AS idle_for,
       now() - xact_start AS xact_age, left(query, 80)
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start
LIMIT 20;

Find the blocker, not just the victim

Waiting queries point at their blockers directly:

SELECT pid, pg_blocking_pids(pid) AS blockers, wait_event_type, wait_event,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Join pg_locks to see whether the idle session holds an AccessExclusiveLock on the table you are trying to change. If the holder is a forgotten psql session, terminate it with SELECT pg_terminate_backend(pid);; if it is application code, the same pattern will return tomorrow.

Close the boundary

Move HTTP calls, file writes, and user think-time outside the transaction. In pooled environments, configure the pool to issue ROLLBACK when a connection is returned dirty, and set a server-side guard:

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

The timeout is the safety net, not the fix. A service that routinely relies on it is still holding locks for a minute at a time.

Verify

Wait longer than the configured timeout with an open transaction and confirm the backend disappears and the log records the termination. Then run the DDL or update that was previously blocked and check that it acquires its lock immediately. Watch pg_stat_activity over a full traffic cycle; any recurring idle in transaction row now identifies a code path that still returns a connection without ending its transaction.