Finding and Debugging Long Running Transactions

1. Why long transactions are a silent problem

A long running transaction rarely causes a single, obviously slow query. Instead it holds a database snapshot open in the background that prevents other processes from cleaning up outdated row versions. The result shows up with a delay: bloated tables, a growing wraparound counter, blocked DDL statements, or locks that seem to persist for minutes without any apparent reason. Anyone who only looks at the slow query log sees none of this, because the actual transaction often does not contain a single expensive query, it simply stays open.

The case of idle in transaction is particularly tricky: the application started a transaction, ran one or more queries, and is now waiting, without COMMIT or ROLLBACK, for a further action, for example an external API response or user input. From the database's perspective this session is completely inactive, but from a transaction perspective it still holds its snapshot and its locks. This combination of apparent inactivity and actual blocking makes long running transactions one of the hardest problems to diagnose in database operations.

Another reason this pattern stays undetected for so long lies in the separation of responsibilities between the development team and database operations. The development team only sees in the application log that a request was answered successfully, without knowing how long the underlying database transaction actually stayed open. Operations, in turn, usually see only aggregated metrics like CPU utilization or connection count in standard dashboards, not the transaction age of individual sessions. Only a targeted query against the system catalogs shown in this article closes that gap between the two perspectives.

2. Detecting idle in transaction

The idle in transaction state is directly visible in PostgreSQL through the state column in pg_stat_activity, with the exact value idle in transaction. The crucial additional value is xact_start, the time the transaction began, not to be confused with query_start, the start time of the last query executed within that transaction. The difference between now and xact_start shows the actual transaction age, regardless of whether a query is currently running or the session is waiting.

In MySQL the information schema table INNODB_TRX provides the column trx_started for the same purpose, complemented by trx_state, which distinguishes between actively running and waiting transactions. Important for both systems: a short transaction duration of a few milliseconds is normal and harmless, only above a project specific threshold, often somewhere between five and thirty seconds for interactive applications, does an open transaction become a real risk for vacuum and locking.

-- PostgreSQL: find sessions idle in transaction longer than 30 seconds
SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - state_change AS idle_age,
    left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '30 seconds'
ORDER BY xact_start;

-- MySQL: find long-running open transactions via INNODB_TRX
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_open,
    trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY trx_started;

3. Using system catalogs and session views for diagnosis

Beyond mere detection, session views provide additional context that is essential for root cause analysis. pg_stat_activity shows, besides transaction age, client_addr, the IP address of the connected client, as well as application_name, provided the client sets that value correctly. These two fields are often the fastest way to identify the responsible application component, without first having to search through log files.

It is also worth joining pg_stat_activity against pg_locks to see exactly which locks a long open transaction is holding. A transaction that only executed a SELECT usually holds harmless shared locks, while a transaction preceded by an UPDATE or DELETE holds exclusive row locks that block other write access to the same rows. This distinction determines whether a long transaction merely causes MVCC bloat or actively blocks other sessions.

-- Join pg_stat_activity with pg_locks to see what a long transaction is holding
SELECT
    a.pid,
    a.application_name,
    a.client_addr,
    now() - a.xact_start AS transaction_age,
    l.mode,
    l.locktype,
    l.relation::regclass AS locked_table
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE a.state = 'idle in transaction'
  AND l.locktype != 'virtualxid'
ORDER BY a.xact_start;

4. Causes: forgotten commits, application bugs, network issues

The most common cause of long running transactions is an application bug where a transaction begins before an external call, for example an HTTP request to a payment gateway, and the COMMIT only happens after that call returns. If the external service responds slowly or not at all, the transaction stays open for the entire duration of that wait, even though the database itself has nothing to do during that time. This pattern occurs especially often in combination with ORM frameworks that define transaction boundaries implicitly around request scopes instead of setting them explicitly in application code.

A second common cause is dropped connections, where the client loses the TCP connection without the database noticing immediately. Without tcp_keepalives or a configured statement timeout, such a transaction can in theory stay open indefinitely, because the database is waiting for a response from a client that no longer exists. A third cause involves connection pools, where a connection with an open transaction accidentally gets returned to the pool and later reused for a completely different purpose without a ROLLBACK happening first.

5. Effects on vacuum, MVCC, and locks

In MVCC based databases like PostgreSQL, the oldest still open transaction defines the boundary up to which outdated row versions may be cleaned up. As long as a transaction stays open, autovacuum cannot remove row versions that might theoretically still be visible to it, even if those versions have long become irrelevant to every other transaction. The result is table and index bloat, which unnecessarily grows disk size and slows down subsequent queries, because more dead row versions have to be scanned through.

