PostgreSQL's two-phase commit used with dblink example

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
简介:
我在前面一篇博客有提到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.

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
固态存储 关系型数据库 Linux
|
关系型数据库 数据库 PostgreSQL
|
4月前
|
存储 关系型数据库 测试技术
拯救海量数据:PostgreSQL分区表性能优化实战手册(附压测对比)
本文深入解析PostgreSQL分区表的核心原理与优化策略,涵盖性能痛点、实战案例及压测对比。首先阐述分区表作为继承表+路由规则的逻辑封装,分析分区裁剪失效、全局索引膨胀和VACUUM堆积三大性能杀手,并通过电商订单表崩溃事件说明旧分区维护的重要性。接着提出四维设计法优化分区策略,包括时间范围分区黄金法则与自动化维护体系。同时对比局部索引与全局索引性能,展示后者在特定场景下的优势。进一步探讨并行查询优化、冷热数据分层存储及故障复盘,解决分区锁竞争问题。
439 2
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
514 0

推荐镜像

更多