Scope nested transactions and / or nested connections causing MSDTC escalation

Question

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

I wrote a database shell class that creates connections as needed and removes them as quickly as possible. The connection string remains unchanged in all connections.

Here is a very small 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 on the contents of the PerformConnectionAction method so that only one can start at a time, then everything works, but there is a noticeable performance hit. However, when I delete it, it escalates.

, , TransactionScope, TransactionScopes / PerformDataContextAction PerformConnectionAction ( ); ( /):

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();
}

, , .

, :

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

, / , , MSDTC ?

Answer 1

? .

/ , . .

, , , , .