Database error handling: what if you have to call outside service and the transaction fails?

We all know that we can always wrap our database call in transaction ( with or without a proper ORM), in a form like this:

$con = Propel::getConnection(EventPeer::DATABASE_NAME);
try {
    $con->begin();
    // do your update, save, delete or whatever here.
    $con->commit();
} catch (PropelException $e) {
    $con->rollback();
    throw $e;
}

This way would guarantee that if the transaction fails, the database is restored to the correct status.

But the problem is that let's say when I do a transaction, in addition to that transaction, I need to update another database ( an example would be when I update an entry in a column in databaseA, another entry in a column in databaseB must be updated). How to handle this case?

Let's say, this is my code, I have three databases that need to be updated ( dbA, dbB, dbc):

$con = Propel::getConnection("dbA");
try {
   开发者_运维百科 $con->begin();
    // update to dbA
    // update to dbB
    //update to dbc
    $con->commit();
} catch (PropelException $e) {
    $con->rollback();
    throw $e;
}

If dbc fails, I can rollback the dbA but I can't rollback dbb.

I think this problem should be database independent. And since I am using ORM, this should be ORM independent as well.

Update: Some of the database transactions are wrapped in ORM, some are using naked PDO, oledb ( or whatever bare minimum language provided database calls). So my solution has to take care this.

Any idea?

First, some databases support distributed transaction protocols that will allow all of dbA, dbB, and dbC to participate in the same transaction at once. If yours does, use that :)

Failing that, however, you will need to implement your own distributed transaction protocol, such as two-phase-commit or Paxos. These protocols are complex, but this complexity is absolutely necessary, so don't be tempted to cut corners :) I would recommend following the references listed from those wikipedia links and reading them before attempting to implement something like this.

You need a DBMS that supports Distributed transactions. These do exactly what you need: they enforce the begin/commit/rollback semantics across multiple systems.

For example, Enterprise Java Beans and Microsoft Transaction Server support distributed transactions.

If the "call outside" is not to a database, it gets even trickier. You could try to emulate transactions, but some things are difficult to roll back (filesystem operations) or impossible (posting data to a server). So it will depend on the specific problem.

Most RDBMS's support distributed transactions. For instance, MS SQL Server uses the Distributed Transaction Cooridinator (DTC), a service, to enlist distributed transactions.