Autonomous Transaction Management View and Termination

Availability

This feature is available since MogDB 5.0.6.

Introduction

This feature supports viewing the number of active autonomous transactions and related details, as well as terminating autonomous transactions without stopping the database.

Benefits

Enhances the observability of autonomous transactions, facilitating the observation of system autonomous transaction session details and their relationship with main transaction sessions, making it easier for users to control the number of autonomous transactions running in the system.

Description

In the current design of MogDB, a main transaction initiates the creation of a session connection process after receiving an autonomous transaction command for the first time. This is an autonomous transaction session independent of the main transaction. Even if the main transaction executes multiple autonomous transaction commands later, it reuses the same session connection. When the main transaction commits or rolls back, the autonomous transaction session connection is closed (destroyed).

This feature supports viewing the number of autonomous transaction session connections and specific details through the pg_running_xacts view. If you want to query the specific SQL execution content of autonomous transactions, you can construct a new view based on this view and other existing views (such as pg_stat_activity).

When users query an excessive number of autonomous transaction connections through the pg_running_xacts view, they can use the SELECT pg_terminate_backend(pid) command to forcibly terminate active autonomous transactions (the pid of the autonomous transaction is obtained according to the pg_running_xacts view). At this point, the number of autonomous transaction session connections in the system will decrease.

View Description

The pg_running_xacts view adds the following three columns:

Example

-- Create table
Create table test_auto_dataa (a int);

-- Create autonomous transaction stored procedure
create or replace procedure taest_auto_pp()
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into test_auto_dataa select generate_series(1,700000);
commit;
end;
/

Begin                                     -- Main transaction begins
taest_auto_pp();                         -- Execute autonomous transaction
insert into test_auto_dataa select generate_series(1,7000000);
end
/

-- During the execution of the autonomous transaction (the autonomous transaction can be confirmed to be running using the pg_stat_activity view), query the pg_running_xacts view to see the session connections of the autonomous transaction. The sessionid corresponding to is_autonomous_session is 't', and parent_sessionid is not 0. Before the main transaction ends, the session connection of the autonomous transaction still exists. Note: In thread pool mode, after the autonomous transaction ends, its sessionid and pid are reset to zero. After the main transaction is completed (committed or rolled back), the session connection of the autonomous transaction disappears.
select * from pg_running_xacts;
select * from pg_get_running_xacts();

-- During the execution of the autonomous transaction, query the thread ID of the autonomous transaction from the above view, record the session ID of the main session, and then use pg_terminate_backend(pid) to terminate the autonomous transaction.
select pg_terminate_backend(pid);

Related Pages

pg_running_xacts, pg_get_running_xacts(), PG_STAT_ACTIVITY, pg_terminate_backend(pid int), Autonomous Transaction