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

什么是长事务

事务中，当执行了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 查看长事务指标

登录实例，在任意库中执行如下SQL，通过查询“pg_stat_activity”视图来查看事务的活动信息。

select (now() - xact_start) trans_time, pid, datname, usename, client_addr, wait_event, state, substring(query, 1,500) 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;

在任意库中执行如下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获取。
如果需要终止某个明确的长事务会话，可通过以下步骤实现。
下面步骤以终止运行中的语句ALTER TABLE distributors DROP COLUMN address RESTRICT;为例。
1. 通过query条件筛选出“pg_stat_activity”中需要终止的唯一会话（不包含其他会话信息）。
```
select * from pg_stat_activity where query ilike 'alter table distributors%'; 
```
2. 执行如下命令终止该会话。
```
select pg_terminate_backend(pid) from pg_stat_activity where query ilike 'alter table distributors%'; 
```
  如果1中结果有n条会话语句，则这n条会话会全部终止。
对于涉及批量操作的业务，建议在业务低峰期执行。
建议设置告警规则，关注“最长未决事务存活时长”指标，及时提交未决事务。