postgresql事务与oracle中的事务差异

事务

事务ID及回卷

　　参见postgresql中的事务回卷原理及预防措施。

子事务（事务处理：概念与技术 4.7）

 

 子事务具有ACI特性，但是不具有D特性。只会在主事务提交时，才会提交，无法单独提交。pg不支持子事务。

xact

保存点

保存点是不支持子事务/嵌套事务时的折中实现，但它是ANSI SQL标准的一部分。因为管理更加简单（参见事务处理：概念与技术 4.5节）。 

保存点比子事务具有更灵活的可恢复性，可以恢复到任意位置。但是保存点不可以单独提交。

zjh@postgres=# create table table1 (a int);
CREATE TABLE

zjh@postgres=# BEGIN;
BEGIN
zjh@postgres=*#     INSERT INTO table1 VALUES (3);
INSERT 0 1
zjh@postgres=*#     SAVEPOINT my_savepoint;
SAVEPOINT
zjh@postgres=*#     INSERT INTO table1 VALUES (4);
INSERT 0 1
zjh@postgres=*#     RELEASE SAVEPOINT my_savepoint;
RELEASE
zjh@postgres=*# select * from table1;
 a 
---
 3
 4
(2 rows)

zjh@postgres=*# rollback;
ROLLBACK
zjh@postgres=# select * from table1;
 a 
---
(0 rows)

zjh@postgres=# BEGIN;
BEGIN
zjh@postgres=*#     INSERT INTO table1 VALUES (4);
INSERT 0 1
zjh@postgres=*# BEGIN;
WARNING:  there is already a transaction in progress
BEGIN

 pg中的事务行为

create table test_trans(id int, v int);
insert into test_trans values(1,1);
insert into test_trans values(2,2);
insert into test_trans values(3,3);
insert into test_trans values(4,4);

-- 测试事务里面走plsql块，plsql块里面正常回滚
begin;
do $$

begin
delete from test_trans where id = 1;
rollback;

END$$;

-- 报“invalidtransaction termination”    如果CALL在事务块中执行，则被调用的存储过程无法执行事务控制语句（也就是commit/rollback）等TCL语句。只有CALL在自己事务中执行时，才允许事务控制语句。而我们使用python程序模块psycopg连接的时候，通常是以begin开始运行的，这就代表了CALL在事务块中运行，是没办法在存储过程中执行commit的。
select * from test_trans;
commit;

-- 测试事务里面走plsql块，plsql块里面正常提交
begin;
do $$

begin
delete from test_trans where id = 1;
rollback;

END$$;
-- ERROR:  invalid transaction termination
select * from test_trans;
commit;

-- 测试事务里面走plsql块，plsql块里面无事务控制提交
begin;
do $$

begin
delete from test_trans where id = 1;

END$$;
select * from test_trans;
rollback;

-- 测试事务里面走plsql块，plsql块里面无事务控制回滚
begin;
do $$

begin
delete from test_trans where id = 1;

END$$;
select * from test_trans;
commit;
insert into test_trans values(1,1);

-- 测试事务里面走plsql块，plsql块exception里面回滚
begin;
do $$
declare i int;
begin
delete from test_trans where id = 1;
i:=1/0;  -- i=1/0; 也行
exception when others then
   rollback;
END$$;
-- 同上面，报无效的事务终止
select * from test_trans;
commit;

-- 测试事务里面走plsql块，plsql块exception里面提交
begin;
do $$
declare i int;
begin
delete from test_trans where id = 1;
i:=1/0;
exception when others then
   commit;
END$$;
-- 同上面，报无效的事务终止
select * from test_trans;
rollback;

-- 测试存储过程的上述主块和异常控制行为
create or replace procedure sp_test_trans()
language plpgsql    
as $$
begin
    delete from test_trans where id = 1;
    commit;
end $$;

lightdb@postgres=# begin;
BEGIN
lightdb@postgres=*# call sp_test_trans ;
ERROR:  invalid transaction termination    -- 存储过程和事务都一样，不允许嵌套在事务中
CONTEXT:  PL/pgSQL function sp_test_trans() line 4 at COMMIT


-- 测试函数的上述主块和异常控制行为
create or replace function fn_test_trans() returns void
language plpgsql    
as $$
begin
    delete from test_trans where id = 1;
    commit;
end $$;
-- 因为select会开启隐式事务，所以在事务中了，所以函数本质上和存储过程一样（不存在存储过程中允许事务一说），是 call调用所致的差别
lightdb@postgres=# select * from fn_test_trans();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function fn_test_trans() line 4 at COMMIT

