如何使用PRAGMAAUTONOMOUS_TRANSACTION进行事务控制_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

自治事务是由调用程序启动的独立事务。自治事务中SQL命令的提交或回滚对调用程序的任何事务中的提交或回滚都没有影响。调用程序中的提交或回滚对自治事务中SQL命令的提交或回滚也没有影响。

通过在SPL块的声明部分中指定以下指令，可将SPL程序声明为自治事务：

PRAGMA AUTONOMOUS_TRANSACTION;

以下SPL程序可包含 PRAGMA AUTONOMOUS_TRANSACTION：

独立的存储过程和函数。
匿名块。
包中声明为子程序的存储过程和函数以及其他调用存储过程、函数和匿名块。
触发器。
对象类型方法。

下面是与自治事务有关的问题和限制：

每个自治事务只要在进行中，就会消耗一个连接槽。在某些情况下，这可能意味着应增大postgresql.conf文件中的max_connections参数。
在大多数方面，自治事务的行为就像是一个完全独立的会话，但GUC（即通过SET建立的设置）是一个有意制造的例外。自治事务吸收周围的值，并可以将它们提交的值传播到外部事务。
自治事务可以嵌套，但在单个会话中自治事务的嵌套级别限制为16级。
自治事务中不支持并行查询。
自治事务的PolarDB PostgreSQL版（兼容Oracle）实现与Oracle数据库不完全兼容，因为如果SPL块末尾有未提交的事务，则PolarDB PostgreSQL版（兼容Oracle）自治事务不会产生错误。

以下一组示例阐释了自治事务的用法。第一组场景显示了没有自治事务时的默认行为。

在每个场景之前，dept表重置为以下初始值：

SELECT * FROM dept;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

场景

场景1a：没有自治事务，只有最终COMMIT

第一组场景显示了如何插入三行，首先就从事务的初始BEGIN命令之后开始插入第一行，然后从起始事务的匿名块插入第二行，最后从匿名块内执行的存储过程插入第三行。

该存储过程如下：

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
END;

PSQL会话如下：

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
END;
COMMIT;

在最后提交后，将插入所有三行：

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |     loc
--------+------------+-------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
     60 | FINANCE    | CHICAGO
     70 | MARKETING  | LOS ANGELES
(7 rows)

场景1b：没有自治事务，但有最终ROLLBACK

下一个场景显示，所有插入之后的最后一个ROLLBACK命令将导致所有三个插入的回滚：

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
END;
ROLLBACK;

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

场景1c：没有自治事务，但有匿名块ROLLBACK

匿名块结尾给出的ROLLBACK命令也消除了所有以前的三个插入：

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    ROLLBACK;
END;
COMMIT;

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

下一组场景显示了在不同位置使用PRAGMA AUTONOMOUS_TRANSACTION自治事务的效果。

场景2a：带有COMMIT的匿名块的自治事务

存储过程保持最初创建的样子：

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
END;

现在，PRAGMA AUTONOMOUS_TRANSACTION通过匿名块给出，并且匿名块末尾给出COMMIT命令。

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
ROLLBACK;

在事务结束时执行ROLLBACK后，只丢弃了事务开始时的第一行插入。带有PRAGMA AUTONOMOUS_TRANSACTION的匿名块中的另两行插入已独立提交。

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |     loc
--------+------------+-------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | FINANCE    | CHICAGO
     70 | MARKETING  | LOS ANGELES
(6 rows)

场景2b：带有COMMIT的自治事务匿名块包含带有ROLLBACK的存储过程，而不是自治事务过程
现在，存储过程在末尾具有ROLLBACK命令。但是，您会看到PRAGMA ANONYMOUS_TRANSACTION未包含在此存储过程中。
```
CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
    ROLLBACK;
END;
```
现在，该存储过程中的回滚会在匿名块中的最终COMMIT命令之前删除匿名块中插入的两行（deptno 60和70）。
```
BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
COMMIT;
```
在事务结束时进行最终提交之后，插入的唯一一行是事务开始时插入的第一行。由于匿名块是自治事务，因此封闭存储过程中的回滚对执行匿名块之前发生的插入没有影响。
```
SELECT * FROM dept ORDER by 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
(5 rows)
```
场景2c ：带有COMMIT的自治事务匿名块包含带ROLLBACK的存储过程，该过程也是自治事务过程
现在，在末尾具有ROLLBACK命令的存储过程也包含PRAGMA ANONYMOUS_TRANSACTION。这将隔离该存储过程中ROLLBACK命令的效果。
```
CREATE OR REPLACE PROCEDURE insert_dept_70 IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
    ROLLBACK;
END;
```
现在，该存储过程中的回滚会删除由该过程插入的行，而不是在匿名块中插入的其他行。
```
BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
COMMIT;
```
在事务结束时进行最终提交后，插入的行是从事务开始时插入的第一行，以及在匿名块开始时插入的行。回滚的唯一插入是该存储过程中的插入。
```
SELECT * FROM dept ORDER by 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
     60 | FINANCE    | CHICAGO
(6 rows)
```
现在，以下各节显示了一系列其他SPL程序类型中的PRAGMA AUTONOMOUS_TRANSACTION的示例。

