Postgresql中plpgsql事务管理实例（commit/rollback）

总结

• commit与rollback都会主动把之前的事务结束掉，然后再自动开启新事务。知道这一点，后面所有用例的行为就都很好理解了。
• commit与rollback会主动关闭游标，例外是for循环中的commit/rollback会把游标转换为hold状态，循环内部可以一直保持open状态，直到循环结束。
• commit and chain与rollback and chain语法会使用与前一个事务相同的事务特征：
  • XactIsoLevel：隔离级别
  • XactReadOnly：只读事务
  • XactDeferrable：可串行化级别下的延迟配置

用例

1 显示回滚事务：与普通事务行为一致

代码语言：javascript复制

drop table test1;
create table test1(a int);

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();

-- 结果
select * from test1;
 a 
---
 0
 2
 4
 6
 8

2 显示提交/回滚事务：与普通事务行为一致

代码语言：javascript复制

drop table test1;
create table test1(a int);

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (1);
    INSERT INTO test1 (a) VALUES (2);
    commit;
    INSERT INTO test1 (a) VALUES (3);
    rollback;
    INSERT INTO test1 (a) VALUES (4);
    commit;
END;
$$;

CALL transaction_test2();

-- 结果
select * from test1;
 a 
---
 1
 2
 4

3 commit后程序报错：commit的数据存在

代码语言：javascript复制

drop table test1;
create table test1(a int);

CREATE PROCEDURE transaction_test3()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (1);
    INSERT INTO test1 (a) VALUES (2);
    commit;
    INSERT INTO test1 (a) VALUES (3);
    rollback;
    raise division_by_zero;
END;
$$;

CALL transaction_test3();

-- 结果
select * from test1;
 a 
---
 1
 2
 4

4 无commit后程序报错：数据全部回滚

代码语言：javascript复制

drop table test1;
create table test1(a int);

CREATE PROCEDURE transaction_test4()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (1);
    INSERT INTO test1 (a) VALUES (2);
    INSERT INTO test1 (a) VALUES (3);
    raise division_by_zero;
END;
$$;

CALL transaction_test4();

-- 结果
select * from test1;
 a 
---
 1
 2
 4

5 嵌套调用【内层提交】【外层回滚】：内外层数据全部存在

对于事务系统来说，内层函数、外层函数都在一个事务中，内层提交就等于把事务提交了，所以外层数据也在。

代码语言：javascript复制

drop table test1;
create table test1(a int);

CREATE PROCEDURE transaction_test51()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (2);
    commit;
END;
$$;


CREATE PROCEDURE transaction_test5()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (1);
    call transaction_test51();
    rollback;
END;
$$;

CALL transaction_test5();

-- 结果
select * from test1;
 a 
---
 1
 2
 4

6 嵌套调用【内层提交】【外层回滚】：内外层数据都不存在

代码语言：javascript复制

drop table test1;
create table test1(a int);

CREATE PROCEDURE transaction_test61()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (2);
    rollback;
END;
$$;


CREATE PROCEDURE transaction_test6()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (1);
    call transaction_test61();
    commit;
END;
$$;

CALL transaction_test6();

-- 结果
select * from test1;
 a 
---

7 嵌套调用proc->func：func内不能使用事务控制语句

代码语言：javascript复制

drop table test1;
create table test1(a int);

CREATE or replace FUNCTION transaction_test71()
RETURNS BOOLEAN LANGUAGE plpgsql  
AS $$
BEGIN
    INSERT INTO test1 (a) VALUES (2);
    commit;
    return true;
END;
$$;

select transaction_test71();

-- 结果
postgres=# select transaction_test71();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function transaction_test71() line 4 at COMMIT

游标用例

1 open游标后commit：游标恢复unopen状态，使用失败

代码语言：javascript复制

drop table tf1;
create table tf1(c1 int, c2 int,  c3 varchar(32), c4 varchar(32), c5 int);
insert into tf1 values(1,1000, 'China','Dalian', 23000);
insert into tf1 values(2,4000, 'Janpan', 'Tokio', 45000);
insert into tf1 values(3,1500, 'China', 'Xian', 25000);
insert into tf1 values(4,300, 'China', 'Changsha', 24000);
insert into tf1 values(5,400,'USA','New York', 35000);
insert into tf1 values(6,5000, 'USA', 'Bostom', 15000);

postgres=# select * from tf1;
 c1 |  c2  |   c3   |    c4    |  c5   
---- ------ -------- ---------- -------
  1 | 1000 | China  | Dalian   | 23000
  2 | 4000 | Janpan | Tokio    | 45000
  3 | 1500 | China  | Xian     | 25000
  4 |  300 | China  | Changsha | 24000
  5 |  400 | USA    | New York | 35000
  6 | 5000 | USA    | Bostom   | 15000
  

CREATE  OR REPLACE PROCEDURE transaction_cur_test1()
AS $$
DECLARE
    curs1 refcursor;                       
    curs2 CURSOR FOR SELECT c1 FROM tf1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tf1 WHERE c1 > key;
    
    x int;
    y tf1%ROWTYPE;
BEGIN
    open curs1 FOR SELECT * FROM tf1 WHERE c1 > 3;
    RAISE NOTICE '================== curs1 =================';
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
    commit; 
    RAISE NOTICE '------------------ commit------------------';
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3; 
END;
$$ LANGUAGE plpgsql;

