Compare dblink module Within One Transaction in PgSQL,EDB,Oracle

digoal

测试版本:
PostgreSQL 9.0.3
EnterpriseDB ADVPlus 8.3R2
Oracle 10.2.0.4
RHEL 5 x86_64

PostgreSQL示例 ：
在 PostgreSQL的存储函数中不支持自治事务，啥意思呢？也就是说一个存储过程里的SQL被认为是一个事务单元来处理。要么全部成功要么全部失败。有两个例外：dblink和exception 。each BEGIN/EXCEPTION/END block creates a subtransaction.
下面看看PostgreSQL中dblink在事务中的使用.(实际上PostgreSQL中使用dblink和本地事务是隔离的).
例一:
digoal=> select now();begin;select pg_sleep(3);select now();select pg_sleep(3);select now();end;
              now            
-------------------------------
2011-02-14 10:29:34.924432+08
(1 row)

BEGIN
pg_sleep
----------

(1 row)

              now            
-------------------------------
2011-02-14 10:29:34.924645+08
(1 row)

pg_sleep
----------

(1 row)

              now            
-------------------------------
2011-02-14 10:29:34.924645+08
(1 row)

COMMIT
now()取到的是事务开始的系统时间.
换到dblink下取这个时间看看是什么情况,
select * from dblink_connect('lk_test','hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal');
digoal=> begin;
BEGIN
digoal=> select * from dblink('lk_test','select now()') as t(v_time timestamp with time zone);
            v_time            
-------------------------------
2011-02-14 10:36:17.582965+08
(1 row)

digoal=> select * from dblink('lk_test','select now()') as t(v_time timestamp with time zone);
            v_time            
-------------------------------
2011-02-14 10:36:23.939499+08
(1 row)

digoal=> end;
从 now() 函数的返回推测dblink与本地事务隔离，属于自治事务.
换个例子更能说明情况.
digoal=> begin;
BEGIN
digoal=> select * from dblink_exec('lk_test','begin;insert into tbl_user (id) values(1),(2);commit;');
dblink_exec
-------------
COMMIT
(1 row)

digoal=> insert into tbl_user (id) values(3);
ERROR:  duplicate key value violates unique constraint "tbl_user_pkey"
DETAIL:  Key (id)=(3) already exists.
digoal=> end;
ROLLBACK
digoal=> select * from dblink('lk_test','select id from tbl_user where id in (1,2)') as t (id int);
id
----
  1
  2
(2 rows)
远程执行成功，本地执行失败，事务回滚，但是远程已经提交，因此有DBLINK的时候PG采用这种简单的办法不能确保事务完整性。（但是在ORACLE中采用2PC 处理可以确保远程与本地事务的完整性）
下面的例子是完全的本地事务，可以确保事务完整性。
digoal=> delete from tbl_user where id=3;
DELETE 1
digoal=> begin;
BEGIN
digoal=> insert into tbl_user (id) values(3);
INSERT 0 1
digoal=> insert into tbl_user (id) values(2);
ERROR:  duplicate key value violates unique constraint "tbl_user_pkey"
DETAIL:  Key (id)=(2) already exists.
digoal=> end;
ROLLBACK
digoal=> select id from tbl_user where id=3;
id
----
(0 rows)

再来一个示例,
HOST A:
digoal=> begin;
digoal=> select * from dblink_exec('lk_test','begin;insert into tbl_user (id) values(1),(2);commit;');
dblink_exec
-------------
COMMIT
(1 row)

HOST B:
select id from tbl_user where id in (1,2)
id
----
  1
  2
(2 rows)

HOST A:
ROLLBACK;

HOST B:
select id from tbl_user where id in (1,2)
id
----
  1
  2
(2 rows)

Oracle示例：
HOST A:
SQL> insert into tbl_test@lk_test values(1,'zhou','digoal','sky-mobi','28',sysdate,'abc');

1 row created.
HOST B:
SQL> select * from tbl_test where id=1;

no rows selected
HOST A:
commit;
HOST B:
SQL> select * from tbl_test where id=1;

        ID FIRSTNAME                        LASTNAME                         CORP                                    AGE CREATE_TIME
---------- -------------------------------- -------------------------------- -------------------------------- ---------- -------------------
INFO
--------------------------------------------------------------------------------------------------------------------------------
         1 zhou                             digoal                           sky-mobi                                 28 2011-02-14 11:31:32
abc

Oracle示例2：
HOST A:
SQL> insert into tbl_test@lk_test values(1,'zhou','digoal','sky-mobi','28',sysdate,'abc');

1 row created.

SQL> insert into tbl_test values('ab','zhou','digoal','sky-mobi','28',sysdate,'abc');
insert into tbl_test values('ab','zhou','digoal','sky-mobi','28',sysdate,'abc')
                            *
ERROR at line 1:
ORA-01722: invalid number


SQL> rollback;

Rollback complete.
HOST B:
SQL> select * from tbl_test where id=1;

no rows selected

EnterpriseDB示例：
enterprisedb和postgreSQL差不多，只不过提供了create database link的命令接口.
edb=# \h create database link
Command:     CREATE DATABASE LINK
Description: create a new database link
Syntax:
CREATE [PUBLIC] DATABASE LINK name
    CONNECT TO username
    IDENTIFIED BY password
    USING [ "oci" | "libpq" ] 'connection_string'

EnterpriseDB -> Oracle 示例
HOST A edb:
edb=# begin;
edb=# insert into tbl_user@edb.xl_local values(1,'zhou','digoal');
INSERT 0 1

HOST B oracle:

SQL> select * from msss.tbl_user;

        ID FIRSTNAME             LASTNAME
---------- --------------------- --------------------------------
         1 zhou                  digoal

HOST A:
rollback;

HOST B:

SQL> select * from msss.tbl_user;

        ID FIRSTNAME             LASTNAME
---------- --------------------- --------------------------------
         1 zhou                  digoal
没有回滚.

EnterpriseDB -> PostgreSQL 示例
HOST A: EDB
edb=# begin;
edb=# insert into tbl_user@lk_test (id) values (9999999);
INSERT 0 1
HOST B: POSTGRESQL
digoal=> select * from tbl_user where id=99999999;
    id    | firstname | lastname | corp | age
----------+-----------+----------+------+-----
99999999 |           |          |      |   
(1 row)

HOST A:
rollback;

HOST B:
digoal=> select * from tbl_user where id=99999999;
    id    | firstname | lastname | corp | age
----------+-----------+----------+------+-----
99999999 |           |          |      |   
(1 row)
没有回滚.

在PostgreSQL和EnterpriseDB中，使用prepared transaction可以解决2PC的问题,达到和Oracle类似甚至更强大的效果.下篇博客再说Prepared Transaction的问题.