在postgres上使用两阶段提交 [英] Using two phase commits on postgres

问题描述

假设一个表在 db1中有一个名为 t1的表，而另一个表在 db2中有一个名为 t2的表，并且我需要在两个表中插入一条记录，否则将失败。

已连接到db1，我想我应该输入这个

  BEGIN; 
准备交易 pepe； -这就是使您的交易存储在磁盘上的手册，所以如果我不能从另一个数据库使用它，目的是什么？）
 
插入t1（字段）值（'a_value '）; 
 
提交准备'pepe'
  

已连接到db2，我想是

  BEGIN; 
准备交易 pepe； -失败（事务的名称，含义，用途？）
-抱怨此错误：事务标识符 pepe已在使用中 
 
插入t2（字段）值（'another_value'）; 
 
提交准备好的'pepe'
  

您可能会看到我没有了解如何在postgres上使用两阶段提交。

TL; DR

我没有得到如何在同一RDBMS内的不同DB上执行同步命令。

我已阅读官方的Postgres文档，该文件用于在两个或多个不相关的Postgres数据库中进行同步工作，即所谓的两阶段提交协议可供我们使用。

所以我开始尝试看看人们在postgres中如何实际使用它们，我没有看到任何实际的例子，至多我能发送给这篇文章，他正在尝试使用几种postgres客户端连接到ord中的differents数据库er来模拟并行运行的多个进程对应该以感激（全部提交）或可怕方式（全部回滚）结束的几个数据库的运行。

其他来源我有一个偷看的例子，例如：

• https://en.wikipedia.org/wiki/Two-phase_commit_protocol （此来源
  很好地解释了该协议，但实际上让我想知道$ b $在哪里或是谁b我的协调员以及如何向参与者发送消息...我
  仅得到准备交易< id> ，提交准备好的 或
  回滚准备好的< id 命令供我使用）


• 两阶段提交


• https://dba。 stackexchange.com/questions/145656/dependent-transaction-in-separate-database-connections


• > https://www.endpoint.com/blog/2010/07/29/distributed-transactions-and-two -phase-


• https://www.citusdata.com/blog/2017/11/22/how-citus-executes-distributed-transactions/


• （来自golang客户端应用） https://github.com/go- pg / pg / issues / 490

请我真的很困惑，我希望horse_with_no_name出现在这里并启发我（

预先感谢！

分辨率（在Laurenz回答之后）

已连接t o在db1中，以下是要执行的sql行：

  BEGIN; 
-做所有事情或不做任何事情
-起点-
准备交易 t1； 
提交准备好的 t1 ||回滚准备的 t2（决策需要意识和协调）
  

同时连接到db2要执行的脚本：

  BEGIN; 
-做所有事情或不做任何事情
-起点-
准备交易 t2； 
 
提交了 t2 ||回滚已准备的't2'
  

• -停止点-是协调器进程（例如，执行语句的应用程序
  或psql
  客户端控制台或pgAdminII背后的人员）应停止的位置执行两个
  脚本。




• 然后，首先在db1上（然后在db2上，反之亦然），
  协调器进程（无论是否有人为）都必须运行<$每个连接上的c $ c> PREPARE事务。

  
  
  
  
  
  • 如果其中之一失败，则协调器必须运行在已经准备好交易的数据库上准备回滚，在其他数据库上 ROLLBACK 。
  
  
  • 如果没有人失败，则协调员必须在所有涉及的数据库上运行COMMIT PREPARED，该操作将永远不会失败（例如，当您走出房屋一步之遥，并且所有东西都已正确设置为安全退出时，就已存在房屋）
  
  

解决方案

我认为您误会了 PREPARE TRANSACTION 。

该语句在交易中结束工作，也就是说，应在之后发出所有工作都已完成。这个想法是，除了提交本身之外， PREPARE TRANSACTION 会执行所有在提交过程中可能失败的操作。这是为了确保随后的 COMMIT PREPARED 不会失败。

想法是，处理如下：

• 在涉及分布式事务的所有数据库上运行 START TRANSACTION 。 / p>




• 完成所有工作。如果有错误，请回滚所有事务。




• 运行 PREPARE TRANSACTION 在所有数据库上。如果该操作在任何地方都失败，请在已经准备好交易的数据库上运行 ROLLBACK ，然后在其他数据库上运行 ROLLBACK 。 / p>




• 一旦 PREPARE交易在任何地方都成功，则运行 COMMIT PREPARED 在所有涉及的数据库上。

这样，您可以保证全有或全无。跨多个数据库。

这里我没有提到的一个重要组成部分是分布式事务管理器。该软件可以永久存储上述算法中当前的处理位置，以便它可以在崩溃后清理或继续提交。

没有分布式事务管理器，两阶段提交的价值不高，而且实际上很危险：如果交易陷入准备阶段，阶段，但尚未提交，它们将继续持有锁，并且（对于PostgreSQL）即使通过服务器重新启动，也会阻止自动清理工作 ，因为此类事务必须持久。

