SqlConnection and avoiding promotion to MSDTC

Question

When we need to do database access in our application, we use the following patterns:

• For querying, we have a static factory class with a method CreateOpenConnection which does nothing more than new SqlConnection(myConnectionString) and calls Open() on it. This method gets called before we do a query and the connection is disposed after the query returns.
• For inserts/updates/deletes we use a Unit of Work pattern where the changes are batched up and submitted to the database with a call to work.Commit() like so:

work.Commit:

using (var tranScope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    using (var conn = DapperFactory.CreateOpenConnection())
    {
      var count = _changeTracker.CommitChanges(conn);

      tranScope.Complete();

      return count;
    }
}

This seems to work great for general usage as part of a webservice, but is currently giving me MSDTC trouble when I try to use this in combination with Rebus.

From what I can tell, Rebus (when it handles a message in the queue) creates a new TransactionScope so that in case of a failure to handle the message, stuff can be rolled back. Now, this in itself has worked fine so far. I can open a new SqlConnection inside a Rebus message handler without any issues (however, using our legacy Entity Framework queries and manual SqlConnections inside the same Rebus TransactionScope doesn't work, but I don't consider that an issue right now). But yesterday I asked the following question:

Serial processing of a certain message type in Rebus

To which the answer seems to be to use the saga feature of Rebus. I tried implementing this and configured it so that the Rebus saga gets persisted to a new SQL Server database (with a distinct connection string). Presumably, using that SQL Server persistence opens a SqlConnection of its own, because any time I try to create a SqlConnection now, I get the following exception:

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

Enabling MSDTC is something I would very, very much like to avoid doing, with regards to configuration and performance overhead. And I may be wrong, but it also just doesn't seem necessary.

What I presume is happening here is that Rebus creates an ambient TransactionScope and that the SqlConnection it creates enlists to that scope. And when I try to create my own SqlConnection it also tries to enlist to that ambient scope and because multiple connections are involved, it gets promoted to MSDTC, which fails.

I have an idea on how to fix this, but I don't know if it's the right thing to do. What I would do is:

• Add Enlist=false to my application's connection string so that it never enlists to ambient transactions.
• Modify the Commit method so that it doesn't create a new TransactionScope (which my connection won't subscribe to any more because I just told that it shouldn't) but that it uses conn.BeginTransaction.

Like so:

var transaction = conn.BeginTransaction();

try
{
  var count = _changeTracker.CommitChanges(conn);
  transaction.Commit();
  return count;
}
catch
{
  transaction.Rollback();
  throw;
}
finally
{
  transaction.Dispose();
}

I'm just not sure if this is the right approach and what the possible drawbacks are.

Any tips?

UPDATE: To clarify, it's not the work.Commit() that's been giving me problems, I'm quite sure that it would work, but I never get there because my querying is what fails.

An example of what fails:

public int? GetWarehouseID(int appID)
{
  var query = @"
select top 1 ID from OrganizationUnits o
where TypeID & 16 = 16 /* warehouse */";

  using (var conn = _dapper.OpenConnection())
  {
    var id = conn.Query<int?>(query).FirstOrDefault();

    return id;
  }
}

This gets called when a TransactionScope has been created by Rebus, as well as after a SqlConnection is opened by Rebus. Upon opening my SqlConnection, it tries to enlist and crashes

Answer 1

I am somewhat surprised that you're seeing this, because the RequiresNew should mean that it is isolated from the other transaction; usually, this message means that 2 connection have been activated inside a transaction scope - are you sure there is no other code creating / opening a connection inside that block?

Your proposed solution should work - although in some ways TransactionScopeOption.Suppress may be more convenient than changing your config (but either should work). However, there's a problem: ADO.NET transactions must be passed to the individual commands, so you would need (also tidying up the code a bit):

using(var transaction = conn.BeginTransaction()) {
    try {
        var count = _changeTracker.CommitChanges(conn, transaction);
        transaction.Commit();
        return count;
    } catch {
        transaction.Rollback();
        throw;
    }
}

where CommitChanges accepts a transaction - perhaps using optional parameters:

int CommitChanges(DbConnection connection, DbTransaction transaction = null)
{ ... }

Your naming of DapperFactory suggests you are using "dapper" - in which case, you can just pass that into "dapper" whether it is null or not, i.e.

conn.Execute(sql, args, transaction: transaction);