Nested TransactionScope and/or nested connections causing MSDTC escalation

Question

I am trying to avoid MSDTC escalation in my application. I am using LINQ with SQL Server Express 2008 R2, and later will be using the full version.

I have written a database wrapper class which creates connections as necessary and disposes of them as quickly as possible. The connection string remains the same across all connections.

Here is a very slimmed-down version of my class:

public class SqlServerDatabaseWrapper {

  public SqlServerDatabaseWrapper(string connectionString) {
    ConnectionString = connectionString; 
  }

  public string ConnectionString { get; private set; }

  private static IDbConnection GetOpenConnection() {
    var conn = new SqlConnection(ConnectionString);
    conn.Open();
    return conn;
  }

  // there is also a second method to return a value
  // there is PerformCommandAction for SqlCommand as well
  public void PerformDataContextAction<TContext>(Func<IDbConnection, TContext> creator, Action<TContext> action) where TContext : DataContext {
    PerformConnectionAction(conn => {
      using (var context = creator(conn))
        action(context);
    });
  }

  // there is also a second method to return a value
  public void PerformConnectionAction(Action<IDbConnection> action) {
    using (IDbConnection conn = GetOpenConnection(ConnectionString)) {
      action(conn);
    }
  }
}

Used as follows:

var db = new SqlServerDatabaseWrapper(connectionString);
db.PerformDataContextAction(
  conn => new SomeDataContext(conn), 
  context => { /* do something */ }
);

If I put a lock around the content of the PerformConnectionAction method, so only one can run at a time, then everything works but there is a noticeable performance penalty. However, when I remove it, it escalates.

The code that is using the wrapper is using a TransactionScope, and there could be nesting of TransactionScopes and/or calls to either PerformDataContextAction or PerformConnectionAction (which each create a new connection with the same connection string); in pseudo-code (as this might occur across different classes/methods):

var db = new SqlServerDatabaseWrapper(connectionString)
using (TransactionScope tran = new TransactionScope()) {
  db.PerformDataContextAction( 
    /* ... */,
    context => {
      using (TransactionScope tran2 = new TransactionScope()) {
        db.PerformConnectionAction(conn => { /* some stuff */ });
        tran2.Complete();
      }
    }
  tran.Complete();
}

Note also that there is use of the static Membership methods which could occur at various points.

I should also add that the connection string is as follows:

Data Source=.\SQLEXPRESS;Initial Catalog=db1;User Id=test1;Password=test1;MultipleActiveResultSets=true;Enlist=false;

The question is, how do I refactor/rewrite my code so that my application can perform well, without MSDTC, and without introducing locks?

Thanks

Answer 1

Are you using only one connection to the database inside the transaction scope? Creating two connections with the same or a different connection string inside a transaction scope will escalate the transaction to a distributed one.

You could store the connection in a thread static variable and close/dispose it when all the work in the transaction is done. Then every thread will have it's own connection.

When you add a lock to your logic you will probably not get an distributed transaction because the connection pool will return you the same connection every time.