call transaction_cur_test1();

-- 结果
postgres=# call transaction_cur_test1();
NOTICE:  ================== curs1 =================
NOTICE:  curs1 : China
NOTICE:  curs1 : USA
NOTICE:  ---------------curs1 commit---------------
ERROR:  cursor "<unnamed portal 5>" does not exist
CONTEXT:  PL/pgSQL function transaction_cur_test1() line 16 at FETCH

2 open游标后rollback：游标恢复unopen状态，使用失败

代码语言：javascript复制

drop table tf1;
create table tf1(c1 int, c2 int,  c3 varchar(32), c4 varchar(32), c5 int);
insert into tf1 values(1,1000, 'China','Dalian', 23000);
insert into tf1 values(2,4000, 'Janpan', 'Tokio', 45000);
insert into tf1 values(3,1500, 'China', 'Xian', 25000);
insert into tf1 values(4,300, 'China', 'Changsha', 24000);
insert into tf1 values(5,400,'USA','New York', 35000);
insert into tf1 values(6,5000, 'USA', 'Bostom', 15000);

postgres=# select * from tf1;
 c1 |  c2  |   c3   |    c4    |  c5   
---- ------ -------- ---------- -------
  1 | 1000 | China  | Dalian   | 23000
  2 | 4000 | Janpan | Tokio    | 45000
  3 | 1500 | China  | Xian     | 25000
  4 |  300 | China  | Changsha | 24000
  5 |  400 | USA    | New York | 35000
  6 | 5000 | USA    | Bostom   | 15000
  

CREATE  OR REPLACE PROCEDURE transaction_cur_test2()
AS $$
DECLARE
    curs1 refcursor;                       
    curs2 CURSOR FOR SELECT c1 FROM tf1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tf1 WHERE c1 > key;
    
    x int;
    y tf1%ROWTYPE;
BEGIN
    open curs2;
    RAISE NOTICE '================== curs2 =================';
    fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
    fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
    RAISE NOTICE '------------------ rollback --------------';
    rollback;
    fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
    fetch curs2 into x; RAISE NOTICE 'curs2 : %', x;
    
END;
$$ LANGUAGE plpgsql;

call transaction_cur_test2();

-- 结果
postgres=# call transaction_cur_test2();
NOTICE:  ================== curs2 =================
NOTICE:  curs2 : 1
NOTICE:  curs2 : 2
NOTICE:  ------------------ rollback --------------
ERROR:  cursor "curs2" does not exist
CONTEXT:  PL/pgSQL function transaction_cur_test2() line 16 at FETCH

3 open游标后commit后open：游标重新打开正常使用

代码语言：javascript复制

drop table tf1;
create table tf1(c1 int, c2 int,  c3 varchar(32), c4 varchar(32), c5 int);
insert into tf1 values(1,1000, 'China','Dalian', 23000);
insert into tf1 values(2,4000, 'Janpan', 'Tokio', 45000);
insert into tf1 values(3,1500, 'China', 'Xian', 25000);
insert into tf1 values(4,300, 'China', 'Changsha', 24000);
insert into tf1 values(5,400,'USA','New York', 35000);
insert into tf1 values(6,5000, 'USA', 'Bostom', 15000);

postgres=# select * from tf1;
 c1 |  c2  |   c3   |    c4    |  c5   
---- ------ -------- ---------- -------
  1 | 1000 | China  | Dalian   | 23000
  2 | 4000 | Janpan | Tokio    | 45000
  3 | 1500 | China  | Xian     | 25000
  4 |  300 | China  | Changsha | 24000
  5 |  400 | USA    | New York | 35000
  6 | 5000 | USA    | Bostom   | 15000
  

CREATE  OR REPLACE PROCEDURE transaction_cur_test3()
AS $$
DECLARE
    curs1 refcursor;                       
    curs2 CURSOR FOR SELECT c1 FROM tf1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tf1 WHERE c1 > key;
    
    x int;
    y tf1%ROWTYPE;
BEGIN
    open curs1 FOR SELECT * FROM tf1 WHERE c1 > 3;
    RAISE NOTICE '================== curs1 =================';
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
    commit; 
    RAISE NOTICE '------------------ commit------------------';
    open curs1 FOR SELECT * FROM tf1 WHERE c1 > 3;
    RAISE NOTICE '-------------- curs1 reopen----------------';
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3;
    fetch curs1 into y; RAISE NOTICE 'curs1 : %', y.c3; 
END;
$$ LANGUAGE plpgsql;

call transaction_cur_test3();

-- 结果
postgres=# call transaction_cur_test3();
NOTICE:  ================== curs1 =================
NOTICE:  curs1 : China
NOTICE:  curs1 : USA
NOTICE:  ------------------ commit------------------
NOTICE:  -------------- curs1 reopen----------------
NOTICE:  curs1 : China
NOTICE:  curs1 : USA
CALL

4 循环体内commit：游标转换为hold状态直到循环结束

代码语言：javascript复制

drop table test1;
drop table test2;
create table test1(a int);
create table test2(x int);
insert into test2 values (1),(2);

CREATE PROCEDURE transaction_cur_test4()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        COMMIT;
    END LOOP;
END;
$$;

CALL transaction_cur_test4();

-- 结果
postgres=# select * from test1;
 a 
---
 1
 2

uml

0 人点赞