Fixing TransactionScope and SqlConnection Issues

sql Jun 20, 2019

I came across a weird issue related to transactions.

The PUT/update endpoint of my newly deployed API was throwing errors on the staging environment. Of course, it was all tested working on my local machine, so what was going on?

Looking at the logs, I found this error message:

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.

Here is the inner exception:

The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)

I knew that the problem couldn't be caused by a database or SQL Server setting, because existing code already used transactions with no issues.

Below is the code that I wrote that was using transactions:


using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    foreach (var uploader in agencyUploaders)
    {
        uploader.AgencyID = agencyId;

        var agencyUploader = await _uploaderRepository.Get(agencyId, uploader.ChannelID, uploader.AdType).ConfigureAwait(false);

        if (agencyUploader != null)
        {
            await _uploaderRepository.Update(uploader).ConfigureAwait(false);
        }
        else
        {
            await _uploaderRepository.Add(uploader).ConfigureAwait(false);
        }
    }

    transaction.Complete();
}

Thankfully, Google-fu led me to a few StackOverflow questions that were similar to my problem.

It turned out that the transaction was automatically being promoted to a distributed transaction because the repository calls were using different connection strings. Here's the Get method in my repository class:


public async Task Get(int agencyId, Channel channel, AgencyUploaderAdType adType)
{
    var query = "SELECT * FROM AgencyUploader WHERE AgencyID = @agencyId AND ChannelID = @channelId AND AdType = @adType";
    
    using (var sqlConnection = new SqlConnection(_dbConnections.ConnectionStringReadOnly))
    {
        return (await sqlConnection.QueryAsync(query, new {
            agencyId,
            channelId = (int)channel,
            adType = adType.ToString("d")
        }).ConfigureAwait(false)).FirstOrDefault();
    }
}

And here's the Update method:


public async Task Update(AgencyUploader agencyUploader)
{
    string query = $"UPDATE AgencyUploader SET Uploader = @uploader WHERE AgencyID = @agencyId AND ChannelID = @channelId AND AdType = @adType";

    using (var sqlConnection = new SqlConnection(_dbConnections.ConnectionString))
    {
        await sqlConnection.ExecuteAsync(query, new
        {
            uploader = agencyUploader.Uploader.ToUpper(),
            agencyId = agencyUploader.AgencyID,
            channelId = (int)agencyUploader.ChannelID,
            adType = agencyUploader.AdType.ToString("d")
        }).ConfigureAwait(false);
    }
}

Notice the difference in the connection strings that were passed into the SqlConnection constructor. However, this was NOT what caused the issue!

The gotcha is that these two connection string variables had the same values in my local configuration. But on the staging environment, the values were slightly different. They still pointed to the same server and database, but the connection string keys were not in the same order.

ConnectionString:

MultipleActiveResultSets=True;Pooling=True;App=Agency API;Connection Timeout=10

ConnectionStringReadOnly:

MultipleActiveResultSets=True;App=Agency API;Pooling=True;Connection Timeout=10

So even if they actually pointed to the same place, having different connection string values automatically made the system treat them as different servers - hence, the transaction is distributed.

And since distributed transactions were prohibited on staging, the issue only surfaced in that environment.

I replicated the error on my local environment by turning off and disabling the Distributed Transaction Coordinator service. Then I changed the order of the keys in one of the connection strings.

Hope this helps anyone struggling with the same issue!

unsplash-logoJohn Barkiple

Kristina Alberto

Software engineer from Sydney. I work at Domain Group.