PostgreSQL：准备交易 [英] Postgresql: PREPARE TRANSACTION

问题描述

我有两个数据库服务器db1和db2。

I've two DB servers db1 and db2.

db1有一个名为 tbl_album
的表。
db2有一个名为 tbl_user_album

db1 has a table called tbl_album
db2 has a table called tbl_user_album

CREATE TABLE tbl_album
(
id    PRIMARY KEY,
name  varchar(128)
...
);

CREATE TABLE tbl_user_album
(
id          PRIMARY KEY,
album_id    bigint
...
);

现在，如果用户想创建一个相册，我的PHP代码需要做的是：

Now if a user wants to create an album what my php code needs to do is:

• 在db1中创建一条记录并保存其ID（主键）


• 使用它在db2中创建一条记录保存在第一条语句中

是否可以将这两个语句保留在事务中？我也可以使用php解决方案。我的意思是说，如果有一种解决方案需要php代码来保留db句柄并在这些句柄上进行提交或回滚，那我就很好。

Is it possible to keep these two statements in a transaction? I'm ok with a php solution too. I mean I'm fine if there is a solution that needs php code to retain db handles and commit or rollback on those handles.

任何帮助都是值得的。

推荐答案

是可以的，但是您真的需要吗？

Yes it is possible, but do you really need it?

在确定这实际上必须是两个独立的数据库之前，请三思而后行。

Think twice before you decide this really must be two separate databases.

您可以保持两个连接均处于打开状态，如果第二个命令失败，则回滚第一个命令。

You could just keep both connections open and ROLLBACK the first command if the second one fails.

如果您真的需要准备好的交易，请继续阅读。

If you'd really need prepared transactions, continue reading.

关于您的模式-我将使用序列生成器和RETURNING子句在数据库方面，只是为了方便。

Regarding your schema - I would use sequence generators and RETURNING clause on database side, just for convenience.

CREATE TABLE tbl_album (
  id    serial PRIMARY KEY,
  name  varchar(128) UNIQUE,
  ...
);
CREATE TABLE tbl_user_album (
  id          serial PRIMARY KEY,
  album_id    bigint NOT NULL,
  ...
);

现在，您将需要一些外部胶水-分布式事务处理协调器（？）-才能正常工作。

Now you will need some external glue - distributed transaction coordinator (?) - to make this work properly.

窍门是使用 PREPARE交易 ，而不是 COMMIT 。然后，在两个事务都成功之后，使用 COMMIT PREPARED 。

The trick is to use PREPARE TRANSACTION instead of COMMIT. Then after both transactions succeed, use COMMIT PREPARED.

PHP概念验证如下。

PHP proof-of-concept is below.

警告！。此代码缺少关键部分-错误控制。 $ db2 中的任何错误都应被捕获，并且准备回滚时应在 $ db1 <上执行/ code>
如果您没有发现错误，则会将 $ db1 留给冻结交易，这确实非常糟糕。

WARNING! this code is missing the critical part - that is error control. Any error in $db2 should be caught and ROLLBACK PREPARED should be executed on $db1 If you don't catch errors you will leave $db1 with frozen transactions which is really, really bad.

<?php
$db1 = pg_connect( "dbname=db1" );
$db2 = pg_connect( "dbname=db2" );
$transid = uniqid();

pg_query( $db1, 'BEGIN' );
$result = pg_query( $db1, "INSERT INTO tbl_album(name) VALUES('Absolutely Free') RETURNING id" );
$row = pg_fetch_row($result);
$albumid = $row[0];
pg_query( $db1, "PREPARE TRANSACTION '$transid'" );
if ( pg_query( $db2, "INSERT INTO tbl_user_album(album_id) VALUES($albumid)" ) ) {
    pg_query( $db1, "COMMIT PREPARED '$transid'" );
}
else {
    pg_query( $db1, "ROLLBACK PREPARED '$transid'" );
}
?>

再说一遍-在使用前先考虑一下。

And again - think before you will use it. What Erwin proposes might be more sensible.

哦，还有一点需要注意...要使用此PostgreSQL功能，您需要设置 max_prepared_transactions 配置变量为非零值。

Oh and just one more note... To use this PostgreSQL feature, you need to set max_prepared_transactions config variable to nonzero value.

这篇关于PostgreSQL：准备交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！