Transaction Management

Calling a stored procedure automatically starts a transaction. When the calling is complete, the transaction is automatically submitted, or rolled back upon an exception. In addition to automatic transaction control, you can also use COMMIT/ROLLBACK to control transactions in stored procedures. Running the COMMIT/ROLLBACK commands in a stored procedure will commit or roll back the current transaction and automatically starts a new transaction. All subsequent operations will be performed in the new transaction.

Usage Scenarios

COMMIT/ROLLBACK can be used in the following contexts:

COMMIT/ROLLBACK can be used in PLSQL stored procedures.
COMMIT/ROLLBACK can be used in stored procedures that contain EXCEPTION.
COMMIT/ROLLBACK can be used in EXCEPTION statements of stored procedures.
A stored procedure that contains COMMIT/ROLLBACK (which means the stored procedure is controled by /BEGIN/START/END) can be called in a transaction block.
COMMIT/ROLLBACK, including IF, FOR, CURSOR LOOP, and WHILE, can be called in most PLSQL contexts and statements.

The following content can be submitted or rolled back:

DDL statements after COMMIT/ROLLBACK can be submitted or rolled back.
DML statements after COMMIT/ROLLBACK can be submitted.
GUC parameters in stored procedures can be submitted or rolled back.

Usage Restrictions

COMMIT/ROLLBACK cannot be used in the following contexts:

COMMIT/ROLLBACK cannot be called in stored procedures other than PLSQL, such as PLJAVA and PLPYTHON.
COMMIT/ROLLBACK and stored procedures that contain COMMIT/ROLLBACK cannot be called in functions.
After SAVEPOINT is called in a transaction block, stored procedures that contain COMMIT/ROLLBACK cannot be called.
Stored procedures that contain COMMIT/ROLLBACK cannot be called in TRIGGER.
COMMIT/ROLLBACK cannot be invoked in EXECUTE statements.
Stored procedures that contain COMMIT/ROLLBACK cannot be called in CURSOR statements.
Stored procedures that contain IMMUTABLE or SHIPPABLE cannot call COMMIT/ROLLBACK or another stored procedure that contain COMMIT/ROLLBACK.
Stored procedures that contain COMMIT/ROLLBACK cannot be called in SQL statements other than SELECT PROC and CALL PROC.
COMMIT/ROLLBACK cannot be called in a stored procedure whose header contains GUC parameters.
COMMIT/ROLLBACK cannot be called in expressions or CURSOR/EXECUTE statements.
Return values and expression calculation of stored procedures are not supported.

The following content cannot be committed or rolled back:

Variables declared or imported in stored procedures cannot be submitted or rolled back.
In stored procedures, GUC parameters that take effect only after a restart cannot be submitted or rolled back.

Examples

Example 1: COMMIT/ROLLBACK can be used in PLSQL stored procedures.

CREATE TABLE EXAMPLE1(COL1 INT);

CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE()
AS
BEGIN
    FOR i IN 0..20 LOOP
        INSERT INTO EXAMPLE1(COL1) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
/

Example 2:

COMMIT/ROLLBACK can be used in stored procedures that contain EXCEPTION.

COMMIT/ROLLBACK can be used in EXCEPTION statements of stored procedures.

DDL statements after COMMIT/ROLLBACK can be submitted or rolled back.

CREATE OR REPLACE PROCEDURE TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK()
AS
BEGIN
    DROP TABLE IF EXISTS TEST_COMMIT;
    CREATE TABLE TEST_COMMIT(A INT, B INT);
    INSERT INTO TEST_COMMIT SELECT 1, 1;
    COMMIT;
        CREATE TABLE TEST_ROLLBACK(A INT, B INT);
    RAISE EXCEPTION 'RAISE EXCEPTION AFTER COMMIT';
EXCEPTION
    WHEN OTHERS THEN
    INSERT INTO TEST_COMMIT SELECT 2, 2;
    ROLLBACK;
END;
/

Example 3: A stored procedure that contains COMMIT/ROLLBACK (which means the stored procedure is controled by /BEGIN/START/END) can be called in a transaction block.
```
BEGIN;
    CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();
END;
```

Example 4: COMMIT/ROLLBACK, including IF, FOR, CURSOR LOOP, and WHILE, can be called in most PLSQL contexts and statements.

CREATE OR REPLACE PROCEDURE TEST_COMMIT2()
IS
BEGIN
    DROP TABLE IF EXISTS TEST_COMMIT;
    CREATE TABLE TEST_COMMIT(A INT);
    FOR I IN REVERSE 3..0 LOOP
    INSERT INTO TEST_COMMIT SELECT I;
    COMMIT;
    END LOOP;
    FOR I IN REVERSE 2..4 LOOP
    UPDATE TEST_COMMIT SET A=I;
    COMMIT;
    END LOOP;
EXCEPTION
WHEN OTHERS THEN
    INSERT INTO TEST_COMMIT SELECT 4;
    COMMIT;
