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:
1 |
SELECT state, query, query_id FROM pgxc_stat_activity;
|
The result shows that the statement is in the idle in transaction state.
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
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$$;
|
1 |
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.
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:
1 |
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.
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:
1 2 3 4 5 6 7 8 |
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.
Database Usage FAQs
- How Do I Change Distribution Columns?
- How Do I View and Set the Database Character Encoding?
- What Do I Do If Date Type Is Automatically Converted to the Timestamp Type During Table Creation?
- Do I Need to Run VACUUM FULL and ANALYZE on Common Tables Periodically?
- Do I Need to Set a Distribution Key After Setting a Primary Key?
- Is GaussDB(DWS) Compatible with PostgreSQL Stored Procedures?
- What Are Partitioned Tables, Partitions, and Partition Keys?
- How Can I Export the Table Structure?
- How Can I Delete Table Data Efficiently?
- How Do I View Foreign Table Information?
- If No Distribution Column Is Specified, How Will Data Be Stored?
- How Do I Replace the Null Result with 0?
- How Do I Check Whether a Table Is Row-Stored or Column-Stored?
- How Do I Query the Information About GaussDB(DWS) Column-Store Tables?
- Why Sometimes the GaussDB(DWS) Query Indexes Become Invalid?
- How Do I Use a User-Defined Function to Rewrite the CRC32() Function?
- What Are the Schemas Starting with pg_toast_temp* or pg_temp*?
- Solutions to Inconsistent GaussDB(DWS) Query Results
- Which System Catalogs That the VACUUM FULL Operation Cannot Be Performed on?
- In Which Scenarios Would a Statement Be "idle in transaction"?
- How Does GaussDB(DWS) Implement Row-to-Column and Column-to-Row Conversion?
- What Are the Differences Between Unique Constraints and Unique Indexes?
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.
more