长事务问题定位及处理方法

什么是长事务

事务中，当执行了DDL或者DML操作并且长时间没有提交的事务称为长事务。数据库中存在长事务会有如下影响：

• 耗尽IO资源
• 单一长事务占用大量CPU
• 锁定资源，占用锁从而导致降低并发性
• 会导致表膨胀

长事务相关的监控指标

长事务主要看两个指标：“最长事务存活时长”和“最长未决事务存活时长”，其中后者为适用于两阶段事务的指标。

• 查看方式一：通过云监控服务查看监控指标。
• 查看方式二：通过SQL查看监控指标。
  • 登录实例，在任意库中执行如下SQL，查看最长事务存活时长（oldest_transaction_duration）。

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

  • 登录实例，在任意库中执行如下SQL，查看最长未决事务存活时长（oldest_transaction_duration_2pc）。

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

出现长事务的原因

• 批量操作。
• 有大量锁竞争。

排查方法

1. 查看“最长事务存活时长”和“最长未决事务存活时长”指标，观察出现长事务的情况。详见查看监控指标。
   图1 查看长事务指标
   
2. 登录实例，在任意库中执行如下SQL，通过查询“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. 在任意库中执行如下SQL，通过查询“pg_prepared_xacts”视图来查看两阶段长事务。

   select * from pg_prepared_xacts order by 3 desc;

解决方法

• 对于长事务，业务确认后执行如下SQL终止该事务。

  select pg_cancel_backend($PID);

  若pg_cancel_backend语句无效，再使用如下SQL。

  select pg_terminate_backend($PID);

  

  $PID：长事务进程ID，通过2获取。

• 对于涉及批量操作的业务，建议在业务低峰期执行。
• 建议设置告警规则，关注“最长未决事务存活时长”指标，及时提交未决事务。