Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL’s two-phase commit used with dblink example - 2PC , 两阶段事务

5 minute read

背景

我在前面一篇博客有提到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.

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...