本文比较了在存储过程中事务管理上Oracle和PG的异同. **Oracle** 测试脚本: ``` -- drop table if exists t1; -- create table t1(id int); set serveroutput on declare begin delete from t1; insert into t1 values(1); begin insert into t1 values (2); commit; insert into t1 values ('a'); exception when others then dbms_output.put_line('ERROR：'||SQLERRM); insert into t1 values(3); dbms_output.put_line('INSERT：3'); end; insert into t1 values(4); dbms_output.put_line('INSERT：4'); commit; end; / select * from t1; ``` 执行结果: ``` SQL> set serveroutput on SQL> SQL> declare 2 begin 3 delete from t1; 4 insert into t1 values(1); 5 begin 6 insert into t1 values (2); 7 commit; 8 insert into t1 values ('a'); 9 exception when others then 10 dbms_output.put_line('ERROR：'||SQLERRM); 11 insert into t1 values(3); 12 dbms_output.put_line('INSERT：3'); 13 end; 14 insert into t1 values(4); 15 dbms_output.put_line('INSERT：4'); 16 commit; 17 end; 18 / ERROR：ORA-01722: 无效数字 INSERT：3 INSERT：4 PL/SQL 过程已成功完成。 SQL> SQL> select * from t1; ID ---------- 1 2 3 4 SQL> ``` 执行完成后，t1表中有4行记录。 **PostgreSQL** 测试脚本： ``` -- drop table if exists t1; -- create table t1(id int); do $$ declare begin delete from t1; insert into t1 values(1); begin insert into t1 values (2); commit; insert into t1 values ('a'); exception when others then raise notice 'ERROR：%',SQLERRM; insert into t1 values(3); raise notice 'INSERT：%',3; end; insert into t1 values(4); raise notice 'INSERT：%',4; commit; end; $$ ; select * from t1; ``` 执行结果： ``` testdb=# do $$ testdb$# declare testdb$# begin testdb$# delete from t1; testdb$# insert into t1 values(1); testdb$# begin testdb$# insert into t1 values (2); testdb$# commit; testdb$# insert into t1 values ('a'); testdb$# exception when others then testdb$# raise notice 'ERROR：%',SQLERRM; testdb$# insert into t1 values(3); testdb$# raise notice 'INSERT：%',3; testdb$# end; testdb$# insert into t1 values(4); testdb$# raise notice 'INSERT：%',4; testdb$# commit; testdb$# end; testdb$# $$ testdb-# ; NOTICE: ERROR：cannot commit while a subtransaction is active NOTICE: INSERT：3 NOTICE: INSERT：4 DO testdb=# testdb=# select * from t1; id ---- 1 3 4 (3 rows) ``` 在PG中，执行结果只有3行，原因是在内层的begin/end中的commit出现异常（cannot commit while a subtransaction is active），值2没有插入到数据表中。 关于子事务的进一步信息，可参见参考资料中的链接。 **参考资料** [PostgreSQL Subtransactions Considered Harmful](https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful) [Subtransactions and performance in PostgreSQL](https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/)