这很难解决。

Asumming that a have a table called "t1" in a "db1" and other table called "t2" in a "db2", and i need to insert a record on both tables or fails.

Connected to the db1 i guess i shall type this

BEGIN;
PREPARE TRANSACTION 'pepe'; -- this says the manual that makes your transaction gets stored on disk, so what is the purpose if i can't use it from another database?)

insert into t1 (field) values ('a_value');

COMMIT PREPARED 'pepe'

Connected to the db2 i guess that

BEGIN;
PREPARE TRANSACTION 'pepe'; -- this fails (the name of the transacttion, what is the meaning, what is use for?)
 -- It complains about this "ERROR:  transaction identifier "pepe" is already in use"

insert into t2 (field) values ('another_value');

COMMIT PREPARED 'pepe'

As you may see i don't get how to use two phase commits on postgres.

TL;DR

I'm not getting how to perform syncronization commands on differents DB within the same RDBMS.

I have read at oficial postgres documentation that for syncronizing works across two or more unrelated postgres databases an implementation of the so called "two-phases commits" protocol is at our disposal.

So i start trying to see how people do actually use them within the postgres, i do not see any actual example, at most i get to this post of a guy that was trying to experiment with several postgres client connected to the differents databases in order to emulate the multiple process running in pararell doing things to the several dbs that should end in a gratefully (all commit) or dreadfully way (all rollback).

Other sources i have peek looking foward examples were:

• https://en.wikipedia.org/wiki/Two-phase_commit_protocol (this source explain well the protocol but really makes me wonder where or who is my "Coordinator" and how to send messages to the "participants"... i only got prepare transaction <id>, commit prepared <id> or rollback prepared <id> commands at my disposal)
• Two phase commit
• https://dba.stackexchange.com/questions/145656/dependent-transaction-in-separate-database-connections
• https://www.endpoint.com/blog/2010/07/29/distributed-transactions-and-two-phase-
• https://www.citusdata.com/blog/2017/11/22/how-citus-executes-distributed-transactions/
• (From a golang client-app) https://github.com/go-pg/pg/issues/490

Please i'm really confused, i hope horse_with_no_name to appear here and enlightme (as happen in the past) or any other charity soul that can help me.

Thanks in advance!

Resolution (After Laurenz's Answer)

Connected to the db1, these are the sql lines to execute:

BEGIN;
-- DO THINGS TO BE DONE IN A ALL OR NOTHING FASHION
-- Stop point --    
PREPARE TRANSACTION 't1';
COMMIT PREPARED 't1' || ROLLBACK PREPARED 't2' (decision requires awareness and coordination)

meanwhile connected to the db2 these will be the script to execute:

BEGIN;
-- DO THINGS TO BE DONE IN A ALL OR NOTHING FASHION
-- Stop point --  
PREPARE TRANSACTION 't2';

COMMIT PREPARED 't2' || ROLLBACK PREPARED 't2'

• The -- Stop point -- is where a coordinator process (for example an application executing the statement, or a human behind a psql client console or pgAdminII) shall stop the execution of both scripts.

• Then, first on db1 (and then on db2, or viceversa) the coordinator process (whatever been human or not) must run PREPARE TRANSACTION on each connection.

  • If one of then fails, then the coordinator must run ROLLBACK PREPARED on those database where the transaction was already prepared and ROLLBACK on the others.
  • If no one fails the coordinator must run COMMIT PREPARED on all involved databases, an operation that shall not fail ever (like existing the home when you are one step outside your house with all the things properly set to exit safely)

解决方案

I think you misunderstood PREPARE TRANSACTION.

That statement ends work on the transaction, that is, it should be issued after all the work is done. The idea is that PREPARE TRANSACTION does everything that could potentially fail during a commit except for the commit itself. That is to guarantee that a subsequent COMMIT PREPARED cannot fail.

The idea is that processing is as follows:

• Run START TRANSACTION on all database involved in the distributed transaction.

• Do all the work. If there are errors, ROLLBACK all transactions.

• Run PREPARE TRANSACTION on all databases. If that fails anywhere, run ROLLBACK PREPARED on those database where the transaction was already prepared and ROLLBACK on the others.

• Once PREPARE TRANSACTION has succeeded everywhere, run COMMIT PREPARED on all involved databases.

That way, you can guarantee “all or nothing” across several databases.

One important component here that I haven't mentioned is the distributed transaction manager. It is a piece of software that persistently memorizes where in the above algorithm processing currently is so that it can clean up or continue committing after a crash.

Without a distributed transaction manager, two-phase commit is not worth a lot, and it is actually dangerous: if transactions get stuck in the “prepared” phase but are not committed yet, they will continue to hold locks and (in the case of PostgreSQL) block autovacuum work even through server restarts, as such transactions must needs be persistent.

This is difficult to get right.

这篇关于在postgres上使用两阶段提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！