事务管理

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

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

语法格式

  1. 定义保存点
  2.     SAVEPOINT savepoint_name;
  3. 回滚保存点
  4.     ROLLBACK TO [SAVEPOINT] savepoint_name;
  5. 释放保存点
  6.     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。

    1. CREATE TABLE EXAMPLE1(COL1 INT);
    3. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE()
    4. AS
    5. BEGIN
    6.     FOR i IN 0..20 LOOP
    7.         INSERT INTO EXAMPLE1(COL1) VALUES (i);
    8.         IF i % 2 = 0 THEN
    9.             COMMIT;
    10.         ELSE
    11.             ROLLBACK;
    12.         END IF;
    13.     END LOOP;
    14. END;
    15. /

  • 示例2：

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

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

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

    1. CREATE OR REPLACE PROCEDURE TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK()
    2. AS
    3. BEGIN
    4.  DROP TABLE IF EXISTS TEST_COMMIT; 
    5.  CREATE TABLE TEST_COMMIT(A INT, B INT);
    6.  INSERT INTO TEST_COMMIT SELECT 1, 1;
    7.  COMMIT;
    8.         CREATE TABLE TEST_ROLLBACK(A INT, B INT);
    9.  RAISE EXCEPTION 'RAISE EXCEPTION AFTER COMMIT';
    10. EXCEPTION
    11.     WHEN OTHERS THEN
    12.  INSERT INTO TEST_COMMIT SELECT 2, 2;
    13.  ROLLBACK;
    14. END;
    15. /

  • 示例3：支持在事务块里调用含有COMMIT/ROLLBACK的存储过程，即通过/BEGIN/START/END等开启控制的外部事务。

    1. BEGIN;
    2.     CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK();
    3. END;

  • 示例4：支持多数PLSQL的上下文和语句内调用COMMIT/ROLLBACK，包括常用的IF/FOR/CURSOR LOOP/WHILE。

    1. CREATE OR REPLACE PROCEDURE TEST_COMMIT2()
    2. IS
    3. BEGIN
    4.     DROP TABLE IF EXISTS TEST_COMMIT;
    5.     CREATE TABLE TEST_COMMIT(A INT);
    6.     FOR I IN REVERSE 3..0 LOOP
    7.  INSERT INTO TEST_COMMIT SELECT I;
    8.  COMMIT;
    9.     END LOOP;
    10.     FOR I IN REVERSE 2..4 LOOP
    11.  UPDATE TEST_COMMIT SET A=I;
    12.  COMMIT;
    13.     END LOOP;
    14. EXCEPTION
    15. WHEN OTHERS THEN   
    16.  INSERT INTO TEST_COMMIT SELECT 4;
    17.     COMMIT;
    18. END;
    19. /

  • 示例5：支持存储过程返回值与简单表达式计算。

    1. CREATE OR REPLACE PROCEDURE exec_func3(RET_NUM OUT INT)
    2. AS
    3. BEGIN
    4.     RET_NUM := 1+1;
    5. COMMIT;
    6. END;
    7. /
    8. CREATE OR REPLACE PROCEDURE exec_func4(ADD_NUM IN INT)
    9. AS
    10. SUM_NUM INT;
    11. BEGIN
    12. SUM_NUM := ADD_NUM + exec_func3();
    13. COMMIT;
    14. END;
    15. /

  • 示例6：支持存储过程内GUC参数的回滚提交。

    1. SHOW explain_perf_mode;
    2. SHOW enable_force_vector_engine;
    4. CREATE OR REPLACE PROCEDURE GUC_ROLLBACK()
    5. AS
    6. BEGIN
    7.     SET enable_force_vector_engine = on;
    8.     COMMIT;
    9.     SET explain_perf_mode TO pretty;
    10.     ROLLBACK;
    11. END;
    12. /
    14. call GUC_ROLLBACK();
    15. SHOW explain_perf_mode;
    16. SHOW enable_force_vector_engine;
    17. SET enable_force_vector_engine = off;

  • 示例7：函数（Function）中不允许调用commit/rollback语句，同时不允许函数调用含有commit/rollback的存储过程。

    1. CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE1() RETURN INT
    2. AS
    3. EXP INT;
    4. BEGIN
    5.     FOR i IN 0..20 LOOP
    6.         INSERT INTO EXAMPLE1(col1) VALUES (i);
    7.         IF i % 2 = 0 THEN
    8.             COMMIT;
    9.         ELSE
    10.             ROLLBACK;
    11.         END IF;
    12.     END LOOP;
    13.     SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
    14.     RETURN EXP;
    15. END;
    16. /

  • 示例8：函数（Fucntion）中不允许调用带有commit/rollback语句的存储过程。

    1. CREATE OR REPLACE FUNCTION FUNCTION_EXAMPLE2() RETURN INT
    2. AS
    3. EXP INT;
    4. BEGIN
    5.     --transaction_example为存储过程，带有commit/rollback语句
    6.     CALL transaction_example();
    7.     SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
    8.     RETURN EXP;
    9. END;
    10. /

  • 示例9：不允许Trigger的存储过程包含commit/rollback语句，或调用带有commit/rollback语句的存储过程。

    1. CREATE OR REPLACE FUNCTION FUNCTION_TRI_EXAMPLE2() RETURN TRIGGER
    2. AS
    3. EXP INT;
    4. BEGIN
    5.     FOR i IN 0..20 LOOP
    6.         INSERT INTO EXAMPLE1(col1) VALUES (i);
    7.         IF i % 2 = 0 THEN
    8.             COMMIT;
    9.         ELSE
    10.             ROLLBACK;
    11.         END IF;
    12.     END LOOP;
    13.     SELECT COUNT(*) FROM EXAMPLE1 INTO EXP;
    14. END;
    15. /
    17. CREATE TRIGGER TRIGGER_EXAMPLE AFTER DELETE ON EXAMPLE1 
    18. FOR EACH ROW EXECUTE PROCEDURE FUNCTION_TRI_EXAMPLE2();
    20. DELETE FROM EXAMPLE1;

  • 示例10：不支持带有IMMUABLE以及SHIPPABLE的存储过程调用commit/rollback，或调用带有commit/rollback语句的存储过程。

    1. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE1()
    2. IMMUTABLE
    3. AS
    4. BEGIN
    5.     FOR i IN 0..20 LOOP
    6.         INSERT INTO EXAMPLE1 (col1) VALUES (i);
    7.         IF i % 2 = 0 THEN
    8.             COMMIT;
    9.         ELSE
    10.             ROLLBACK;
    11.         END IF;
    12.     END LOOP;
    13. END;
    14. /

  • 示例12：不支持出现在SQL中的调用（除了Select Procedure）。

    1. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE3()
    2. AS
    3. BEGIN
    4.     FOR i IN 0..20 LOOP
    5.         INSERT INTO EXAMPLE1 (col1) VALUES (i);
    6.         IF i % 2 = 0 THEN
    7.             EXECUTE IMMEDIATE 'COMMIT';
    8.         ELSE
    9.             EXECUTE IMMEDIATE 'ROLLBACK';
    10.         END IF;
    11.     END LOOP;
    12. END;
    13. /

  • 示例13：存储过程头带有GUC参数设置的不允许调用commit/rollback语句。

    1. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE4()
    2. SET ARRAY_NULLS TO "ON"
    3. AS
    4. BEGIN
    5.     FOR i IN 0..20 LOOP
    6.         INSERT INTO EXAMPLE1 (col1) VALUES (i);
    7.         IF i % 2 = 0 THEN
    8.             COMMIT;
    9.         ELSE
    10.             ROLLBACK;
    11.         END IF;
    12.     END LOOP;
    13. END;
    14. /

  • 示例14：游标open的对象不允许为带有commit/rollback语句的存储过程。

    1. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE5(INTIN IN INT, INTOUT OUT INT)
    2. AS
    3. BEGIN
    4. INTOUT := INTIN + 1;
    5. COMMIT;
    6. END;
    7. /
    9. CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE6()
    10. AS
    11. CURSOR CURSOR1(EXPIN INT)
    12. IS SELECT TRANSACTION_EXAMPLE5(EXPIN);
    13. INTEXP INT;
    14. BEGIN
    15.     FOR i IN 0..20 LOOP
    16.         OPEN CURSOR1(i);
    17.         FETCH CURSOR1 INTO INTEXP;
    18.         INSERT INTO EXAMPLE1(COL1) VALUES (INTEXP);
    19.         IF i % 2 = 0 THEN
    20.             COMMIT;
    21.         ELSE
    22.             ROLLBACK;
    23.         END IF;
    24.         CLOSE CURSOR1;
    25.     END LOOP;
    26. END; 
    27. /

  • 示例15：不支持CURSOR/EXECUTE语句，以及各类表达式内调用COMMIT/ROLLBACK。

    1. CREATE OR REPLACE PROCEDURE exec_func1()
    2. AS
    3. BEGIN
    4.     CREATE TABLE TEST_exec(A INT);
    5. COMMIT;
    6. END;
    7. /
    8. CREATE OR REPLACE PROCEDURE exec_func2()
    9. AS
    10. BEGIN
    11. EXECUTE exec_func1();
    12. COMMIT;
    13. END;
    14. /

  • 示例16：存储过程使用保存点回退事务部分修改。

    1. CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE1()
    2. AS
    3. BEGIN
    4.     INSERT INTO EXAMPLE1 VALUES(1);
    5.     SAVEPOINT s1;
    6.     INSERT INTO EXAMPLE1 VALUES(2);
    7.     ROLLBACK TO s1;  -- 回退插入记录2
    8.     INSERT INTO EXAMPLE1 VALUES(3);
    9. END;
    10. /

  • 示例17：存储过程中使用保存点回退到存储过程外部定义的保存点。

    1. CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE2()
    2. AS
    3. BEGIN
    4.     INSERT INTO EXAMPLE1 VALUES(2);
    5.     ROLLBACK TO s1;  -- 回退插入记录2
    6.     INSERT INTO EXAMPLE1 VALUES(3);
    7. END;
    8. /
    10. BEGIN;
    11. INSERT INTO EXAMPLE1 VALUES(1);
    12. SAVEPOINT s1;
    13. CALL STP_SAVEPOINT_EXAMPLE2();
    14. SELECT * FROM EXAMPLE1;
    15. COMMIT;

  • 示例18：不支持存储过程中释放存储过程外部定义的保存点。

    1. CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE3() 
    2. AS 
    3. BEGIN 
    4.     INSERT INTO EXAMPLE1 VALUES(2); 
    5.     RELEASE SAVEPOINT s1; -- 释放存储过程外部定义的保存点 
    6.     INSERT INTO EXAMPLE1 VALUES(3); 
    7. END;
    8. /
    10. BEGIN; 
    11. INSERT INTO EXAMPLE1 VALUES(1); 
    12. SAVEPOINT s1; 
    13. CALL STP_SAVEPOINT_EXAMPLE3(); 
    14. COMMIT;

  • 示例19：存储过程外部SQL/其它存储过程回退到存储过程中定义的保存点。

    1. CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE3()
    2. AS
    3. BEGIN
    4.     INSERT INTO EXAMPLE1 VALUES(1);
    5.     SAVEPOINT s1;
    6.     INSERT INTO EXAMPLE1 VALUES(2);
    7. END;
    8. /
    10. BEGIN;
    11. INSERT INTO EXAMPLE1 VALUES(3);
    12. CALL STP_SAVEPOINT_EXAMPLE3();
    13. ROLLBACK TO SAVEPOINT s1; --回退存储过程中插入记录2
    14. SELECT * FROM EXAMPLE1;
    15. COMMIT;