New transaction is not allowed because there are other threads running in the session execption with federation services

Question

We are developing an application which uses SQL Azure with Federation Services. We integrated the federation service logic into our application recently. Now, we noticed some strange SQL exceptions with the following message: "New transaction is not allowed because there are other threads running in the session." The appearance of this error is completely not deterministic.

Our application's logic is quite complex. We use Linq 2 SQL, and create a new DataContext (also a new connection) when we want to work on a different federation member. It is possible that multiple connections exist on the same thread directed to different federation members, but always one thread exists at the same time.

Sometimes we use transactions within one federation member. We begin the transaction manually with the SqlConnection class BeginTransaction method mixed with manually executed "BEGIN TRANSACTION" command. Bofore the transaction begin we send a "USE FEDERATION..." statement. We suspect always a transaction begin occures this issue. Without federation srvices, on a local sql server it runs without any problems.

Does anyone met with this issue? Do you have an idea what should cause this exception? We think that the indeterministical nature of the error may come from connection pooling and not properly closed connections. Is it possible that the connection got from the pool is not in a resetted state, so it in a transaction of an other thread?

Answer 1

see http://blogs.msdn.com/b/cbiyikoglu/archive/2006/11/21/mars-transactions-and-sql-error-3997-3988-or-3983.aspx

Answer 2

I am not sure what the issue could be, but two things come in mind:

- Read this bug on Connect related to this message. It contains interesting information about the underlying cause for this error message, and says that you don't need multiple threads to get to this error. http://connect.microsoft.com/VisualStudio/feedback/details/612369/misleading-error-message-in-sqlexception-when-using-entity-framework

- If somehow a transaction gets promoted to a distributed transaction, you would get an error, because SQL Azure doesn't support distributed transactions. I can't think of why you would hit this issue because you are using SqlTransaction; and that's not supposed to escalate to distributed transactions...

What if you change your code to only use BEGIN TRANSACTION in your T-SQL? Does the error still happen?

---

Herve Roggero, MVP SQL Azure Co-Author: Pro SQL Azure http://www.herveroggero.com