数据库自治事务学习研究

自治事务（Autonomous Transaction）允许用户创建一个“事务中的事务”，它能独立于其父事务提交或回滚。 利用自治事务，可以挂起当前执行的事务，开始一个新事务，完成一些工作，然后提交或回滚，所有这些都 不影响当前执行事务的状态。同样，当前事务的回退也对自治事务没有影响。
本文以国产数据库为案例来阐述自治事务的行为及原理。

测试案例1. Kingbase自治事务

[kingbase@localhost ~]$ ksql
test=# create table prolog(id int primary key,errname varchar);
CREATE TABLE
test=# \set SQLTERM /
test=# create or replace function write_prolog(a inout int,b inout text) return integer as
test-# pragma autonomous_transaction;
test-# begin
test-# insert into prolog(id,errname) values(a,b);
test-# commit;
test-# return a;
test-# end;
test-# /
CREATE FUNCTION
test=# create table test001(id int primary key,name varchar);
test-# /
CREATE TABLE
test=# create or replace procedure proc_auto_trans(a inout int,b inout text) as
test-# c int;
test-# begin
test-# insert into test001(id,name) values(a,b);
test-# c := write_prolog(a,b);
test-# insert into test001(id,name) values(a,b);
test-# end;
test-# /
CREATE PROCEDURE
test=# declare
test-# a int := 2;
test-# b text := 'bob';
test-# begin
test-# proc_auto_trans(a,b);
test-# end;
test-# /
ERROR:  duplicate key value violates unique constraint "test001_pkey"
DETAIL:  Key (id)=(2) already exists.
CONTEXT:  SQL statement "insert into test001(id,name) values(a,b)"
PL/SQL function proc_auto_trans(integer,text) line 6 at SQL statement
SQL statement "CALL proc_auto_trans(a,b)"
PL/SQL function inline_code_block line 5 at CALL
test=# select * from prolog;
test-# /
 id | errname
----+---------
  2 | bob
(1 row)

test=# select * from test001;
test-# /
 id | name
----+------
(0 rows)

测试案例2. openGauss自治事务

[omm@localhost ~]$ gsql
guass_test=> create table prolog(id int primary key,errname varchar);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "prolog_pkey" for table "prolog"
CREATE TABLE
guass_test=> create table test001(id int primary key,name varchar);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test001_pkey" for table "test001"
CREATE TABLE
guass_test=> create or replace function write_prolog(a inout int,b inout text) return integer as
pragma autonomous_transaction;
begin
        insert into prolog(id,errname) values(a,b);
        commit;
        return a;
end;
/guass_test$> guass_test$> guass_test$> guass_test$> guass_test$> guass_test$> guass_test$>
CREATE FUNCTION
guass_test=> create or replace procedure proc_auto_trans(a inout int,b inout text) as
c int;
begin
        insert into test001(id,name) values(a,b);
        c := write_prolog(a,b);
        insert into test001(id,name) values(a,b);
end;
/guass_test$> guass_test$> guass_test$> guass_test$> guass_test$> guass_test$> guass_test$>
CREATE PROCEDURE
guass_test=> declare
        a int := 2;
        b text := 'bob';
begin
        proc_auto_trans(a,b);
end;
/guass_test-> guass_test-> guass_test-> guass_test$> guass_test$> guass_test$>
ERROR:  duplicate key value violates unique constraint "test001_pkey"
DETAIL:  Key (id)=(2) already exists.
CONTEXT:  SQL statement "insert into test001(id,name) values(a,b)"
PL/pgSQL function proc_auto_trans(integer,text) line 6 at SQL statement
SQL statement "CALL proc_auto_trans(a,b)"
PL/pgSQL function inline_code_block line 4 at SQL statement
guass_test=> select * from prolog;
 id | errname
----+---------
  2 | bob
(1 row)

guass_test=> select * from test001;
 id | name
----+------
(0 rows)

从Kingbase和openGauss的测试案例中验证了自治事务独立于其父事务提交或回滚，在记录日志等场景下发挥着有很重要的作用。

原理分析

接下来以开源openGauss源码分析来阐述其实现原理

源码调用流程图

/* gram.y */
decl_sect		: 
...
                | opt_block_label decl_start { SetErrorState(); } decl_stmts
                    {
                        u_sess->plsql_cxt.curr_compile_context->plpgsql_IdentifierLookup = IDENTIFIER_LOOKUP_NORMAL;
                        $$.label	  = $1;
                        /* Remember variables declared in decl_stmts */
                        $$.n_initvars = plpgsql_add_initdatums(&($$.initvarnos));
                        $$.isAutonomous = u_sess->plsql_cxt.pragma_autonomous;
                        u_sess->plsql_cxt.pragma_autonomous = false;
                    }
...
decl_statement	: 
...
                |	K_PRAGMA any_identifier ';'
                    {
                        if (pg_strcasecmp($2, "autonomous_transaction") == 0) {
                            u_sess->plsql_cxt.pragma_autonomous = true;
                            if (u_sess->plsql_cxt.curr_compile_context->plpgsql_curr_compile !=NULL) {
                                u_sess->plsql_cxt.curr_compile_context->plpgsql_curr_compile->is_autonomous = true;
                            }
                        }
                        else {
                            elog(errstate, "invalid pragma");
                            u_sess->plsql_cxt.have_error = true;
                        }
                    }
/* gram.y 语法解析出语句块是否开启自治事务 PLpgSQL_function->action->isAutonomous,之后单独调用libpq接口来执行语句块 */
plpgsql_inline_handler
|__plpgsql_exec_autonm_function /* IsAutonomousTransaction(func->action->isAutonomous) */
|	|__CreateAutonomousSession
|	|	|__connInfo "dbname=%s port=%d host='localhost' application_name='autonomoustransaction' user=%s connect_timeout=600"
|	|	|__PQconnectdb(connInfo)
|	|	|__PQsetNoticeProcessor
|	|
|	|__AssembleAutomnousStatement
|	|	|__appendStringInfoString /* Lookup the pg_proc tuple by Oid; */
|	|
|	|__AutonomousSession::ExecSimpleQuery
|		|__PQexecAutonm
|		|	|__PQsendQueryAutonm
|		|		|__PQsendQueryStart
|		|		|__pqPuts
|		|		|__pqFlush
|		|
|		|__HandlePGResult
|			|__PQresultStatus
|			|__PQgetvalue
|
|__DestoryAutonomousSession
	|__AutonomousSession::DetachSession
		|__PQfinish
		|__PQclear

总结如下：
openGauss在执行存储过程中通过分析函数DECLARE部分是否有PRAGMA AUTONOMOUS_TRANSACTION来决定是否对该函数开启自治事务，如果开启自治事务，接下来，第一步，创建libpq连接，第二步，查询pg_proc表拼装SQL语句，如果带有返回值的拼装为select * from xxx()，反之拼装成select xxx()，第三步libpq发送SQL语句到后端执行，第四步处理返回结果，最后一步断开连接。