-- 纯粹的pl/sql块嵌套，子块里面有事务
do $$
declare i int;
begin
    delete from test_trans where id = 2;
    begin
        delete from test_trans where id = 1;
        i:=1/0;  -- i=1/0; 也行
    exception when others then
       rollback;   -- 不只是回滚子块，回滚了整个块
       -- 这里再加DML是什么行为？
    END;
    commit;
END $$;

-- 存储过程调用函数，里面带事务
create or replace procedure sp_test_call_func()
language plpgsql    
as $$
begin
    delete from test_trans where id = 1;
    commit;
    perform fn_test_trans();
end $$;

-- 因为已经在事务中，所以perform调用失败

上述语句oracle中的行为

dbeaver默认自动提交，所以去掉了自动提交。

 

create table test_trans(id int, v int);
insert into test_trans values(1,1);
insert into test_trans values(2,2);
insert into test_trans values(3,3);
insert into test_trans values(4,4);

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

begin
delete from test_trans where id = 1;
rollback;
END;
-- 已经被回滚了，有咩有commit无所谓
COMMIT;

declare i int;
begin
delete from test_trans where id = 1;
i:=1/0;  
exception when others then
   rollback;  -- 已经被回滚了
END;
/  -- dbeaver去掉 /
SELECT * FROM test_trans;

declare i int;
begin
delete from test_trans where id = 1;
i:=1/0;  
exception when others then
   commit;   -- 还在同一个事务，1被删了
END;
/  -- dbeaver去掉 /

SELECT * FROM test_trans;

INSERT into test_trans values(1,1);
SELECT * FROM test_trans;
create or replace procedure sp_test_trans
is
begin
    delete from test_trans where id = 1;
    commit;
end;
/  -- dbeaver去掉 /

-- 测试存储过程提交
DELETE FROM test_trans WHERE id = 4;

CALL sp_test_trans();
-- delete from id = 4也提交了。

SELECT * FROM test_trans;
TRUNCATE TABLE test_trans;
insert into test_trans values(1,1);
insert into test_trans values(2,2);
insert into test_trans values(3,3);
insert into test_trans values(4,4);
COMMIT;

declare i int;
begin
    delete from test_trans where id = 1;

    begin
      delete from test_trans where id = 2;
      i:=1/0;  -- i=1/0; 也行
    
    exception when others then
       commit;   -- 还在同一个事务，2被删了
    END;
exception when others then
   ROLLBACK;   -- 还在同一个事务，1被删了
END;
/  -- dbeaver去掉 /
SELECT * FROM test_trans;

事务行为差异总结

　　如果驱动或客户端不设置，oracle默认在DML后自动开启事务，且不自动提交。在select后自动提交。这是由内核控制的。

　　在pg中，如果不在一个事务中，默认是会自动开启一个事务、自动提交的。自动开启没问题，但是自动提交需要改成非自动（这是第一个变化点）。

 　　pg多语句在一个事务需要显示开始，oracle则不用显示开启。所以第二个点是如果一批语句，第一个DML开启的地方才是真正的事务，之前的select全部自动提交，不管在不在存储过程、不管有没有异常。

　　第三个点是pg没有见到commit/ROLLBACK，都不结束事务（需要确认pg里面commit是客户端、驱动干的还是内核干的）。第四个点是oracle如果发生了异常，比如1/0，只是报错，不改变事务的状态。

　　oracle语句块嵌套、函数调用函数、函数调用过程、过程调用过程：子块和事务没有关系，一路平行到底，也就是代码怎么写和事务没关系，除了明确的savepoint、COMMIT、ROLLBACK。

 　　最后，plpgsql块本身就是个savepoint(跟这里的描述是一致的，https://dev.to/aws-heroes/exceptions-and-commit-in-postgresql-plpgsql-vs-oracle-plsql-1nk8)，有异常的时候整个rollback，所以要跟Oracle一致，那就是每个语句前都需要加个savepoint。exception发生的时候，回滚到出错前那一个savepoint（这种做法是比较差的）。

正确的做法是，语句级回滚。当做语句的状态正常出错即可。用户爱回滚回滚、爱提交提交。

 

　　-- savepoint的差异，没有差异。V$TRANSACTION查看进行中的事务（pg暂无对应，实现也简单的，主要是返回事务id和最新执行的语句）。https://www.modb.pro/db/51691 查看Oracle事务行为，还有一个Transaction Guard，12c新增，用户给终端更好的体验。

参考：

　　https://www.postgresql.org/docs/11/plpgsql-transactions.html

 https://franckpachot.medium.com/postgresql-subtransactions-savepoints-and-exception-blocks-67e0fbd412af

子事务的问题：https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/

oracle自治事务

http://www.nyoug.org/Presentations/2002/fire_forget.pdf，lightdb的自治事务行为和oracle一致，百度/必应搜索引擎搜索"lightdb 自治事务"即可。