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

指标异常说明（影响）

事务中，当执行了DDL或者DML操作并且长时间没有提交的事务称为长事务。

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

• oldest_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：最长未决事务存活时长

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

当数据库中存在长事务，会有如下影响：

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

建议通过配置最长事务存活时长和最长未决事务存活时长的监控告警来观察数据库中的长事务。

问题排查思路

1. 批量操作
2. 有大量锁竞争

排查方法

1. 通过查看监控指标查看长事务。
   图1 查看长事务
   
2. 通过查询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. 通过查询pg_prepared_xacts查看两阶段长事务。

   select * from pg_prepared_xacts order by 3 desc;

解决方法

1. 对于长事务，业务确认后可终止该事务。

   参考2，查到长事务的pid后，使用如下SQL执行：

   推荐使用如下命令：

   select pg_cancel_backend($PID);

   若pg_cancel_backend语句无效，再使用本语句：

   select pg_terminate_backend($PID);

2. 对于涉及批量操作的业务可以等到业务低峰期进行。
3. 关注oldest_transaction_duration_2pc指标，并及时提交未决事务。