Troubleshooting Long-Running Transactions

Description

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.

There are two long-running transaction metrics: oldest_transaction_duration and oldest_transaction_duration_2pc. The latter is used for two-phase transactions.

• oldest_transaction_duration: oldest active transaction duration

  select EXTRACT (EPOCH FROM max(now()-xact_start)) from pg_stat_activity where backend_type = 'client backend' and state <> 'idle';

• oldest_transaction_duration_2pc: oldest two-phase commit transaction duration

  select coalesce(EXTRACT (EPOCH FROM now() - min(prepared)), 0) from pg_prepared_xact();

Long-running transactions may:

1. Exhaust I/O resources.
2. Occupy a large number of CPUs.
3. Lock resources and reduce concurrency.
4. Cause table bloats.

You are advised to configure monitoring alarms for the two long-running transaction metrics to observe the long-running transactions in the instance.

Cause Analysis

1. There are batch operations.
2. There are a lot of lock contentions.

Troubleshooting

1. Find long-running transactions by referring to Viewing Monitoring Metrics.
   Figure 1 Finding long-running transactions
   
2. Obtain long-running transaction details by querying 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. Find two-phase long-running transactions by querying pg_prepared_xacts.

   select * from pg_prepared_xacts order by 3 desc;

Solution

1. Terminate long-running transactions if needed.

   Obtain the long-running transaction PIDs by referring to 2. Then run the following SQL statement:

   Recommended:

   select pg_cancel_backend($PID);

   Alternative:

   select pg_terminate_backend($PID);

2. Perform batch operations during off-peak hours.
3. Observe the oldest_transaction_duration_2pc metric and commit pending transactions in a timely manner.