Database Error Handling: What if You have to Call Outside service and the Transaction Fails?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.