Re: Oracle and Two Phase commit with Perl?

On Thu, Oct 06, 2011 at 03:20:08PM -0700, Eirik Toft wrote:
> On Sep 13, 11:16 am, [email protected] ("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.