Beyond the bloat problem, a very long open transaction can, in extreme cases, contribute to transaction ID wraparound, a state in which PostgreSQL has to switch into a read only mode for data integrity reasons. On the locking side, a transaction with open write locks blocks every other transaction trying to modify the same rows, which manifests as growing wait times on seemingly unrelated requests. This chain reaction, from a single forgotten transaction to noticeable latency across the whole system, is the core reason why long transactions need to be monitored proactively.

6. Configuring timeouts as a protective mechanism

The most effective preventive protection against long running transactions is a server side configured timeout that kicks in independent of application code. PostgreSQL provides idle_in_transaction_session_timeout for this, which automatically terminates a session once it has stayed in the idle in transaction state longer than the configured duration. In addition, statement_timeout limits the runtime of individual queries, but does not protect against the idle in transaction pattern itself, because no query is actively running between statements.

In MySQL, innodb_rollback_on_timeout combined with an application side timeout plays a similar role, though MySQL offers no direct equivalent to idle_in_transaction_session_timeout, and control lies more with the client or connection pool. The practical recommendation: these timeouts should be set as tight as possible without interfering with legitimate but slow batch operations, typically as a global default with an explicit exception for known batch jobs.

-- PostgreSQL: kill sessions idle in transaction for more than 60 seconds
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

-- Per-session override for a known long-running batch job
SET idle_in_transaction_session_timeout = '30min';
BEGIN;
-- ... long batch operation with a legitimate reason to stay open
COMMIT;

7. Building monitoring and alerting for long transactions

Timeouts alone only fix acute cases, they do not prevent the same pattern from recurring in application code. A recurring monitoring job that runs the query against pg_stat_activity shown in section two once a minute and alerts once a threshold is exceeded makes the problem visible before the timeout even fires. It is important to enrich the alert with application_name and client_addr, so the responsible team can be identified directly, instead of receiving only a generic warning.

It also pays off to keep a permanent time series of the maximum transaction duration over time, for example through a monitoring system like Prometheus with a matching exporter. That way you can detect whether a specific deployment introduces new long transactions, and the correlation between deployment time and the appearance of new idle in transaction sessions gives a direct clue to the responsible code change.

An often underestimated side effect of this monitoring is establishing a historical reference value. Without a documented baseline of how long transactions for a given endpoint typically stay open, a gradual degradation over weeks is hard to distinguish from normal fluctuation. A weekly report with the ninetieth and ninety ninth percentile of transaction duration per endpoint makes such gradual regressions visible long before they escalate into an acute incident.

8. The debugging workflow step by step

The practical workflow starts with the query from section two, to identify all currently open, long running transactions. For each session found, the join against pg_locks from section three is then run, to determine whether the transaction actively blocks other sessions or merely passively delays vacuum. In parallel, application_name and client_addr are used to identify the responsible application component and search the application log for the matching request ID.

In the application code itself, it then gets checked whether the transaction boundary wraps an external call, as described in section four, and whether that boundary can be narrowed so the external call happens outside the transaction. After the fix, verification happens through renewed monitoring: the maximum observed transaction age for the affected endpoint should drop noticeably after the change, not just at a single point in time, but over a representative period of several days.

In teams with several developers working in parallel, it pays off to establish this workflow as a fixed part of code review, instead of only applying it reactively after an incident. A simple rule, every change to a transaction boundary must be explicitly justified in the pull request, especially when an external call remains inside the boundary, prevents many regressions before they even get merged. This preventive measure is considerably cheaper than diagnosing a problem after it has already reached production.

9. Comparing timeout mechanisms across databases

The concrete configuration of timeouts against long transactions differs substantially between the common database systems. The following table compares the key mechanisms.

Notably, only PostgreSQL ships with a dedicated, server side idle in transaction timeout. In MySQL and, to some extent, SQL Server, responsibility lies more with the client or connection pool, which in practice means application side timeouts and connection pool configuration matter even more there to reach the same level of protection.

10. Summary

Long running transactions, especially in the idle in transaction state, are one of the hardest problems to see in database operations, because they rarely produce a single slow query, instead they block vacuum in the background, cause bloat, and slow down other sessions through locks. pg_stat_activity with the xact_start column provides the most reliable detection, complemented by a join against pg_locks to confirm whether a transaction actively blocks.

The most common cause lies in transaction boundaries that wrap external, slow calls instead of placing them outside the transaction. idle_in_transaction_session_timeout in PostgreSQL provides an effective server side safeguard, but it does not replace fixing the actual root cause in application code. Continuous monitoring ensures new regressions of this pattern get noticed early, instead of quietly accumulating into bloat and wait times overnight.

11. FAQ: Debugging Long Running Transactions