PostgreSQL's two-phase commit used with dblink example-阿里云开发者社区

我在前面一篇博客有提到PostgreSQL的事务中如果混合了本地SQL和远程执行的SQL的话，可能会导致事务不完整。
那么怎么来解决这种混合事务的完整性问题呢?没错，prepare transaction为我们提供了强大而有弹性的two-phase commit功能。
下面来举例说明一下:
测试环境：
PostgreSQL 9.0.3
TABLE :

digoal=> \d tbl_user
                                  Table "digoal.tbl_user"
  Column   |         Type          |                       Modifiers                       
-----------+-----------------------+-------------------------------------------------------
 id        | bigint                | not null default nextval('tbl_user_id_seq'::regclass)
 firstname | character varying(32) | 
 lastname  | character varying(32) | 
 corp      | character varying(32) | 
 age       | smallint              | 
Indexes:
    "tbl_user_pkey" PRIMARY KEY, btree (id)

原始数据:

digoal=> select * from tbl_user;
 id | firstname | lastname |   corp   | age 
----+-----------+----------+----------+-----
  1 | zhou      | digoal   | sky-mobi |  27
  2 | zhou      | digoal   | sky-mobi |  27
  3 | zhou      | digoal   | sky-mobi |  27
  4 | zhou      | digoal   | sky-mobi |  27
  5 | zhou      | digoal   | sky-mobi |  27
(5 rows)

远程数据库连接本机的ETH0网口，即测试中本地数据库和远程数据库使用同一个数据库。(不同数据库效果也是一样的)

要使用prepared transaction首先要打开这个功能,修改参数,max_prepared_transactions = 50 (存储50个prepared transaction,默认为0表示不支持prepared transaction)修改参数后重启数据库,注意这个修改的是远程数据库的参数.

开始测试一:

create or replace function fun_test () returns void as $BODY$
declare
v_trace text;
begin
v_trace = 'RMT 2PC begin';
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','begin;insert into tbl_user(id) values(5);prepare transaction ''p1''') ;
v_trace = 'LOCAL start';
insert into tbl_user(id) values(6);
v_trace = 'RMT 2PC commit start';
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','commit prepared ''p1''') ;
v_trace = 'RMT 2PC commit success';
raise notice 'execute success at : %.',v_trace;
return;
exception
when others then
raise notice 'execute error at : %.',v_trace;
perform * from dblink('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','select 1 from pg_prepared_xacts where gid=''p1''') as t(id int);
if found then
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','rollback prepared ''p1''');
end if;
return;
end;
$BODY$ language plpgsql;

(5将违反约束，因此应该在执行v_trace = 'LOCAL start';之前抛出异常,来看看结果)

digoal=> select * from fun_test();
NOTICE:  execute error at : RMT 2PC begin.
 fun_test 
----------
 
(1 row)

digoal=> select * from pg_prepared_xacts ;
 transaction | gid | prepared | owner | database 
-------------+-----+----------+-------+----------
(0 rows)
digoal=> select * from tbl_user;
 id | firstname | lastname |   corp   | age 
----+-----------+----------+----------+-----
  1 | zhou      | digoal   | sky-mobi |  27
  2 | zhou      | digoal   | sky-mobi |  27
  3 | zhou      | digoal   | sky-mobi |  27
  4 | zhou      | digoal   | sky-mobi |  27
  5 | zhou      | digoal   | sky-mobi |  27
(5 rows)

很好,达到预期效果，确保了事务完整性.

注意，如果去掉exception中的判断，会发生什么情况.

create or replace function fun_test () returns void as $BODY$
declare
v_trace text;
begin
v_trace = 'RMT 2PC begin';
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','begin;insert into tbl_user(id) values(5);prepare transaction ''p1''') ;
v_trace = 'LOCAL start';
insert into tbl_user(id) values(6);
v_trace = 'RMT 2PC commit start';
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','commit prepared ''p1''') ;
v_trace = 'RMT 2PC commit success';
raise notice 'execute success at : %.',v_trace;
return;
exception
when others then
raise notice 'execute error at : %.',v_trace;
-- perform * from dblink('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','select 1 from pg_prepared_xacts where gid=''p1''') as t(id int);
-- if found then
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','rollback prepared ''p1''');
-- end if;
return;
end;
$BODY$ language plpgsql;

# 没错，会报P1不存在的错误,所以还是判断一下为妙,如果能在语法中加入IF EXISTS就更完美了.

digoal=> select * from fun_test();
NOTICE:  execute error at : RMT 2PC begin.
ERROR:  prepared transaction with identifier "p1" does not exist
CONTEXT:  Error occurred on dblink connection named "unnamed": could not execute command.
SQL statement "SELECT dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','rollback prepared ''p1''')"
PL/pgSQL function "fun_test" line 18 at PERFORM

开始测试二:

create or replace function fun_test () returns void as $BODY$
declare
v_trace text;
begin
v_trace = 'RMT 2PC begin';
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','begin;insert into tbl_user(id) values(6);prepare transaction ''p1''') ;
v_trace = 'LOCAL start';
insert into tbl_user(id) values(5);
v_trace = 'RMT 2PC commit start';
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','commit prepared ''p1''') ;
v_trace = 'RMT 2PC commit success';
raise notice 'execute success at : %.',v_trace;
return;
exception
when others then
raise notice 'execute error at : %.',v_trace;
perform * from dblink('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','select 1 from pg_prepared_xacts where gid=''p1''') as t(id int);
if found then
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','rollback prepared ''p1''');
end if;
return;
end;
$BODY$ language plpgsql;

(5将违反约束，因此应该在执行v_trace = 'RMT 2PC commit start';之前抛出异常,来看看结果)

digoal=> select * from fun_test();
NOTICE:  execute error at : LOCAL start.
 fun_test 
----------
 
(1 row)
digoal=> select * from tbl_user;
 id | firstname | lastname |   corp   | age 
----+-----------+----------+----------+-----
  1 | zhou      | digoal   | sky-mobi |  27
  2 | zhou      | digoal   | sky-mobi |  27
  3 | zhou      | digoal   | sky-mobi |  27
  4 | zhou      | digoal   | sky-mobi |  27
  5 | zhou      | digoal   | sky-mobi |  27
(5 rows)
digoal=> select * from pg_prepared_xacts ;
 transaction | gid | prepared | owner | database 
-------------+-----+----------+-------+----------
(0 rows)

# 非常好,达到预期效果，确保了事务完整性。

例三:

create or replace function fun_test () returns void as $BODY$
declare
v_trace text;
begin
v_trace = 'RMT 2PC begin';
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','begin;insert into tbl_user(id) values(6);prepare transaction ''p1''') ;
v_trace = 'LOCAL start';
insert into tbl_user(id) values(7);
v_trace = 'RMT 2PC commit start';
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','commit prepared ''p1''') ;
v_trace = 'RMT 2PC commit success';
raise notice 'execute success at : %.',v_trace;
return;
exception
when others then
raise notice 'execute error at : %.',v_trace;
perform * from dblink('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','select 1 from pg_prepared_xacts where gid=''p1''') as t(id int);
if found then
perform dblink_exec('hostaddr=172.16.3.33 port=1921 dbname=digoal user=digoal password=digoal','rollback prepared ''p1''');
end if;
return;
end;
$BODY$ language plpgsql;

把插入的ID修改为6 , 7，结果应该是成功,来看看

digoal=> select * from fun_test();
NOTICE:  execute success at : RMT 2PC commit success.
 fun_test 
----------
 
(1 row)
digoal=> select * from tbl_user;
 id | firstname | lastname |   corp   | age 
----+-----------+----------+----------+-----
  1 | zhou      | digoal   | sky-mobi |  27
  2 | zhou      | digoal   | sky-mobi |  27
  3 | zhou      | digoal   | sky-mobi |  27
  4 | zhou      | digoal   | sky-mobi |  27
  5 | zhou      | digoal   | sky-mobi |  27
  6 |           |          |          |    
  7 |           |          |          |    
(7 rows)

good 完全符合预期，确保了事务完整性.

原理 :

begin;
SQLs;
prepare transaction 'transaction_id';

执行以上SQL，将把SQLs的执行内容存入数据库磁盘中，因此不管数据库DOWN机也好，正常关闭也好，断开连接也好，这部分SQLs都是存在的。因此PostgreSQL的2PC机制对数据来说是安全的。但是唯一不好的是2PC占用事务号，因此非常长时间的2PC可能对数据库造成不良影响.

注意事项 :
1. 不要使2PC时间过长，因为有2PC存在的话vacuum不能回收垃圾空间(这个我在之前的博客也有写到)。
2. 2PC时间过长还可能造成强制数据库SHUTDOWN，如 transaction ID wraparound.
3. 2PC时间过长也可能带来锁时间过长的问题。
4. 因此没必要的话建议不要开启prepared transaction,由应用来实现2PC也是不错的选择.

原文警告:

Caution

It is unwise to leave transactions in the prepared state for a long time. This will interfere with the ability of VACUUM to reclaim storage, and in extreme cases could cause the database to shut down to prevent transaction ID wraparound (see Section 23.1.4). Keep in mind also that the transaction continues to hold whatever locks it held. The intended usage of the feature is that a prepared transaction will normally be committed or rolled back as soon as an external transaction manager has verified that other databases are also prepared to commit.

If you have not set up an external transaction manager to track prepared transactions and ensure they get closed out promptly, it is best to keep the prepared-transaction feature disabled by setting max_prepared_transactions to zero. This will prevent accidental creation of prepared transactions that might then be forgotten and eventually cause problems.

PostgreSQL's two-phase commit used with dblink example

热门文章

最新文章

相关课程

相关电子书

相关实验场景