How to dispose TransactionScope in cancelable async/await?

Question

I'm trying to use the new async/await feature to asynchronously work with a DB. As some of the requests can be lengthy, I want to be able to cancel them. The issue I'm running into is that TransactionScope apparently has a thread affinity, and it seems that when canceling the task, its Dispose() gets ran on a wrong thread.

Specifically, when calling .TestTx() I get the following AggregateException containing InvalidOperationException on task.Wait ():

"A TransactionScope must be disposed on the same thread that it was created."

Here's the code:

public void TestTx () {
    var cancellation = new CancellationTokenSource ();
    var task = TestTxAsync ( cancellation.Token );
    cancellation.Cancel ();
    task.Wait ();
}

private async Task TestTxAsync ( CancellationToken cancellationToken ) {
    using ( var scope = new TransactionScope () ) {
        using ( var connection = new SqlConnection ( m_ConnectionString ) ) {
            await connection.OpenAsync ( cancellationToken );
            //using ( var command = new SqlCommand ( ... , connection ) ) {
            //  await command.ExecuteReaderAsync ();
            //  ...
            //}
        }
    }
}

UPDATED: the commented out part is to show there's something to be done — asynchronously — with the connection once it's open, but that code is not required to reproduce the issue.

Answer 1

In .NET Framework 4.5.1, there is a set of new constructors for TransactionScope that take a TransactionScopeAsyncFlowOption parameter.

According to the MSDN, it enables transaction flow across thread continuations.

My understanding is that it is meant to allow you to write code like this:

// transaction scope
using (var scope = new TransactionScope(... ,
  TransactionScopeAsyncFlowOption.Enabled))
{
  // connection
  using (var connection = new SqlConnection(_connectionString))
  {
    // open connection asynchronously
    await connection.OpenAsync();

    using (var command = connection.CreateCommand())
    {
      command.CommandText = ...;

      // run command asynchronously
      using (var dataReader = await command.ExecuteReaderAsync())
      {
        while (dataReader.Read())
        {
          ...
        }
      }
    }
  }
  scope.Complete();
}

I have not tried it yet, so I don't know if it will work.

Answer 2

I know this is an old thread, but if anyone has run into the problem System.InvalidOperationException : A TransactionScope must be disposed on the same thread that it was created.

The solution is to upgrade to .net 4.5.1 at a minimum and use a transaction like the following:

using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
   //Run some code here, like calling an async method
   await someAsnycMethod();
   transaction.Complete();
} 

Now the transaction is shared between methods. Take a look at the link below. It provide a simple example and more detail

For complete details, take a look at This

Answer 3

The problem stems from the fact that I was prototyping the code in a console application, which I did not reflect in the question.

The way async/await continues to execute the code after await is dependent on the presence of SynchronizationContext.Current, and console application don't have one by default, which means the continuation gets executed using the current TaskScheduler, which is a ThreadPool, so it (potentially?) executes on a different thread.

Thus one simply needs to have a SynchronizationContext that will ensure TransactionScope is disposed on the same thread it was created. WinForms and WPF applications will have it by default, while console applications can either use a custom one, or borrow DispatcherSynchronizationContext from WPF.

Here are two great blog posts that explain the mechanics in detail:
Await, SynchronizationContext, and Console Apps
Await, SynchronizationContext, and Console Apps: Part 2

Answer 4

Yep, you have to keep you transactionscope on a single thread. Since you are creating the transactionscope before the async action, and use it in the async action, the transactionscope is not used in a single thread. The TransactionScope was not designed to be used like that.

A simple solution I think would be to move the creation of the TransactionScope object and the Connection object into the async action.

UPDATE

Since the async action is inside the SqlConnection object, we cannot alter that. What we can do, is enlist the connection in the transaction scope. I would create the connection object in an async fashion, and then create the transaction scope, and enlist the transaction.

SqlConnection connection = null;
// TODO: Get the connection object in an async fashion
using (var scope = new TransactionScope()) {
    connection.EnlistTransaction(Transaction.Current);
    // ...
    // Do something with the connection/transaction.
    // Do not use async since the transactionscope cannot be used/disposed outside the 
    // thread where it was created.
    // ...
}

Answer 5

For targeting .NET Framework 4.6+, .NET Core 2.1+ or .NET Standard 2.0+

Consider using Microsoft.Data.SqlClient, which brings together the System.Data.SqlClient components of .NET Framework and .NET Core under one roof. Also useful if you'd like to use some of the newer SQL Server features.

Check out the repo or pull from nuget.

Add using statement after adding the package:

using Microsoft.Data.SqlClient;

Example using C# 8:

// transaction scope
using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);

// connection
await using var connection = new SqlConnection(_connectionString);

// open connection asynchronously
await connection.OpenAsync();
await using var command = connection.CreateCommand();
command.CommandText = "SELECT CategoryID, CategoryName FROM Categories;";

// run command asynchronously
await using var dataReader = await command.ExecuteReaderAsync();

while (dataReader.Read())
{
    Console.WriteLine("{0}\t{1}", dataReader.GetInt32(0), dataReader.GetString(1));
}

scope.Complete();