C# controlling a transaction across multiple databases

Question

Say I'm having a Windows Form application which connected to n databases, with n connections opened simultaneously.

What I'm looking for is to do a transaction with all of those databases in one go.

For example if I were to have 2 database connections :

using (ITransaction tx1 = session1.OpenTransaction())
{
    using (ITransaction tx2 = session2.OpenTransaction())
    {
        // Do the query thingy here
    }
}

Writing all that is fine at first, but things get kind of redundant when I wanted to query here and there, and not to mention the possibility to adding a new connection.

What I wanted is to loop all of the registered session and wrap it up in a service, probably something like this :

class TransactionManager
{
    private ISession[] _sessions;

    public TransactionManager(string[] connectionStrings)
    {
        // Initialize the sessions here
    }

    public function DoTransaction(string query)
    {
        foreach (ISession session in _sessions)
        {
            // What to do here? Using? Try-catch?
        }
    }
}

If I were to use using in the foreach loop, it would mean that if connection A successful but connection B wasn't, then only connection B would be rolled back.

Answer 1

It seems you may be re-inventing TransactionScope. Doing all this under a unit of work is straightforward*:

  using (TransactionScope scope = new TransactionScope())
  {
     ... Do Stuff with Connection 1 using SqlDataReader
     ... Do Stuff with Connection 2 using Entity Framework
     ... Do Stuff with Connection 3 on another Oracle Database
     ... And for good measure do some stuff in MSMQ or other DTC resource
     scope.Complete(); // If you are happy
  }

Stuff doesn't need to be inline at all - it can be in a different class, or a different assembly. There's no need to explicitly register database or queue connections with the TransactionScope - everything happens automagically, provided that the resources you use are able to enlist into an ambient transaction.

Now the small print:

• * Any time you use more than one database connection concurrently, or different connection strings, or multiple technologies, this will require 2 phase commit and escalate to a DTC transaction in order to ensure ACID across the resources. DTC itself has lots more small print and poses many more challenges in a corporate network, like firewalls, clustering, security configuration and bugs.

• However, with Lightweight transactions on MS Sql Server, if you can keep all your connections using the same database and same connection string settings, and close each connection before opening the next, then you can avoid DTC.

• Maintaining a transaction across multiple ACID resources will invariably maintain locks on these resources, until the transaction is committed or rolled back. This often doesn't make for good neighbourliness in a high volume enterprise, so be sure to consider the consequences of the locking.

• If the Stuff is done across multiple threads, you'll need to rope in DependentTransaction

• A last point worth mentioning is the default isolation level with TransactionScope is Serializable, which is prone to deadlocks. In most non-critical scenarios you'll probably be able drop this down to Read Committed.