更新时间:2023-10-23 GMT+08:00

事务管理

存储过程本身就处于一个事务中,开始调用最外围存储过程时会自动开启一个事务,在调用结束时自动提交或者发生异常时回滚。除了系统自动的事务控制外,也可以使用COMMIT/ROLLBACK来控制存储过程中的事务。在存储过程中调用COMMIT/ROLLBACK命令,将提交/回滚当前事务并自动开启一个新的事务,后续的所有操作都会在此新事务中运行。

保存点SAVEPOINT是事务中的一个特殊记号,它允许将那些在它建立后执行的命令全部回滚,把事务的状态恢复到保存点所在的时刻。存储过程中允许使用保存点来进行事务管理,当前支持保存点的创建、回滚和释放操作。存储过程中使用回滚保存点只是回退当前事务的修改,而不会改变存储过程的执行流程,也不会回退存储过程中的局部变量值等。

语法格式

定义保存点
    SAVEPOINT savepoint_name;
回滚保存点
    ROLLBACK TO [SAVEPOINT] savepoint_name;
释放保存点
    RELEASE [SAVEPOINT] savepoint_name;

使用场景

支持调用的上下文环境:

  • 支持在PLSQL的存储过程内使用COMMIT/ROLLBACK/SAVEPOINT。
  • 支持含有EXCEPTION的存储过程使用COMMIT/ROLLBACK/SAVEPOINT。
  • 支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK/SAVEPOINT。
  • 支持在事务块里调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程,即通过/BEGIN/START/END等开启控制的外部事务。
  • 支持在子事务中调用含有SAVEPOINT的存储过程,即存储过程中使用外部定义的SAVEPOINT,回退事务状态到存储过程外定义的SAVEPOINT位置。
  • 支持存储过程外部对存储过程内定义的SAVEPOINT可见,即存储过程外可以将事务修改回滚到存储过程中定义SAVEPOINT的位置。
  • 支持多数PLSQL的上下文和语句内调用COMMIT/ROLLBACK/SAVEPOINT,包括常用的IF/FOR/CURSOR LOOP/WHILE。
  • 支持存储过程返回值与简单表达式计算中调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程或者函数。

支持提交/回滚的内容:

  • 支持DDL在COMMIT/ROLLBACK后的提交/回滚。
  • 支持DML的COMMIT/ROLLBACK后的提交。
  • 支持存储过程内GUC参数的回滚提交。

使用限制

不支持调用的上下文环境:

  • 不支持除PLSQL的其他存储过程中调用COMMIT/ROLLBACK/SAVEPOINT,例如PLJAVA、PLPYTHON等。
  • 不支持函数中调用COMMIT/ROLLBACK/SAVEPOINT,包括函数调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程。
  • 不支持事务块中调用了SAVEPOINT后,调用含有COMMIT/ROLLBACK的存储过程。
  • 不支持TRIGGER中调用含有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程。
  • 不支持EXECUTE语句中调用COMMIT/ROLLBACK/SAVEPOINT语句。
  • 不支持在CURSOR语句中打开一个含有COMMIT/ROLLBACK/SAVEPOINT的存储过程。
  • 不支持带有IMMUTABLE以及SHIPPABLE的存储过程调用COMMIT/ROLLBACK/SAVEPOINT,或调用带有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程。
  • 不支持SQL中调用含有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程,除了SELECT PROC以及CALL PROC。
  • 存储过程头带有GUC参数设置的不允许调用COMMIT/ROLLBACK/SAVEPOINT语句。
  • 不支持CURSOR/EXECUTE语句,以及各类表达式内调用COMMIT/ROLLBACK/SAVEPOINT。
  • 自治事务和存储过程事务是两个独立的事务,不能互相使用对方事务中定义的保存点。

不支持提交回滚的内容:

  • 不支持存储过程内声明变量以及传入变量的提交/回滚。
  • 不支持存储过程内必须重启生效的GUC参数的提交/回滚。

