Updated on 2025-06-24 GMT+08:00

Troubleshooting Long-Running Transactions

What Is a Long-Running Transaction?

A long-running transaction refers to a transaction in which DDL or DML operations are being performed and are not committed for a long time. Long-running transactions may:

  • Exhaust I/O resources.
  • Occupy a large number of CPUs.
  • Lock resources and reduce concurrency.
  • Cause table bloats.

Metrics Related to Long-Running Transactions

There are two long-running transaction metrics: oldest_transaction_duration and oldest_transaction_duration_2pc. The latter is used for two-phase transactions. You can check the metrics using either of the following methods:

  • Method 1: Go to the Cloud Eye console.
  • Method 2: Use SQL statements.
    • To check the longest transaction lifetime (oldest_transaction_duration), log in to the instance and run the following SQL statement in any database:
      select EXTRACT (EPOCH FROM max(now()-xact_start)) from pg_stat_activity where backend_type = 'client backend' and state <> 'idle';
    • To check the longest pending transaction lifetime (oldest_transaction_duration_2pc), log in to the instance and run the following SQL statement in any database:
      select coalesce(EXTRACT (EPOCH FROM now() - min(prepared)), 0) from pg_prepared_xact();

Cause Analysis

  • There are batch operations.
  • There are a lot of lock contentions.

Troubleshooting

  1. Check oldest_transaction_duration and oldest_transaction_duration_2pc for long-running transactions. For details, see Viewing Monitoring Metrics.
    Figure 1 Checking long-running transaction metrics
  2. Log in to the instance and run the following SQL statement in any database to obtain transaction activities from pg_stat_activity:
    select (now() - xact_start) trans_time, pid, datname, usename, client_addr, wait_event, state, substring(query, 1,50) from pg_stat_activity where state <> 'idle' and xact_start is not null and backend_type = 'client backend' and pid <> pg_backend_pid() order by 1 desc limit 3;
  3. Run the following SQL statement in any database to query two-phase long-running transactions from pg_prepared_xacts:
    select * from pg_prepared_xacts order by 3 desc;

Solution

  • Terminate long-running transactions by running the following SQL statement:
    select pg_cancel_backend($PID);

    If the pg_cancel_backend statement is invalid, run the following SQL statement:

    select pg_terminate_backend($PID);

    $PID: long transaction process ID, which can be obtained from 2.

  • Perform batch operations during off-peak hours.
  • Set alarm rules for oldest_transaction_duration_2pc and commit pending transactions in a timely manner.