Tuning Aurora PostgreSQL with Amazon DevOps Guru proactive insights - Amazon Aurora
Database has long running idle in transaction connection

Tuning Aurora PostgreSQL with Amazon DevOps Guru proactive insights

DevOps Guru proactive insights detects conditions on your Aurora PostgreSQL DB clusters that can cause problems, and lets you know about them before they occur. Proactive insights can alert you to a long running idle in transaction connection. For more information about troubleshooting long running idle in transaction connections, see Database has long running idle in transaction connection

DevOps Guru can do the following:

  • Prevent many common database issues by cross-checking your database configuration against common recommended settings.

  • Alert you to critical issues in your fleet that, if left unchecked, can lead to larger problems later.

  • Alert you to newly discovered problems.

Every proactive insight contains an analysis of the cause of the problem and recommendations for corrective actions.

For more information about Amazon DevOps Guru for Amazon RDS, see Analyzing Aurora performance anomalies with Amazon DevOps Guru for Amazon RDS.

Database has long running idle in transaction connection

A connection to the database has been in the idle in transaction state for more than 1800 seconds.

Supported engine versions

This insight information is supported for all versions of Aurora PostgreSQL.

Context

A transaction in the idle in transaction state can hold locks that block other queries. It can also prevent VACUUM (including autovacuum) from cleaning up dead rows, leading to index or table bloat or transaction ID wraparound.

Likely causes for this issue

A transaction initiated in an interactive session with BEGIN or START TRANSACTION hasn't ended by using a COMMIT, ROLLBACK, or END command. This causes the transaction to move to idle in transaction state.

Actions

You can find idle transactions by querying pg_stat_activity.

In your SQL client, run the following query to list all connections in idle in transaction state and to order them by duration:

SELECT now() - state_change as idle_in_transaction_duration, now() - xact_start as xact_duration,* 
FROM  pg_stat_activity 
WHERE state  = 'idle in transaction'
AND   xact_start is not null
ORDER BY 1 DESC;

We recommend different actions depending on the causes of your insight.

End transaction

When you initiate a transaction in an interactive session with BEGIN or START TRANSACTION, it moves to idle in transaction state. It remains in this state until you end the transaction by issuing a COMMIT, ROLLBACK, END command or disconnect the connection completely to roll back the transaction.

Terminate the connection

Terminate the connection with an idle transaction using the following query:

SELECT pg_terminate_backend(pid);

pid is the process ID of the connection.

Configure the idle_in_transaction_session_timeout parameter

Configure the idle_in_transaction_session_timeout parameter in the parameter group. The advantage of configuring this parameter is that it does not require a manual intervention to terminate the long idle in transaction. For more information on this parameter, see the PostgreSQL documentation.

The following message will be reported in the PostgreSQL log file after the connection is terminated, when a transaction is in the idle_in_transaction state for longer than the specified time.

FATAL: terminating connection due to idle in transaction timeout
Check the AUTOCOMMIT status

AUTOCOMMIT is turned on by default. But if it is accidentally turned off in the client ensure that you turn it back on.

  • In your psql client, run the following command:

    postgres=> \set AUTOCOMMIT on

  • In pgadmin, turn it on by choosing the AUTOCOMMIT option from the down arrow.

    In pgadmin, choose AUTOCOMMIT to turn it on.
Check the transaction logic in your application code

Investigate your application logic for possible problems. Consider the following actions:

  • Check if the JDBC auto commit is set true in your application. Also, consider using explicit COMMIT commands in your code.

  • Check your error handling logic to see whether it closes a transaction after errors.

  • Check whether your application is taking long to process the rows returned by a query while the transaction is open. If so, consider coding the application to close the transaction before processing the rows.

  • Check whether a transaction contains many long-running operations. If so, divide a single transaction into multiple transactions.

Relevant metrics

The following PI metrics are related to this insight:

  • idle_in_transaction_count - Number of sessions in idle in transaction state.

  • idle_in_transaction_max_time - The duration of the longest running transaction in the idle in transaction state.