In Which Scenarios Would a Statement Be "idle in transaction"?

When user SQL information is queried in the PGXC_STAT_ACTIVITY view, the state column in the query result sometimes displays idle in transaction. idle in transaction indicates that the backend is in a transaction, but no statement is being executed. This status indicates that a statement has been executed. Therefore, the value of query_id is 0, but the transaction has not been committed or rolled back. Statements in this state have been executed and do not occupy CPU and I/O resources, but they occupy connection resources such as connections and concurrent connections.

If a statement is in the idle in transaction state, rectify the fault by referring to the following common scenarios and solutions:

Scenario 1: A Transaction Is Started But Not Committed, and the Statement Is in the "idle in transaction" State

BEGIN/START TRANSACTION is manually executed to start a transaction. After statements are executed, COMMIT/ROLLBACK is not executed. View the PGXC_STAT_ACTIVITY:

SELECT state, query, query_id FROM pgxc_stat_activity;

The result shows that the statement is in the idle in transaction state.

image1

Solution: Manually execute COMMIT/ROLLBACK on the started transaction.

Scenario 2: After a DDL Statement in a Stored Procedure Is Executed, Other Nodes of the Stored Procedure Is In the "idle in transaction" State

Create a stored procedure:

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$$;

View the PGXC_STAT_ACTIVITY view:

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

The result shows that truncate t2 is in the idle in transaction state and coorname is coordinator2. This indicates that the statement has been executed on cn2 and the stored procedure is executing the next statement.

image2

Solution: This problem is caused by slow execution of the stored procedure. Wait until the execution of the stored procedure is complete. You can also optimize the statements that are executed slowly in the stored procedure.

Scenario 3: A Large Number of SAVEPOINT/RELEASE Statements Are in the "idle in transaction" State (Cluster Versions Earlier Than 8.1.0)

View the PGXC_STAT_ACTIVITY view:

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

The result shows that the SAVEPOINT/RELEASE statement is in the idle in transaction state.

image3

Solution:

SAVEPOINT and RELEASE statements are automatically generated by the system when a stored procedure with EXCEPTION is executed. In versions later than 8.1.0, SAVEPOINT is not delivered to CNs. GaussDB (DWS)stored procedures with EXCEPTION are implemented based on subtransactions, the mapping is as follows:

begin
    (Savepoint s1)
    DDL/DML
exception
    (Rollback to s1)
    (Release s1)
...
end

If there is EXCEPTION in a stored procedure when it is started, a subtransaction will be started. If there is and exception during the execution, the current transaction is rolled back and the exception is handled; if there is no exception, the subtransaction is committed.

This problem may occur when there are many such stored procedures and the stored procedures are nested. Similar to scenario 2, you only have to wait after the entire stored procedure is executed. If there are a large number of RELEAS messages, the stored procedure triggers multiple exceptions. In this case, you have to analyze whether the logic of the stored procedure is proper.