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.