prepare transaction

4

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.

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.

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

PHP proof-of-concept is below.

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.

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

转载于:https://my.oschina.net/innovation/blog/3093203