END;
/

Example 5: GUC parameters in stored procedures can be submitted or rolled back.

SHOW explain_perf_mode;
SHOW enable_force_vector_engine;

CREATE OR REPLACE PROCEDURE GUC_ROLLBACK()
AS
BEGIN
    SET enable_force_vector_engine = on;
    COMMIT;
    SET explain_perf_mode TO pretty;
    ROLLBACK;
END;
/

call GUC_ROLLBACK();
SHOW explain_perf_mode;
SHOW enable_force_vector_engine;
SET enable_force_vector_engine = off;

Example 6: COMMIT/ROLLBACK and stored procedures that contain COMMIT/ROLLBACK cannot be called in functions.

CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE1() RETURN INT
AS
EXP INT;
BEGIN
    FOR i IN 0..20 LOOP
        INSERT INTO EXAMPLE1(col1) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
    SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
    RETURN EXP;
END;
/

Example 7: Stored procedures that contain COMMIT/ROLLBACK cannot be called in functions.

CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE2() RETURN INT
AS
EXP INT;
BEGIN
    -- transaction_example is a stored procedure and contains the COMMIT/ROLLBACK statement.
    CALL transaction_example();
    SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
    RETURN EXP;
END;
/

Example 8: A TRIGGER stored procedure cannot contain COMMIT/ROLLBACK or called another stored procedure that contains COMMIT/ROLLBACK.

CREATE OR REPLACE FUNCTION FUNCTION_TRI_EXAMPLE2() RETURN TRIGGER
AS
EXP INT;
BEGIN
    FOR i IN 0..20 LOOP
        INSERT INTO EXAMPLE1(col1) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
    SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
END;
/

CREATE TRIGGER TRIGGER_EXAMPLE AFTER DELETE ON EXAMPLE1
FOR EACH ROW EXECUTE PROCEDURE FUNCTION_TRI_EXAMPLE2();

DELETE FROM EXAMPLE1;

Example 9: Stored procedures that contain IMMUTABLE or SHIPPABLE cannot call COMMIT/ROLLBACK or another stored procedure that contain COMMIT/ROLLBACK.

CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE1()
IMMUTABLE
AS
BEGIN
    FOR i IN 0..20 LOOP
        INSERT INTO EXAMPLE1 (col1) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
/

Example 10: Variables declared or imported in stored procedures cannot be submitted or rolled back.

CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE2(EXP_OUT OUT INT)
AS
EXP INT;
BEGIN
    EXP_OUT := 0;
    COMMIT;
    RAISE NOTICE '%', 'EXP IS:'||EXP;
    EXP_OUT := 1;
    ROLLBACK;
    RAISE NOTICE '%', 'EXP IS:'||EXP;
END;
/

Example 11: Calling in SQL statements (other than Select Procedure) is not supported.

CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE3()
AS
BEGIN
    FOR i IN 0..20 LOOP
        INSERT INTO EXAMPLE1 (col1) VALUES (i);
        IF i % 2 = 0 THEN
            EXECUTE IMMEDIATE 'COMMIT';
        ELSE
            EXECUTE IMMEDIATE 'ROLLBACK';
        END IF;
    END LOOP;
END;
/

Example 12: COMMIT/ROLLBACK cannot be called in a stored procedure whose header contains GUC parameters.

CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE4()
SET ARRAY_NULLS TO "ON"
AS
BEGIN
    FOR i IN 0..20 LOOP
        INSERT INTO EXAMPLE1 (col1) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
/

Example 13: A stored procedure object whose cursor is open cannot contain COMMIT/ROLLBACK.

CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE5(INTIN IN INT, INTOUT OUT INT)
AS
BEGIN
INTOUT := INTIN + 1;
COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE6()
AS
CURSOR CURSOR1(EXPIN INT)
IS SELECT TRANSACTION_EXAMPLE5(EXPIN);
INTEXP INT;
BEGIN
    FOR i IN 0..20 LOOP
        OPEN CURSOR1(i);
        FETCH CURSOR1 INTO INTEXP;
        INSERT INTO EXAMPLE1(COL1) VALUES (INTEXP);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
        CLOSE CURSOR1;
    END LOOP;
END;
/

Example 14: COMMIT/ROLLBACK cannot be called in expressions or CURSOR/EXECUTE statements.

CREATE OR REPLACE PROCEDURE exec_func1()
AS
BEGIN
    CREATE TABLE TEST_exec(A INT);
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE exec_func2()
AS
BEGIN
EXECUTE exec_func1();
COMMIT;
END;
/

Example 15: Return values and expression calculation of stored procedures are not supported.

CREATE OR REPLACE PROCEDURE exec_func3(RET_NUM OUT INT)
AS
BEGIN
    RET_NUM := 1+1;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE exec_func4(ADD_NUM IN INT)
AS
SUM_NUM INT;
BEGIN
SUM_NUM := ADD_NUM + exec_func3();
COMMIT;
END;
/

Issue