Idle Transactions

Check Frequency

Near Realtime (every 10 seconds)

Default Configuration

Detects transactions that have been open with no activity (the idle in transaction state) for longer than the specified threshold of 1800 seconds and creates an issue with severity "warning". Escalates to "critical" if any transactions are still idle after 3600 seconds. Resolves automatically once the transactions are committed, rolled back, or show activity again.

This check is enabled by default. These parameters can be tuned in the Configure section of the Alerts & Check-Up page.

Guidance

Impact

Transactions left open can hold locks, blocking other queries, and can prevent VACUUM (including autovacuum) from cleaning up dead rows, leading to index or table bloat.

Common Causes

  • Application bugs

    If there is an error (either in the application or in the database) while processing a query, the application must handle that correctly and roll back the transaction (and possibly close the connection). If it fails to do that, the idle transaction may be left open as long as the application keeps running.

    If your application sets application_name on its connections or uses a distinct role, you can find more details by querying pg_stat_activity. Review the code path that triggered the idle transaction (you can also check the last query on the connection in pg_stat_activity), and make sure that any errors are handled appropriately (e.g., with rescue or catch) and roll back the transaction.

  • Interactive sessions

    If someone is accessing the database directly in an interactive session and they manually open a transaction with BEGIN or START TRANSACTION, they must close it with COMMIT or ROLLBACK, or disconnect entirely (which will roll back the transaction).

Solution

For an immediate solution, you can kill a connection with an idle transaction with SELECT pg_terminate_backend('<session_pid>');. Note that this only treats the symptom: unless this is due to a one-off manual session, you may run into this problem again.

You can also change the idle_in_transaction_session_timeout setting to automatically roll back idle transactions older than a specified threshold.


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →