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 applicable to 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 CPU resources.
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. View long-running transactions by referring to Viewing Monitoring Metrics.
   Figure 1 Viewing long transactions
   
2. Query pg_stat_activity to view information about long-running transactions.

   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. Query pg_prepared_xacts to view two-phase long-running transactions.

   select * from pg_prepared_xacts order by 3 desc;

Solution

1. Terminate the long-running transaction.

   Query the PID of the long-running transaction by referring to 2 and 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. Pay attention to the oldest_transaction_duration_2pc metric and commit pending transactions in a timely manner.