PostgreSQL problems

Idle in Transaction: Find the Session at the Root of a PostgreSQL Lock Queue

22 November 2026

A session that is idle in a transaction does nothing and costs a lot: it keeps every lock it took, and it can keep vacuum from removing dead rows. When queries pile up on PostgreSQL, the session at the root of the lock queue is very often one of these. Two queries find it.

What idle in transaction means

The application sent BEGIN, ran a statement, and then sent nothing: no COMMIT, no ROLLBACK. pg_stat_activity shows the session with state = 'idle in transaction', and query holds the last statement it ran, not one that runs now.

SELECT pid, usename, application_name, client_addr,
       now() - xact_start   AS transaction_open,
       now() - state_change AS idle_for,
       left(query, 60)      AS last_query
FROM   pg_stat_activity
WHERE  state LIKE 'idle in transaction%'
ORDER  BY xact_start;

Seconds are normal: that is an application between two statements. Minutes or hours are a connection that was forgotten, a debugger at a breakpoint, or code that calls a slow service in the middle of a transaction.

Who waits behind whom

SELECT a.pid, a.usename, a.application_name,
       a.wait_event_type, a.wait_event,
       pg_blocking_pids(a.pid)   AS blocked_by,
       now() - a.query_start     AS waiting,
       left(a.query, 60)         AS query
FROM   pg_stat_activity a
WHERE  cardinality(pg_blocking_pids(a.pid)) > 0
ORDER  BY waiting DESC;

The list shows the waiters. The session to find blocks others and waits for nobody:

WITH w AS (
  SELECT pid, unnest(pg_blocking_pids(pid)) AS blocker
  FROM   pg_stat_activity
  WHERE  wait_event_type = 'Lock'
)
SELECT a.pid, a.usename, a.application_name, a.state,
       now() - a.xact_start   AS transaction_open,
       now() - a.state_change AS in_this_state,
       count(*)               AS direct_waiters,
       left(a.query, 60)      AS last_query
FROM   w
JOIN   pg_stat_activity a ON a.pid = w.blocker
WHERE  w.blocker NOT IN (SELECT pid FROM w)
GROUP  BY a.pid, a.usename, a.application_name, a.state,
          a.xact_start, a.state_change, a.query
ORDER  BY direct_waiters DESC;

A blocker with pid 0 in the first list is a prepared transaction, which has no session. pg_prepared_xacts lists them.

The lock queue behind an ALTER TABLE

The worst pile-ups have three layers. A session idle in a transaction once read a table, so it holds the weakest lock on it. An ALTER TABLE asks for the strongest lock and waits behind it. From that moment every new query on the table, plain SELECT included, waits behind the ALTER TABLE, because PostgreSQL grants locks in the order they were asked for.

The application sees a table that stopped answering. The session to end is not the ALTER TABLE everybody waits behind, it is the idle one at the top.

The fix, in order

  1. End the root session. SELECT pg_terminate_backend(12345); rolls its transaction back and frees its locks. pg_cancel_backend does nothing here: it cancels a running statement, and an idle session runs none.
  2. Finish a prepared transaction. ROLLBACK PREPARED 'gid'; or COMMIT PREPARED 'gid'; from any session of the same database.
  3. Bound idle transactions. ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min'; and a reload. PostgreSQL then ends such sessions by itself. Set it per role when one application needs longer.
  4. Bound the wait of schema changes. SET lock_timeout = '5s'; before every ALTER TABLE. A change that cannot get its lock fails fast and is tried again, and no queue forms behind it.
  5. Fix the application. A transaction should not stay open across a call to another service or a wait for a user. With a pooler in transaction mode, a connection that is returned with an open transaction is the bug to look for.

What it costs beside the locks

An open transaction that wrote something, or that runs at REPEATABLE READ, also pins the xmin horizon: vacuum cannot remove row versions that died after it began, in any table of its database. Tables bloat, index scans slow down, and the transaction ID age stops falling, which is the road to wraparound. One forgotten session over a weekend is enough to see it on Monday.

See it coming

Radar Engine, the detection layer of Scops, reads the lock chains of every instance, with no agent on the server, and tells a real pile-up from the short waits every busy system has:

DetectorWhen it fires
Blocking pile-upWarning: at least 10 lock waiters behind one head, the longest waiting 3 minutes or more. Warning too: a head that is idle in a transaction, or a prepared transaction, holding 3 or more waiters for 5 minutes. Critical: at least 20 lock waiters that are at least half of the active sessions.
Xmin horizon heldVacuum cannot remove dead rows: the finding names the session, the prepared transaction, the replication slot or the standby that holds the oldest xmin.
Connection limitWatch from 80% of the usable connections, Warning from 90% or when the limit is minutes away, Critical from 97%.

When the waiters stand behind a session that itself waits, the finding names the session at the top of the chain and the statement that acts as the gate. Live Activity shows each chain as a tree, with how long a root has been idle in its transaction, now or at any moment of the past.

Questions

Why does an idle in transaction session block other queries?
Its transaction is still open, so every lock it took is still held. Sessions that need a conflicting lock wait until it commits, rolls back or is terminated.

How do I kill an idle in transaction session?
SELECT pg_terminate_backend(pid). pg_cancel_backend does not help, because it cancels a running statement and an idle session runs none.

What is a safe idle_in_transaction_session_timeout?
A few minutes suits most applications: longer than any pause the code makes inside a transaction, far shorter than the time it takes a forgotten session to cause harm. Set a longer value on the roles that need it.

Why does a SELECT wait behind an ALTER TABLE that has not started?
Because locks are granted in the order they were requested. The ALTER TABLE waits for its exclusive lock, and every later request on the table queues behind it, even those that would not conflict with the session that holds the table now.

Related problems

Sources