更新时间:2024-06-11 GMT+08:00

语句处于idle in transaction状态常见场景

在使用PGXC_STAT_ACTIVITY视图查询用户SQL相关信息时,查询结果中的state字段有时会显示“idle in transaction”。idle in transaction具体含义为:后端在事务中,但事务中没有语句在执行。该状态表示该条语句已经执行完成,因此query_id为0,但是本事务还未提交或回滚。此状态下的语句已经执行完成,不占用CPU和IO等资源,会占用连接数,并发数等连接资源。

若业务中出现语句处于idle in transaction状态,可参考如下常见场景及对应的解决方法来处理:

场景一:事务开启后没有提交,语句处于idle in transaction

手动BEGIN/START TRANSACTION开启事务,执行某语句后,不执行COMMIT/ROLLBACK,此时执行如下命令查看视图PGXC_STAT_ACTIVITY:

1
SELECT state, query, query_id FROM pgxc_stat_activity;

查看结果显示:该语句状态为idle in transaction。

解决方法:这种场景下需要手动对开启的事务执行COMMIT/ROLLBACK即可。

场景二:存储过程中有DDL语句,该存储过程结束前,其他节点上DDL语句执行完后的状态是idle in transaction

先创建存储过程:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE OR REPLACE FUNCTION public.test_sleep()
RETURNS void
LANGUAGE plpgsql
AS $$

BEGIN
    truncate t1;
    truncate t2;
    EXECUTE IMMEDIATE 'select pg_sleep(6)';
	RETURN;
END$$;
再执行如下命令查看PGXC_STAT_ACTIVITY视图:
1
SELECT coorname,pid,query_id,state,query,usename FROM pgxc_stat_activity WHERE usename='jack';

查看结果显示:truncate t2处于idle in transaction状态,coorname为coordinator2。说明cn2上该语句已经执行完成,该存储过程在执行下一条语句。

解决方法:此类场景是由于存储过程执行慢导致,等存储过程执行完成即可,也可考虑优化存储过程中执行时间较长的语句。

场景三:大量SAVEPOINT/RELEASE语句处于idle in transaction(8.1.0之前集群版本)

执行如下命令查看PGXC_STAT_ACTIVITY视图:

1
SELECT coorname,pid,query_id,state,query,usename FROM pgxc_stat_activity WHERE usename='jack';

结果显示SAVEPOINT/RELEASE语句处于idle in transaction。

解决方法:

SAVEPOINT和RELEASE语句是带EXCEPTION的存储过程执行时系统自动生成的(8.1.0之后的集群版本不再向CN下发SAVEPOINT),GaussDB(DWS)带EXCEPTION的存储过程在实现上基于子事务实现,简单对应关系如下:

1
2
3
4
5
6
7
8
begin
    (Savepoint s1)
    DDL/DML
exception
    (Rollback to s1)
    (Release s1)
     
end

存储过程启动时如果有EXCEPTION,则会启动一个子事务,如果执行过程中出现EXCEPTION,则会回滚当前事务并进行异常的处理;如果没有出现EXCEPTION则会直接提交前面的子事务。

当此类存储过程较多且有嵌套时容易出现,与场景二类似,等整个存储过程执行完即可。如果RELEASE较多,说明存储过程触发了多个EXCEPTION,可分析存储过程逻辑是否合理。