1

The question: How do you combine Linq, Azure and database connections with transactionscope, without getting transactions elevated to distributed?

Specifically, is workaround/solution 2 (see below) an acceptable solution, or is there a better way?

Background info:

Fetching the connection string from .config file.

using (var db = new DBDataContext())
using (var scope = new TransactionScope())
{
    // Do DB stuff, then call a method that has more Linq code..
    //..accessing the _same_ DB, and needs to be inside this transaction.
}

This seems to be best practice, and works fine when debugging on localhost.

When deployed to Azure, the transaction is 'elevated' to distributed, as soon as a Linq query is executed, despite the fact that we are using the exact same connection string. This causes a runtime exception.

Note that the called method has its own "using DBDataContext()", but no new transactionscope.

It seems that the connection pool manager is not certain that the new connection is to the same database, even though the connectionstring is identical.

There seem to be 3 workarounds:

1) Pass a reference to the existing connection - Not acceptable. There are literally hundreds of methods that invoke the DB. It is not the callers responsibility.

2) Use a global (data layer) connection manager - This is NOT best practice, and should be avoided. But why?

3) Use integrated security - The connection pool manager may recognize the connection as identical to the existing connection when using integrated security. - Have not tested this, because this solution is unacceptable. Should not be forced to use integrated security because of this issue.

Edit:

Using Azure SQL Database (NOT SQL Server on Azure VM).

Azure SQL Database does NOT support distributed transactions.

Improve this question

1 Answer 1

Reset to default
1

You kind of answered your own question here:

When deployed to Azure, the transaction is 'elevated' to distributed, as soon as a Linq query is executed, despite the fact that we are using the exact same connection string. This causes a runtime exception.

Any time a second connection is involved, the transaction is elevated. There may be some optimized cases that circumvent this (I'm not aware of any), but I don't think there's much you can do here. The same connection should be reused.

Think of how it would work without TransactionScope. Your code might look like:

using (var cn = GetDbConnection())
using (var tx = cn.BeginTransaction()) 
{
    // do stuff with tx...

    using (var cn2 = GetDbConnection())
    {
        // Now think about the transaction scope here... 
        // There is no way for cn2 to reuse cn's transaction.
        // It must begin its own transaction. The only way to manage 
        // disconnected transactions of this nature is to elevate to
        //  a distributed transaction.
    }
}

Edit: With regards to your question about a global connection manager, I'm not sure it's a bad idea, depending on your implementation. For the ASP.NET use case, we typically scope the database context per request. Any code down the chain that requires a connection should have its database context injected.

This ensures the same context (connection) is shared across the entire request. The transaction can then be committed automatically or manually, or automatically rolled back in the case of an exception. This is a pretty simple use case and admittedly may not fit the bill for your scenario, but it's one that has worked pretty well for us.

Edit2: Using lightweight transactions, you can avoid elevation by closing one connection BEFORE the next is opened. The transaction itself remains open until the you call ts.Complete, even across connections.

https://blogs.msdn.microsoft.com/adonet/2008/03/25/extending-lightweight-transactions-in-sqlclient/

You open outer connection “A”. The pool has no free appropriate connection, so inner connection “z” is set up and enlisted in the transaction, establishing a lightweight transaction. You now close “A”, which sets aside “z” to wait for the transaction to end. Next you open outer connection “B” (you could also open “A” again and get the same results). “B” looks for a free inner connection in the pool attached to the transaction, doesn’t find one, creates inner connection “y” and tries to enlist it in the transaction. The transaction, now finding two different resources trying to enlist, must promote (resources in general, and sql connections in particular, cannot share local transactions). Finally you end the transaction, which sends the commit or rollback across “z”, disconnects it from the transaction and returns it to the pool.

So this brings us to the extensions we added for Sql Server 2008 support. On the server, we added a new connection reset mode that does not roll back local transactions. This allows SqlClient to return the inner connection to the pool to be reused. In our example, when you open “B”, it will finds “z” waiting in the pool, associated with the transaction where “A” put it when you closed “A”. “B” appropriates and resets “z” (with the transaction-preserving reset) and happily continues working. Neither System.Transaction nor the server are aware that the application sees “z” as two separate connections. As far as they are concerned, there is only one connection, working on a single local transaction and no promotion is necessary.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Yes, but how can we follow best practice and use transactions on an Azure SQL Database (which does not support distributed transactions).
See my edit about managing connection scope via DI. You'll have to design your data access code to avoid multiple connections.
See msdn.microsoft.com/en-us/library/… for more details on why the transaction gets elevated.
Added second edit highlighting lightweight transaction support for SQL 2008+ (should include Azure SQL)
I would have liked to frame the question to be more concise, but this answer was sufficient to make it clear that however problematic a global (data layer) connection manager may be, it is a requirement when using Azure SQL Database and needing transactions. Thanks Chris.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.