Slightly Rough Exchanges: Troubleshooting Transactional Issues with TransactionScope

In my previous post, I discussed the use of TransactionScope within C# and how it might simplify how transactions are managed within your applications. It covered what transactions are, some of the ways that they are traditionally managed, and how the use of TransactionScope could clean up your code and let "magic" take care of the rest. But as well all know - even the best magicians screw up occasionally.

This post discusses some of the common issues and caveats that you might run into when using the feature, how to overcome some of them, and when you might have to simply bite the bullet and avoid it altogether.

Some Common Caveats/Issues

I'll expand on each of these below, but a few things to look out for when using TransactionScope include:

  • Handling Multiple Connections Across Different Databases - This will involve the Microsoft Distributed Transaction Coordinator Service, which could complicate things/make them impossible.
  • Handling Asynchronous (e.g. async/await) - Since the underlying ambient transaction is static, multi-threaded environments will complicate things.
  • Timeout-related Errors - Ensuring you have appropriate timeouts set can be a challenge if you start running into errors, as the defaults are ... strange.

Player MSDTC has entered the game.

oh-snap-msdtc

Sharing transactions between connections with the same server and/or database is pretty trivial, however if you start querying multiple different sources/databases within a single transaction, you'll quickly be introduced to a new friend named the Microsoft Distributed Transaction Coordinator service, which does the following in a nutshell:

The MSDTC is a Windows service providing transaction infrastructure for distributed systems. In this case, a transaction means a general way of structuring the interactions between autonomous agents in a distributed system. Each transaction is a state transformation with four key properties - the ACID properties: Atomic (all or nothing), Consistent (legal), Isolated (independent of concurrent transactions) and Durable (once it happens, it cannot be abrogated). There are different techniques that implement the ACID properties but the most known one is two-phase commit.

Transactions contained within a TransactionScope are automatically promoted to "Distributed Transactions" as soon as multiple databases are involved and thus any servers must the MSDTC service enabled in order to operate as expected. This is extremely important as the calls will simply fail if the service is not enabled (and you won't always get the best error messages in the world to troubleshoot this).

If you are in total control of your environments, this might not be an issue. However, if your product is deployed on-prem, or if you have queries that rely on OPENQUERY or target Linked Servers, which you might not own, you may not be able to guarantee that the service is enabled.

Handling Asynchrony

late-doggo

In a world of SPAs, promises, and parallelism, applications deal with asynchrony much more commonly than in the years of old. The advent of things like async/await made this process much easier, but if you are dealing with transactions, there are quite a few things that you should be aware of.

In a nutshell, async/await doesn't work well within a TransactionScope block out of the box and requires some additional configuration to tell it how to resolve the operations via an TransactionScopeAsyncFlowOption, which might look something like this:

using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
     // Do your async/await work here
}

Another important consideration is that the ambient transaction that is discussed in the previous post to back the transactions is thread-static and thus won't work as expected in multi-threaded environments. As always - use your best judgement to determine to group a series of transactions together, and if there is a better fit given your environment.

Timeout!

lets-take-a-timeout

Managing timeouts is vital when working with transactions as there are a bevy of issues that could arise if you shoot too high or shoot too low. Extremely long timeouts can lead to issues with blocking/deadlocks, which are a nightmare to troubleshoot, and those that err on the side of being too short might not get the job done.

If the following error message looks familiar, then you might be in a situation where you need to reassess your existing timeouts:

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

Depending on your situation, you might use something like this and call it a day:

// I don't care how long it runs, just fix all my transaction issues by never timing-out
using (var transaction = new TransactionScope(TransactionScopeOption.Required, TimeSpan.MaxValue)
{
      // All your transacted operations are belong to us
}

Absolutely don't do this. This will almost certainly lead to blocking/deadlocks that will drive you crazy and this actually won't even work as expected. In most situations, you'll receive the previous error and that's because any explicit timeout values for a TransactionScope will always be limited by the maxTimeout option likely found within your application's config:

<system.transactions>
     <!-- This property will supercede any values defined within your code -->
     <machineSettings maxTimeout="01:00:00" />
</system.transactions>

The default maxTimeout is 10 minutes, so if you begin encountering timeout issues, you'll want to find a happy medium for this setting between that default and some upper-bound. Use your best judgement err towards a shorter timeout any potential deadlock/blocking issues arising.