Database Error Handling: What if You have to Call Outside service and the Transaction Fails?
- by Ngu Soon Hui
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?
