How to use nested TransactionScopes against an Azure SQL Database

Question

I'm currently trying to use nested transaction scopes for DB access against an Azure SQL Database.

I'm using the following code (.Net 4.5.1, my code is async all the way down, it's ASP.Net MVC with EF6.1):

public async Task Test()
{
    // In my actual code, the DbContext is injected within the constructor
    // of my ASP.Net MVC Controller (thanks to IoC and dependency injection)
    // The same DbContext instance is used for the whole HttpRequest
    var context = new TestContext();

    using (var t1 = StartTransactionForAsync())
    {
        using (var t2 = StartTransactionForAsync())
        {
            context.Users.Add(new User { Name = Guid.NewGuid().ToString() });
            await context.SaveChangesAsync();

            t2.Complete();
        }
        ... // Some more code here
        t1.Complete();
    }
}

private static TransactionScope StartTransactionForAsync()
{
    return new TransactionScope(
        TransactionScopeOption.Required,
        new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },
        TransactionScopeAsyncFlowOption.Enabled);
}

Everything is fine, except sometimes the TransactionScope is escalating to MSDTC which is (obviously) not supported by Azure SQL Database. So I sometimes get the following error:

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.

I could add Enlist=False to my connection string, but it would break the code above, as the inner transaction would still insert to database even if the outer TransactionScope is disposed without Complete.

I'm targeting a single Database, using a single Entity Framework context for my whole HttpRequest, always with the same connection string.

So my questions are:

• are nested transactions supported by Azure SQL Database at all?
• why is the above code sometimes escalating to MSDTC?

The official documentation says:

Microsoft Azure SQL Database does not support distributed transactions, which are transactions that affect several resources. For more information, see Distributed Transactions (ADO.NET).

Starting with the version 2.0, application transactions may be automatically promoted to distributed transactions. This applies to applications that use the System.Data.SqlClient class to perform database operations in the context of a System.Transactions transaction.

Transaction promotion occurs when you open multiple connections to different servers or databases within a TransactionScope, or when you enlist multiple connections in a System.Transactions object by using the EnlistTransaction method. Transaction promotion also occurs when you open multiple concurrent connections to the same server and database either within the same TransactionScope or by using the EnlistTransaction method.

Starting with the version 3.5, the transaction will not be promoted if the connection strings for the concurrent connections are exactly the same. For more information about transactions and avoiding transaction promotion, see System.Transactions Integration with SQL Server (ADO.NET).

which doesn't answer any of my questions.

Answer 1

Try adding this to your connection string, it turn on the Multiple Active Result Sets. This supposed to stop the MSDTC problem; even though I am not so sure about this.

MultipleActiveResultSets=True;

On extra information, nested transaction is not really nested transaction.

Answer 2

MSDN: Promotion of a transaction to a DTC may occur when a connection is closed and reopened within a single transaction. Because the Entity Framework opens and closes the connection automatically, you should consider manually opening and closing the connection to avoid transaction promotion.

To Avoid this scenario: How to Manually Open the Connection from the Object Context