PRAGMA AUTONOMOUS_TRANSACTION - PolarDB - Alibaba Cloud Documentation Center

An autonomous transaction is an independent transaction started by a calling program. A commit or rollback of SQL statements in the autonomous transaction has no effect on the commit or rollback in a transaction of the calling program. A commit or rollback in the calling program has no effect on the commit or rollback of SQL statements in the autonomous transaction.

An SPL program is declared as an autonomous transaction when you execute the following statement in the declaration section of the SPL block:

PRAGMA AUTONOMOUS_TRANSACTION;

The following SPL programs can include PRAGMA AUTONOMOUS_TRANSACTION:

Standalone stored procedures and functions
Anonymous blocks
Stored procedures and functions declared as subprograms in packages and other calling procedures, functions, and anonymous blocks
Triggers
Object type methods

The following issues and limits are related to autonomous transactions:

Each autonomous transaction consumes a connection slot when it is in progress. In some cases, it indicates that the value of max_connections in the postgresql.conf file may be increased.
In most cases, an autonomous transaction seems to be a separate session. However, GUC that is set by SET is an exception. Autonomous transactions absorb the surrounding values and can propagate committed values to the outer transaction.
Autonomous transactions can be nested. Up to 16 levels of autonomous transactions are allowed in a single session.
Parallel query is not supported in autonomous transactions.
Autonomous transactions in PolarDB for PostgreSQL(Compatible with Oracle) is not fully compatible with Oracle databases. If an uncommitted transaction exists at the end of the SPL block, the PolarDB for PostgreSQL(Compatible with Oracle) autonomous transaction does not return an error.

The following examples show how to use the autonomous transaction. The first scenario shows the default behavior without the autonomous transaction.

Before each scenario, the dept table is reset to the following initial values:

SELECT * FROM dept;

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

Scenarios

Scenario 1a: No autonomous transactions with only a final COMMIT

The first scenario shows how to insert three rows. Insert the first row after the initial BEGIN statement of the transaction, insert the second row in the anonymous block of the started transaction. Then, insert the third row in the stored procedure that is executed in the anonymous block.

The following example shows the stored procedure:

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

The following example shows the PostgreSQL session:

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

After the final commit, all three rows are inserted:

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)

Scenario 1b: No autonomous transactions, but a final ROLLBACK

The following scenario shows a final ROLLBACK statement after all inserts results in the rollback of all three insertions:

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)

Scenario 1c: No autonomous transactions, but anonymous block ROLLBACK

The ROLLBACK statement at the end of the anonymous block also results in the rollback of all three insertions:

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)

The next scenario shows the effects when you enable autonomous transaction by using PRAGMA AUTONOMOUS_TRANSACTION in different statements.

Scenario 2a: Autonomous transaction of anonymous block with COMMIT

The stored procedure is unchanged:

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

PRAGMA AUTONOMOUS_TRANSACTION is provided in the anonymous block with the COMMIT statement at the end of the anonymous block.

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;

After the ROLLBACK statement at the end of the transaction is executed, only the first insertion at the beginning of the transaction is rolled back. The other two inserted rows in the anonymous block with PRAGMA AUTONOMOUS_TRANSACTION have been committed.

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)

Scenario 2b: Autonomous transaction anonymous block with COMMIT including stored procedure with ROLLBACK, but not an autonomous transaction procedure
The ROLLBACK statement is at the end of the stored procedure. However, PRAGMA ANONYMOUS_TRANSACTION is not included in this stored procedure.
```
CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
    ROLLBACK;
END;
```
The rollback in the stored procedure deletes the two rows inserted in the anonymous block (deptno 60 and 70) before the final COMMIT statement in the anonymous block.
```
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;
```
After the final commit at the end of the transaction, the only inserted row is the first one at the beginning of the transaction. The anonymous block is an autonomous transaction. Therefore, the rollback in the enclosed stored procedure has no effect on the row inserted before the anonymous block is executed.
```
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)
```
Scenario 2c: Autonomous transaction anonymous block with COMMIT including stored procedure with ROLLBACK that is also an autonomous transaction procedure
The stored procedure with the ROLLBACK statement at the end also includes PRAGMA ANONYMOUS_TRANSACTION. This isolates the effect of the ROLLBACK statement in the stored procedure.
```
CREATE OR REPLACE PROCEDURE insert_dept_70 IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
    ROLLBACK;
END;
```
The rollback in the stored procedure deletes the row inserted by the stored procedure. However, the other row inserted in the anonymous block is not deleted.
```
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;
```
After the final commit at the end of the transaction, the inserted rows are the row inserted at the beginning of the transaction and the row inserted at the beginning of the anonymous block. The only row insertion that is rolled back is the one in the stored procedure.
```
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)
```
The following sections show examples of PRAGMA AUTONOMOUS_TRANSACTION in other SPL program types.

Autonomous transaction trigger

The following example shows the effect when you declare a trigger with PRAGMA AUTONOMOUS_TRANSACTION.

The following table is created to log changes to the emp table:

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

The following example shows the trigger that is attached to the emp table and inserts changes into the empauditlog table. PRAGMA AUTONOMOUS_TRANSACTION is included in the declaration.

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;

The following two rows are inserted into the emp table in a transaction started by the BEGIN statement:

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

The following example shows the two new rows in the emp table and the two entries in the empauditlog table:

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)

However, the ROLLBACK statement is executed in this session. The newly inserted two rows do not exist in the emp table but the preceding two entries still exist in the empauditlog table. This is because the trigger implicitly commits the changes and PRAGMA AUTONOMOUS_TRANSACTION commits the changes regardless of the rollback in the invocation 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)

Autonomous transaction object type method

The following example shows the effect when you declare an object type method with PRAGMA AUTONOMOUS_TRANSACTION.

The following object type and object type body are created. The member stored procedure in the object type body includes PRAGMA AUTONOMOUS_TRANSACTION in the declaration and the COMMIT statement at the end of the stored procedure.

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;

In the following anonymous block, the system inserts a row into the dept table, invokes the insert_dept method, and then executes the ROLLBACK statement at the end of the anonymous block.

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 has been declared as an autonomous transaction. Therefore, the row of department 60 remains in the table. However, the rollback operation deletes the inserted row of department 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)