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.

Budge

TransactionScope was invented for implicit/automatic transaction (taking advantage of the MSDTC service), not for nested transaction. It seems you're confusing those terms. Escalation to DTC is probably happening because you have some async stuff (which means different threads, which probably means different connections). I hardly see why you want to put async stuff in this kind of (server?) code - beyond the current fashion around "async" :-). More on escalation here: #1691392 – Spae

@SimonMourier I'm not sure I understand your first sentence ; TransactionScope definitely supports nested transactions (see for instance https://mcmap.net/q/482277/-nested-child-transactionscope-rollback). Rollbacking the root scope should rollback the whole operation. As for the async stuff, I'm using it for scalability/performance reasons. That said, it doesn't change the fact that even if the two operations are executed on two different threads, they still use the same DB connection (a unique DbContext instance is used). – Budge

I never said TS does not support nested transactions. I said you're confusing both notions. Nested transaction existed long before DTC existed. You can do nested transaction w/o TS, but you're talking about nested scopes. Async will not bring any scalability benefit here, if behind the scene, you're serializing on one unique connection (it's in fact probably worse), which I still doubt anyway. IMHO you should show a full repro code to avoid wild guesses. – Spae

Distributed Transaction != Nested Transactions, first of all why do you need it? If nested transaction is already in the transaction, you can continue to operate in same transaction. You can easily change your business logic to join an existing transaction instead of creating new one, this is the concept of ambient transactions. – Carcinogen

I have seen this error when there is open reader and a new transactionscope is created, instead if you use .ToListAsync() to fetch all records instead of enumerating queries, this error will go away. – Carcinogen

@AkashKava A nested transaction is required here, because I'm calling existing business code that already needs a transaction from another code that needs a transaction too. For instance, I have a CreateUser method that requires a transaction, which can be called by another CreateOrganization method that needs to be rollbacked (as well as CreateUser) in case of an exception. But do note the inner transaction joins the ambient (outer) transaction as I'm using TransactionScopeOption.Required for both scopes. – Budge

If you are just setting TransactionScopeOption.Required, then it should work, try to find if you have foreach statement trying to query something, I had similar errors and when I modified them as for each (var item in itemQuery.ToList()) instead of 'foreach( var item in itemQuery)' all such nested transactions errors went away. Ideally EF should detect and give proper error, this is a misleading error. If there is an active query still with open reader and when you issue SaveChanges, this is the common error thrown by EF. – Carcinogen

@AkashKava LazyLoading is disabled and the exception is thrown after two INSERT operations, so unfortunately I don't think it's the case here. – Budge

I see another possibility, Async is probably interfering with another TransactionScope that was not initialized with Async option. Infact, I remember one more issue involving nested transaction, I kept a copy of current transaction in DbContext and created a wrapper around it to manually check if I created transaction somewhere earlier or not. I stopped using TransactionScope and started using DbTransaction. – Carcinogen

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.

Raceme

I was convinced MARS was enabled, but after double checking my connection string it actually isn't. Unless for Azure DB it's active by default, this really could be my issue – Budge

I'm running out of time (before the end of the bounty) to fully test if this fixes my issue, but this really was a problem with my connection string (I was sure MARS was enabled, but it actually wasn't). The fix looks promising, so I'm giving you the bounty + an upvote, but I'll accept the answer later after I'm able to properly test everything. Thanks! – Budge

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

Chemo

Answer 3

Azure SQL Database now supports promotion of a transaction to a distributed transaction from TransactionScope. That now makes it possible to use TransactionScope where it was previously not possible since MSDTC was not supported. As a result, you do not necessarily need to control connection open and close as suggested in the previous reply. See: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-transactions-overview/.

Also note that Azure DB currently still does not completely support Multiple Active Result Sets.

Chief