Avoiding distributed transactions (DTC) with SQL Server and async code

Wrapping async code in transaction scope is not as straightforward as sync one.

Let's say we have some simple code:

await using (var connection = new SqlConnection(connectionString))
{
	await using var command = new SqlCommand("select 1", connection);
	await connection.OpenAsync();
	await command.ExecuteScalarAsync();
}

We can wrap it in transaction scope and test that it still works:

using var ts = new TransactionScope();
await using (var connection = new SqlConnection(connectionString))
{
	await using var command = new SqlCommand("select 1", connection);
	await connection.OpenAsync();
	await command.ExecuteScalarAsync();
}
ts.Complete();

But if you try to run this code you will get: "A TransactionScope must be disposed on the same thread that it was created" exception. 

The fix is easy: we need to add TransactionScopeAsyncFlowOption.Enabled option to the constructor:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
await using (var connection = new SqlConnection(connectionString))
{
	await using var command = new SqlCommand("select 1", connection);
	await connection.OpenAsync();
	await command.ExecuteScalarAsync();
}
ts.Complete();

I've also added IsolationLevel.ReadCommitted. Read committed is a default transaction level at SQL server but TS has 'Serializable' as default, and that's probably not what you want. So if we isolate our code to some method for simplicity we`ll get:

static async Task QueryDbAsync()
{
	await using (var connection = new SqlConnection(connectionString))
	{
		await using var command = new SqlCommand("select 1", connection);
		await connection.OpenAsync();
		await command.ExecuteScalarAsync();
	}
}

and run it:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
await QueryDbAsync();
ts.Complete();

Note that a new connection is opened inside the method and it's aware of the ambient transaction we created outside, we didn't have to specify anything. You probably want to have more than one method in a transaction scope, let's try:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
await QueryDbAsync();
await QueryDbAsync(); 
ts.Complete();

Works! Let's run those in parallel?

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
await Task.WhenAll(QueryDbAsync(), QueryDbAsync());
ts.Complete();

Ups, now (if you dont have Microsoft Distributed Transaction Coordinator \MSDTC  configured)
you`ll get: "This platform does not support distributed transactions" or "The Promote method returned an invalid value for the distributed transaction"  exception.

The reason is, we tried to use the same opened connection for another request and it got escalated to a distributed transaction. We are connecting to the same database, so we don't need a distributed transaction. DTC consumes resources, slows down your requests, is not supported by all OS and has none to limited support for the cloud. So what works on your machine might not work on a prod. And as I said, we don't need it, we don't have distributed resources, its the same DB after all.

So, what happened? SQL Allows multiple connections within a single Transaction Scope only if the connections are not open at the same time. We tried to open 2 connections in parallel, we broke the rules. 

The bad news is, that means the same result can be 'achieved' without parallel actions but using nested connections:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
await using (var connection = new SqlConnection(connectionString))
{
	await using var command = new SqlCommand("select 1", connection);
	await connection.OpenAsync();
	await command.ExecuteScalarAsync();
	await using (var nestedConnection = new SqlConnection(connectionString))
	{        
            //lets open connection inside a connection
            await nestedConnection.OpenAsync();
        }
}ts.Complete();

You normally want to close your connection asap, right after you executed your command. Opening one connection inside the other indicates some issues with application design.

In our case it's easy to fix by disposing of the connection before we open another one:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
            
await using (var connection = new SqlConnection(connectionString))
{
	await using var command = new SqlCommand("select 1", connection);
	await connection.OpenAsync();
	await command.ExecuteScalarAsync();
}

await using (var nestedConnection = new SqlConnection(connectionString))
{
	await nestedConnection.OpenAsync();
}

ts.Complete();

It can be not always that obvious. That's the same code as above but refactored to new c#8 bracketless 'using declarations' and it will stop working again:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
await using var connection = new SqlConnection(connectionString);
await using var command = new SqlCommand("select 1", connection);
await connection.OpenAsync();
await command.ExecuteScalarAsync();

await using var nestedConnection = new SqlConnection(connectionString);
await nestedConnection.OpenAsync();

ts.Complete();

The lifetime of a 'using' extend to the end of the scope in which it is declared, so we won't close the first connection after we stop using it.

To fix it we need to close connection explicitly once we don't need it:

await command.ExecuteScalarAsync();
await connection.CloseAsync();

But sometimes nested connections or parallel execution can be already a part of a system or a framework that you can not change.

