事务
事务ID及回卷
子事务（事务处理：概念与技术 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
from test_trans where id = 1;
i:=1/0; -- i=1/0; 也行
exception when others then
rollback; -- 不只是回滚子块，回滚了整个块
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
oracle自治事务
http://www.nyoug.org/Presentations/2002/fire_forget.pdf，lightdb的自治事务行为和oracle一致，百度/必应搜索引擎搜索"lightdb 自治事务"即可。
标签：事务,postgresql,--,begin,test,oracle,trans,id From： https://www.cnblogs.com/lightdb/p/14870386.html