On Thu, Oct 06, 2011 at 03:20:08PM -0700, Eirik Toft wrote:
> On Sep 13, 11:16 am, mark.bo...@proquest.com ("Bobak, Mark") wrote:
> > Does anyone have any experience w/ doing two-phase commit across
> > connections to two different databases from the same Perl program?
> > (To guarantee that either both or neither transaction is committed,
> > for consistency.)
>
> Well, assuming you have AutoCommit turned off, why not....
> unless ($sth1->execute("this","that") && $sth2->execute("this","that")) {
> $dbh1->rollback;
> $dbh2->rollback;
> } else {
> $dbh1->commit;
> $dbh2->commit;
> }
Because if the second commit fails (for any of countless reasons) the
first commit can't be rolled back.
Mark, there's no explicit support for two-phase commit in the DBI, but
drivers are free to implement support via private methods. I don't know
off hand if any do. After a very quick skim of the OCI docs at
http://www.tacsoft.cn/1110/appdev.111/b28395/oci17msc006.htm
it might be fairly simple to add to DBD::Oracle.
Something like:
...as above...
else {
try {
$dbh1->ora_trans_prepare(); # OCITransPrepare()
$dbh2->ora_trans_prepare();
$dbh1->ora_trans_commit(OCI_TRANS_TWOPHASE); # OCITransCommit()
$dbh2->ora_trans_commit(OCI_TRANS_TWOPHASE);
}
catch {
$dbh1->ora_trans_forget(); # OCITransForget()
$dbh2->ora_trans_forget();
}
}
[assuming RaiseError is enabled and Try::Tiny has been use'd.]
Would be nice to add the rest of the OCITrans*() calls as well.
Tim.