自治事务触发器

以下示例显示了使用PRAGMA AUTONOMOUS_TRANSACTION声明触发器的效果。

下表是为了记录对emp表的更改而创建的：

CREATE TABLE empauditlog (
    audit_date      DATE,
    audit_user      VARCHAR2(20),
    audit_desc      VARCHAR2(20)
);

附加到emp表并将这些更改插入empauditlog表的触发器如下：您会看到，在声明部分中包含了PRAGMA AUTONOMOUS_TRANSACTION。

CREATE OR REPLACE TRIGGER emp_audit_trig
    AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_action        VARCHAR2(20);
BEGIN
    IF INSERTING THEN
        v_action := 'Added employee(s)';
    ELSIF UPDATING THEN
        v_action := 'Updated employee(s)';
    ELSIF DELETING THEN
        v_action := 'Deleted employee(s)';
    END IF;
    INSERT INTO empauditlog VALUES (SYSDATE, USER,
        v_action);
END;

在BEGIN命令启动的事务中执行了以下两个插入到emp表的操作。

BEGIN;
INSERT INTO emp VALUES (9001,'SMITH','ANALYST',7782,SYSDATE,NULL,NULL,10);
INSERT INTO emp VALUES (9002,'JONES','CLERK',7782,SYSDATE,NULL,NULL,10);

下面显示了emp表中的两个新行以及empauditlog表中的两个条目：

SELECT * FROM emp WHERE empno > 9000;

 empno | ename |   job   | mgr  |      hiredate      | sal | comm | deptno
-------+-------+---------+------+--------------------+-----+------+--------
  9001 | SMITH | ANALYST | 7782 | 23-AUG-18 07:12:27 |     |      |     10
  9002 | JONES | CLERK   | 7782 | 23-AUG-18 07:12:27 |     |      |     10
(2 rows)

SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24:MI:SS') AS "audit date",
    audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;

     audit date     |  audit_user  |    audit_desc
--------------------+--------------+-------------------
 23-AUG-18 07:12:27 | polardb      | Added employee(s)
 23-AUG-18 07:12:27 | polardb      | Added employee(s)
(2 rows)

但随后在此会话期间给出了ROLLBACK命令。emp表不再包含这两行，而empauditlog表仍包含其两个条目，这是因为触发器隐式执行了提交，并且PRAGMA AUTONOMOUS_TRANSACTION提交这些更改的操作独立于调用事务中给出的回滚。

ROLLBACK;

SELECT * FROM emp WHERE empno > 9000;

 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24:MI:SS') AS "audit date",
    audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;

     audit date     |  audit_user  |    audit_desc
--------------------+--------------+-------------------
 23-AUG-18 07:12:27 | polardb      | Added employee(s)
 23-AUG-18 07:12:27 | polardb      | Added employee(s)
(2 rows)

自治事务对象类型方法

以下示例显示了使用PRAGMAAUTONOMOUS_TRANSACTION声明对象方法的效果。

将创建以下对象类型和对象类型主体。对象类型主体中的成员存储过程包含声明部分中的PRAGMA AUTONOMOUS_TRANSACTION以及位于存储过程结尾的COMMIT。

CREATE OR REPLACE TYPE insert_dept_typ AS OBJECT (
    deptno          NUMBER(2),
    dname           VARCHAR2(14),
    loc             VARCHAR2(13),
    MEMBER PROCEDURE insert_dept
);

CREATE OR REPLACE TYPE BODY insert_dept_typ AS
    MEMBER PROCEDURE insert_dept
    IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO dept VALUES (SELF.deptno,SELF.dname,SELF.loc);
        COMMIT;
    END;
END;

在以下匿名块中，将执行一个插入到dept表的操作，然后调用对象的insert_dept方法，最后在匿名块中执行ROLLBACK命令。

BEGIN;
DECLARE
    v_dept          INSERT_DEPT_TYP :=
                      insert_dept_typ(60,'FINANCE','CHICAGO');
BEGIN
    INSERT INTO dept VALUES (50,'HR','DENVER');
    v_dept.insert_dept;
    ROLLBACK;
END;

由于insert_dept已声明为自治事务，因此其插入的部门编号60仍位于表中，但回滚删除了插入的部门50。

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | FINANCE    | CHICAGO
(5 rows)