What are the other options to fix that? We can pass a connection to every method of every repo :

static async Task QueryDbAsync2(SqlConnection connection)
{
	await using var command = new SqlCommand("select 1", connection);
	await command.ExecuteScalarAsync();	
}

Let's test:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
await Task.WhenAll(QueryDbAsync2(connection), QueryDbAsync2(connection));
ts.Complete();

Note:
I`m writing this code for the sake of example, but most probably parallel execution of requests from the same connection is not what you want. I had to enable MARS to make examples work (MARS ) But it comes with a batch of restrictions. For example, MARS operations execute synchronously on the server, so, even though my requests to the server are departing in parallel, the processing of those requests will be synchronous.

The need to create and pass a connection to every single repo method can overcomplicate your code and eliminates the purpose of the ambient transaction. What if we make some factory or context that can provide methods connection? One option I`ve seen a lot is to keep connection instance for a lifetime of a request You can do it using some DI tricks like AddScoped in .NET Core but that won't work for background services, each of those will keep connection for a lifetime of the application.

AsyncLocal

The most common way of solving it was keeping connection in a thread using 'Threadlocal'.

Bad news: that won't work with async code, because your code execution can jump from thread to thread.

Good news: AsyncLocal allows to keep context during async flow : AsyncLocal 

//lets keep connection in a thread using AsyncLocal
static AsyncLocal<SqlConnection> DbContext= new AsyncLocal<SqlConnection>();

//we need to change that method a bit
static async Task QueryDbAsyncV3()
{
	//now we get connection from context
	DbContext.Value ??= new SqlConnection(connectionString);

	await using var command = new SqlCommand("select 1", DbContext.Value);
	DbContext.Value.Open();
	try
	{
		await command.ExecuteScalarAsync();
	}
	finally
	{ 
		//note that we can`t use 'using' with the connection anymore, 
                //cause it will dispose the connection and might still need it
		DbContext.Value.Close();
	}
}

So, how will it go with two requests?

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
try
{
	await Task.WhenAll(QueryDbAsyncV3(), QueryDbAsyncV3());
	ts.Complete();
}
finally
{
    	ts.Dispose();
        DbContext.Value?.Dispose();
        DbContext.Value = null; //to make sure no one will try to use disposed connection
}

Ups, whats going on again? Maybe its concurrency again? Let's check that context is available after we set it up:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
try
{
	await QueryDbAsyncV3(); //context should be created inside

	//and we expect that AsyncLocal value is not null here
	if (DbContext.Value == null)
		throw new ArgumentException("But it is null :(");

	ts.Complete();
}
finally
{
	ts.Dispose();
	DbContext.Value?.Dispose();
	DbContext.Value = null;
}

Hmm, we set a context in QueryDbAsyncV3 but after returning from the method it is null again. 

Does it mean AsyncLocal doesn't work?

Well, it does, but not as straightforward as you might expect: "The way that this actually works is that as part of the async compiler transformation, the compiler-generated code will notify the logical call context that it needs to establish a copy-on-write scope." This provides a way for contextual information to flow “down” asynchronous calls. Note that the value does not flow “up”. https://blog.stephencleary.com/2016/12/eliding-async-await.html 

In simple words: in order for that to work, we need to setup context before awaiting for QueryDbAsyncV3:

The best is probably to create a connection as close as possible to where we create transaction scope:

var options = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
var ts = new TransactionScope(TransactionScopeOption.Required, options, 
TransactionScopeAsyncFlowOption.Enabled);
try
{
	//setup Connection
	DbContext.Value = new SqlConnection(connectionString);

	//run
	await QueryDbAsyncV3();
	await QueryDbAsyncV3();

	//now it works because it was initialized on the same 'level' and flows only “down”
	if (DbContext.Value == null)
		throw new ArgumentException("You will not see me!");

	ts.Complete();
}
finally
{
	ts.Dispose();
	DbContext.Value?.Dispose();
	DbContext.Value = null;
}

Sum up

• adding transaction scope might be not as easy as it looks 
• don't setup dtc just to solve the issue 
• TS works fine only if you guarantee to have one connection at a time, prefer that if you can, other options come with side effects
• running parallel requests from the same connection is what you want to avoid
• storing connection for reuse makes things even more complicated
• asyncLocal provides values downstream only, you need to setup connection before you await for it
• try to keep things simple and avoid nested connections and parallel executions instead of hacking connection pool