C# Controlling a Transaction Across Multiple Databases

C# controlling a transaction across multiple databases

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.

Multiple database in one transaction in Entity Framework

Not all DB providers support distributed transactions.

Using transaction scopes will try to enlist the DB transaction in a distributed transacation managed by MSDTC. If your provider doesn't support this, it will fail.

SQL Server and Oracle providers support distributed transactions. But many other EF providers don't.

If your DB provider doesn'd support this, you'll have to use a different one or give up using transactions.

Provided you're using SQL Server 2005, it should be working, but:

• MSDTC service must be running (look for it in Services, in the Control Panel).
• the connection strings must be adequate for MSDTC to work

Look at this SO Q&A: confusion about transactions and msdtc.

NOTE: The name of the service is MSDTC. So you can run net start msdtc or net stop msdtc. If you're looking for it in the control panel, you'll find a descriptive name like "Distributed Transaction Coordinator" or a localized name like "Coordinador de transacciones distribuidas". Oddly enough, there is no way to show the name column in the control panel list of local services.

How do I use a TransactionScope with two databases when one is SQL Server CE?

You cannot use Distributed transaction with SQL Server Compact http://msdn.microsoft.com/en-us/library/bb896149.aspx

using transactions in 2 different methods

I would not do the below myself, quoting my comment on the question :

This is a very, very bad idea, what if the user waited 10 minutes
before pressing the second button. Do you want to keep your
transaction locked for that time ? It would time out.

But if you insist:

You can share the connection object as a class field (same for the transaction object). Create new on on the first button click.
Use the same connection and transaction object when the user clicks second button and commit the transaction in the event of the second button.

SqlConnection cnn;
SqlTransaction transac;

private void button1_Click(object sender, EventArgs e)
{
    cnn = new SqlConnection();
    transac = new SqlTransaction();
    // continue work
}

private void button2_Click(object sender, EventArgs e)
{

      // use cnn and transac directly, if null return from this method

      Trans.commit();
}

---

Related Topics