1、数据库事务原子性的局限与自治事务
1.1、事务原子性概述
如下所示，如果一个事务中有A和B两个数据的插入操作，当A的SQL语句执行成功后，因为某种原因（如服务器断电等），导致B的SQL语句执行失败，待数据库服务恢复正常后，数据库将进行回滚操作，此时即使A的SQL执行成功，也会连带一并撤销，回到事务执行之前的状态：
create table t(msg varchar(27));
commit;
insert into t values('string1');
insert into t values('string2');
rollback;
上述SQL在Oracle Database 19c中的执行效果如下图：
1.2、Oracle数据库下自治事务使用场景
如1.1的SQL所示，若此时有个需求，是在关闭自动提交或不在每个SQL后手动commit的前提下，通过rollback语句，仅回滚最近执行的一个insert语句，而保留其他insert语句的执行结果，这个时候就需要用到自治事务。
自治事务并不是所有的数据库都支持，当下用得比较多的场景是在Oracle数据库下使用自治事务，针对本文1.1章节的SQL语句，我们可以改写为如下形式，使用Oracle的自治事务：
create table t ( msg varchar(27) );
commit;
create or replace procedure Autonomous_Insert
as
pragma autonomous_transaction;
begin
insert into t values ( 'string2' );
rollback;
end;
/
begin
insert into t values ( 'string1' );
Autonomous_Insert;
commit;
end;
/
select * from t;
上述SQL在Oracle Database 19c中的执行效果如下图：
2、通过dblink实现PostgreSQL下的自治事务
PostgreSQL原本并不像Oracle数据库那样支持自治事务，为了实现自治事务的功能，PostgreSQL需要额外安装扩展插件dblink，通过dblink的跨库查询功能间接实现自治事务。
2.1、PostgreSQL下的dblink扩展概述
PostgreSQL本身并不支持像MariaDB和SQL Server等数据库那样，直接在SQL上进行跨库查询，因此如下图所示的，在MariaDB的跨库查询的SQL语法在PostgreSQL是执行不成功的：
图中的语法在PostgreSQL中是不支持的。
为弥补PostgreSQL在跨库查询的局限性，PostgreSQL需要额外安装dblink扩展，通过dblink实现跨库查询可见2.2章节，跨库查询的语法和自治事务语法相同，只是dblink_connect函数中的，代表数据库名称的dbname参数不同而已，若是跨服务器的话，还涉及到代表IP地址的host参数的不同。
; 2.2、PostgreSQL下通过dblink实现自治事务
在本文1.1章节的两个insert语句的场景下，在PostgreSQL实现自治事务的SQL代码如下：
create database yanmuhuan_test;
\c yanmuhuan_test;
create extension dblink;
\set AUTOCOMMIT off
create table t(msg varchar(27));
commit;
create or replace function Autonomous_Insert(v_description varchar)
returns void
as
$BODY$
declare
v_sql text;
begin
PERFORM dblink_connect('dbname=yanmuhuan_test user=postgres password=yanmuhuan1997114 host=127.0.0.1 port=5432');
v_sql := format('INSERT INTO t VALUES(%L)',v_description);
PERFORM dblink_exec(v_sql);
PERFORM dblink_disconnect();
end;
$BODY$
LANGUAGE plpgsql;
commit;
select Autonomous_Insert('string1');
insert into t values('string2');
rollback;
select * from t;
commit;
上述SQL代码在PostgreSQL 13中执行情况如下图：
3、Oracle数据库与PostgreSQL自治事务思路和SQL逻辑的区别
Oracle数据库和PostgreSQL在自治事务的语法逻辑的实现上，是相反的，如下图所示：
造成这样的结果在于Oracle和PostgreSQL的特性不同，相关特性如下：
- Oracle数据库的会话在建立时，自动提交（隐式提交）默认关闭，因此Oracle数据库中，自治事务必须在存储过程内进行显式提交或回滚（即必须手动写入commit或rollback），所以若存储过程内没有手动写入commit或rollback进行显式提交，将会报”ORA-06519:检测到活动的独立的事务处理,
已经回退”错误； - PostgreSQL的会话在建立时，自动提交（隐式提交）默认打开，而PostgreSQL自治事务是由存储过程中的dblink实现的，相当于在一个SQL交互会话中再新建立一个临时会话，因此在存储过程中手动写入commit或rollback是没有意义且无效的，所以commit和rollback只能在关闭自动提交（隐式提交）的前提下于存储过程外进行。
Original: https://blog.csdn.net/muxia_jhy/article/details/128280884
Author: 木下-俱欢颜
Title: 在PostgreSQL中通过dblink兼容Oracle数据库中的自治事务
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/806445/
转载文章受原作者版权保护。转载请注明原作者出处！