示例

  • 示例1:支持在PLSQL的存储过程内使用COMMIT/ROLLBACK。
    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;
    /
  • 示例2:

    支持含有EXCEPTION的存储过程使用COMMIT/ROLLBACK。

    支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK。

    支持DDL在COMMIT/ROLLBACK后的提交/回滚。

    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;
    /
    
  • 示例3:支持在事务块里调用含有COMMIT/ROLLBACK的存储过程,即通过/BEGIN/START/END等开启控制的外部事务。
    BEGIN;
        CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();
    END;
  • 示例4:支持多数PLSQL的上下文和语句内调用COMMIT/ROLLBACK,包括常用的IF/FOR/CURSOR LOOP/WHILE。
    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;
    /
  • 示例5:支持存储过程返回值与简单表达式计算。
    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;
    /
  • 示例6:支持存储过程内GUC参数的回滚提交。
    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;
  • 示例7:函数(Function)中不允许调用commit/rollback语句,同时不允许函数调用含有commit/rollback的存储过程。
    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;
    /
  • 示例8:函数(Fucntion)中不允许调用带有commit/rollback语句的存储过程。
    CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE2() RETURN INT
    AS
    EXP INT;
    BEGIN
        --transaction_example为存储过程,带有commit/rollback语句
        CALL transaction_example();
        SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
        RETURN EXP;
    END;
    /
  • 示例9:不允许Trigger的存储过程包含commit/rollback语句,或调用带有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;
  • 示例10:不支持带有IMMUABLE以及SHIPPABLE的存储过程调用commit/rollback,或调用带有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;
    /
  • 示例11:不支持存储过程中任何变量的提交,包括存储过程内声明的变量或者传入的参数。
    CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE2(EXP_OUT OUT INT)
    AS
    EXP INT;
    BEGIN
        EXP_OUT := 0;
        COMMIT;
        DBE_OUTPUT.PRINT_LINE('EXP IS:'||EXP);
        EXP_OUT := 1;
        ROLLBACK;
        DBE_OUTPUT.PRINT_LINE('EXP IS:'||EXP);
    END;
    /
  • 示例12:不支持出现在SQL中的调用(除了Select Procedure)。
    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;
    /
  • 示例13:存储过程头带有GUC参数设置的不允许调用commit/rollback语句。
    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;
    /
  • 示例14:游标open的对象不允许为带有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; 
    /
  • 示例15:不支持CURSOR/EXECUTE语句,以及各类表达式内调用COMMIT/ROLLBACK。
    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;
    /
  • 示例16:存储过程使用保存点回退事务部分修改。
    CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE1()
    AS
    BEGIN
        INSERT INTO EXAMPLE1 VALUES(1);
        SAVEPOINT s1;
        INSERT INTO EXAMPLE1 VALUES(2);
        ROLLBACK TO s1;  -- 回退插入记录2
        INSERT INTO EXAMPLE1 VALUES(3);
    END;
    /
  • 示例17:存储过程中使用保存点回退到存储过程外部定义的保存点。
    CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE2()
    AS
    BEGIN
        INSERT INTO EXAMPLE1 VALUES(2);
        ROLLBACK TO s1;  -- 回退插入记录2
        INSERT INTO EXAMPLE1 VALUES(3);
    END;
    /
    
    BEGIN;
    INSERT INTO EXAMPLE1 VALUES(1);
    SAVEPOINT s1;
    CALL STP_SAVEPOINT_EXAMPLE2();
    SELECT * FROM EXAMPLE1;
    COMMIT;
  • 示例18:存储过程外部SQL/其它存储过程回退到存储过程中定义的保存点。
    CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE3()
    AS
    BEGIN
        INSERT INTO EXAMPLE1 VALUES(1);
        SAVEPOINT s1;
        INSERT INTO EXAMPLE1 VALUES(2);
    END;
    /
    
    BEGIN;
    INSERT INTO EXAMPLE1 VALUES(3);
    CALL STP_SAVEPOINT_EXAMPLE3();
    ROLLBACK TO SAVEPOINT s1; --回退存储过程中插入记录2
    SELECT * FROM EXAMPLE1;
    COMMIT;