Implementing transactions over multiple databases

Question

I am performing data changes on multiple databases, and I want to implement a transaction that will cover all of the changes.

This is what I currently have:

try
{
    db[1].begintransaction();
    db[1].ExecuteNonQuery();

    db[2].begintransaction();
    db[2].ExecuteNonQuery();

    ...

    db[N].begintransaction();
    db[N].ExecuteNonQuery();

    // will execute only if no exception raised during the process
    for (int a = 0; a < N; a++)
    {
        db[a].Commit();// what if there is an error/exception here
    }
}
catch
{
    for (int a = 0; a < N; a++)
    {
        db[a].RollBack();
    }
}

The problem is that the above would fail horribly if an exception happens during a Commit() (see the comment). Is there any better way of accomplishing this?

Answer 1

Use the TransactionScope class like this:

using (TransactionScope ts = new TransactionScope())
{
    //all db code here

    // if an error occurs jump out of the using block and it will dispose and rollback

    ts.Complete();
}

The TransactionScope class will automatically convert to a distributed transaction if necessary.

Answer 2

Using transactionScope is the answer. It even works with different DBMS!!!

Transactions over multiple databases

Answer 3

As cletus said, you need some kind of two-phase commit. As the article states, this doesn't always work in practice. If you need a robust solution, you must find a way to serialize the transactions in such a way that you can do them one after the other and roll them back individually.

Since this depends on the specific case on which you don't provide any details, I can't give you ideas how to attack this.

Answer 4

If you wish to execute transaction across multiple instances of SQL Server then take a look at the Microsoft Distributed Transaction Coordinator documentation