logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

事务管理

功能描述

存储过程本身就处于一个事务中，开始调用最外围存储过程时会自动开启一个事务，在调用结束时自动提交或者发生异常时回滚。除了系统自动的事务控制外，也可以使用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参数的回滚提交。

1、查询参数值。

SHOW explain_perf_mode;
SHOW enable_force_vector_engine;

2、创建存储过程修改参数。

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

3、调用存储过程并查询。

call GUC_ROLLBACK();
SHOW explain_perf_mode;
SHOW enable_force_vector_engine;

结果显示如下：

 explain_perf_mode 
-------------------
 normal
(1 row)

enable_force_vector_engine 
----------------------------
 on
(1 row)

4、将参数改回。

SET enable_force_vector_engine = off;

示例7

函数（Function）中不允许调用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 TABLE EXAMPLE1(COL1 INT);
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

不支持出现在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;
/

示例12

存储过程头带有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;
/

示例13

游标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; 
/

示例14

不支持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;
/

示例15

存储过程使用保存点回退事务部分修改。

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

示例16

存储过程中使用保存点回退到存储过程外部定义的保存点。

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;

返回结果显示为：

 stp_savepoint_example2
------------------------

(1 row)

SELECT * FROM EXAMPLE1;
 col1
------
    1
    3
(2 rows)

示例17

存储过程外部回退到存储过程中定义的保存点。

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;

返回结果显示为：

 stp_savepoint_example3
------------------------

(1 row)

ROLLBACK

 col1
------
    1
    3
    3
    1
(4 rows)

示例18

不支持存储过程中释放存储过程外部定义的保存点。

CREATE OR REPLACE PROCEDURE STP_SAVEPOINT_EXAMPLE3() 
AS 
BEGIN 
    INSERT INTO EXAMPLE1 VALUES(2); 
    RELEASE SAVEPOINT s1; -- 释放存储过程外部定义的保存点 
    INSERT INTO EXAMPLE1 VALUES(3); 
END;
/

BEGIN; 
INSERT INTO EXAMPLE1 VALUES(1); 
SAVEPOINT s1; 
CALL STP_SAVEPOINT_EXAMPLE3(); 
COMMIT;