How to bind two transactions together?

Question

I'm in a scenario where I have two distinct databases and I want to commit different changes in both database in such a way that if one of the commit fails, the other will also fail.

If I have a 'Transaction A' on 'Database A' and 'Transaction B' on 'Database B', how can I bind the two transactions together so that both will succeed or fail atomically.

I can't figure out a way to do this. It's easy to rollback 'Transaction B' if 'Transaction A' fails, but if 'Transaction B' fails when 'Transaction A' is already committed I'm screwed.

I would like to know if there is a technology to handle this in a specific database product, or even better if there is a generic pattern to handle this scenario that could even be applied to any transactional system such as binding a database transaction with a transactional message queue.

Answer 1

You need to use a distributed transaction manager (which, in the most simple case, you program itself can act as).

X/OpenXA is the most widely used standard for managing distributed transactions, and all databases of the big four support it natively.

Answer 2

There is a standard database process called "Two Phase Commit" and most of the commercial RDBMSs has successful implementation of 2PC one way or another.

Here are some references;

SQL Server: http://msdn.microsoft.com/en-us/library/aa754091(v=bts.10).aspx Oracle: http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/ds_txns003.htm MySQL: http://dev.mysql.com/doc/refman/5.0/en/xa.html

Answer 3

using (TransactionScope scopeExternal = new TransactionScope())
{
    using (TransactionScope scope1 
            = new TransactionScope(TransactionScopeOption.Suppress))
    {
        ... operations for 1st DB
        scope1.Complete();
    }


    using (TransactionScope scope2 
            = new TransactionScope(TransactionScopeOption.Suppress))
    {
        ... operations for 2nd DB
        scope2.Complete();
    }

    scopeExternal.Complete();
}

If any transaction from either scope1 or scope2 fails it throws an exception that prevent from scopeExternal transaction commit.