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

Socage

If you use "Enlist=false", surely that would make your TransactionScope pointless? because the connection wouldn't be in it – Sightless

Likewise, your BeginTransaction code isn't using the transaction either - an ADO.NET transaction needs to be specified explicitly on the command, so you'd need to pass transaction into CommitChanges, surely? – Sightless

Can you clarify exactly which version of sql server you are using? – Sightless

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

Sightless

Sorry, to clarify (and see my update) it's not the Commit that fails, it's the queries. My code for the commit was just to show how I propose to modify that if I add Enlist=false and still keep the Commit to be atomic (because it would indeed no longer do anything with the TransactionScope that I created. And the part of passing in the transaction is something I overlooked, thanks for the reminder :) – Socage

@Socage I didn't think the Commit was erroring; I was trying to illustrate 2 points - firstly, that the code needed to pass the transaction - which I then forget to include in the example (d'oh! please see edit); and secondly that your try/catch/finally was over-complicated and can be simplified – Sightless

Yeah, I was referring to your comment that you expected RequiresNew to work, which it probably does. But assuming I make the required change of passing in the DbConnection, you think my new approach would work? What I'm worried about is the inserts/updates/deletes not being atomic or the enlist=false having some negative/weird consequences to querying. In a way, it feels like 'fixing' an exception by swallowing it, if you know what I mean :) – Socage

@Socage switching to a very different transaction paradigm is hardly "swallowing it"; but yes, I agree that it is unsatisfying to not understand why your original approach didn't work. It comes down to how long you want to spend trying to fix it, rather than moving on to something useful – Sightless

Answer 2

This depends largely on the version of SQL Server you are using. See here for anothe SO question addressing a similar issue.

Its to do with how SQL 2005 and SQL 2008 differ in handling multiple connections within the same TransactionScope. i.e. SQL 2008 can open multiple connections in the same TransactionScope without escalating to the MSDTC.

Could this be the issue you are seeing

If this is the case, I think the only two options are upgrade to SQL 2008 or enable the MSDTC. I understand that both of these options are probably massive headaches.

Knossos

It must be emphasized that the 2008 handling still only applies when the connection-string and thread-identity of the two connections is identical, and when the connections are open in series (not concurrently) – Sightless