自律型トランザクション

• Oracle

(12R1)

drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 int);
create table tab2(col1 int);

create or replace procedure proc1 as
pragma autonomous_transaction;
begin
insert into tab2 values(1);
commit;
end;
/

select * from tab1;
select * from tab2;

declare
begin

insert into tab1 values(1);
proc1();
rollback;
end;
/

select * from tab1;
select * from tab2;

• MySQL

(5.6)

https://planet.mysql.com/entry/?id=20634

MyISAMを使用する

drop table tab1 ;
drop table tab2 ;

create table tab1(col1 int);
create table tab2(col1 int) engine=MyISAM;

select * from tab1;
select * from tab2;

start transaction;
insert into tab1 values(1);
insert into tab2 values(1);
rollback;

select * from tab1;
select * from tab2;

• PostgreSQL

(9.4)

https://aws.amazon.com/jp/blogs/news/migrating-oracle-autonomous-transactions-to-postgresql/

dblinkを使用する

create extension dblink;
\dx

CREATE SERVER loopback_dblink FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (hostaddr '127.0.0.1', dbname 'test');

CREATE USER MAPPING FOR postgres SERVER loopback_dblink
OPTIONS (user 'postgres', password 'postgres');

\des
\deu

drop table tab1 ;
drop table tab2 ;

create table tab1(col1 int);
create table tab2(col1 int);

CREATE OR REPLACE FUNCTION func1() RETURNS void AS $$
BEGIN
PERFORM dblink_connect('myconn', 'loopback_dblink');
PERFORM dblink_exec('myconn', 'insert into tab2 values(1)');
PERFORM dblink_disconnect('myconn');
END;
$$ LANGUAGE plpgsql;

select * from tab1;
select * from tab2;

start transaction;
insert into tab1 values(1);
select func1();
rollback;

select * from tab1;
select * from tab2;

• SQL Server

(2014)

自律型トランザクションは未サポート

※試したかぎり、自分自身へのリンクサーバは設定不可