Long-running / open transaction

A long-running or open transaction is accumulating locks, undo and bloat. We find the offending transaction, determine its type (idle in transaction, long batch, impact) and resolve it safely.

Which is the oldest open transaction?

List open transactions by start time. The oldest one is usually the source of the problem. Check its state: is it actively running, or is it 'idle in transaction' waiting for a commit?

SELECT s.sid, s.serial#, s.username, s.status, t.start_time, t.used_ublk*8/1024 AS undo_mb
FROM v$transaction t JOIN v$session s ON s.taddr=t.addr
ORDER BY t.start_time;

• idle in transaction (commit forgotten, sitting idle)
• Active but very long-running batch/DML
• Accumulating locks/bloat/undo while the transaction continues

Why does idle in transaction occur?

The session opened a transaction but sits idle without running a query; that is, the commit/rollback was forgotten. This holds locks and undo unnecessarily. Is an application fix enough, or does it need to be terminated urgently?

SELECT pid, now()-state_change AS idle_age, left(query,80) AS last_query
FROM pg_stat_activity WHERE state='idle in transaction' ORDER BY state_change;

• The application/connection pool does not commit (permanent fix)
• I need to terminate it right now

What is the active long transaction doing?

The session is actually running (active). Is it a single huge DML/report, or a blocker that is making other sessions wait and must be cut off urgently?

SELECT s.sid, s.serial#, sq.sql_text, s.last_call_et AS running_secs
FROM v$session s JOIN v$sql sq ON sq.sql_id=s.sql_id
WHERE s.status='ACTIVE' AND s.type='USER' ORDER BY s.last_call_et DESC;

• A single huge DML/report; it needs to be split up
• It is making others wait; terminate it urgently

What is the long transaction accumulating?

Determine the side effect of the long transaction: is it mainly a lock backlog making other sessions wait, or bloat from holding old row versions and blocking cleanup (VACUUM/undo)?

SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;
SELECT max(age(backend_xmin)) AS max_xmin_age FROM pg_stat_activity WHERE backend_xmin IS NOT NULL;

• A lock backlog is making other sessions wait
• Old version buildup is blocking cleanup (undo/bloat)

Root cause: the application does not commit (idle in transaction)

The connection pool or application opens a transaction and then keeps the connection idle without committing/rolling back. This holds locks and undo unnecessarily.

Solution: terminate the offending session safely

If a blocking/long session is making others wait, it must be terminated in a controlled way. Termination triggers a rollback, which may need space/time; be careful.

Root cause: a single huge DML/report

A DML that processes millions of rows in a single transaction, or a very long report, accumulates locks and undo/bloat for its entire duration.

Root cause: lock backlog queue

Because of the locks it holds, the long transaction has created a wait queue behind it; many sessions are waiting for the same resource.

Root cause: old version buildup (undo/bloat)

The long-running transaction prevents cleanup of the old row versions that predate it. In Postgres this shows up as table/index bloat, in Oracle as undo pressure.