Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction

Question

Just curious if anyone else has got this particular error and know how to solve it?

The scenario is as follow...

We have an ASP.NET web application using Enterprise Library running on Windows Server 2008 IIS farm connecting to a SQL Server 2008 cluster back end. MSDTC is turned on. DB connections are pooled.

My suspicion is that somewhere along the line there is a failed MSDTC transaction, the connection got returned to the pool and the next query on a different page is picking up the misbehaving connection and got this particular error. Funny thing is we got this error on a query that has no need whatsoever with distributed transaction (committing to two database, etc.). We were only doing select query (no transaction) when we got the error.

We did SQL Profiling and the query got ran on the SQL Server, but never came back (since the MSDTC transaction was already aborted in the connection).

Some other related errors to accompany this are:

• New request is not allowed to start because it should come with valid transaction descriptor.
• Internal .Net Framework Data Provider error 60.

Answer 1

MSDTC has default 90 seconds timeout, if one query execute exceed this time limit, you will encounter this error when the transaction is trying to commit.

Answer 2

A bounty may help get the answer you seek, but you're probably going to get better answers if you give some code samples and give a better description of when the error occurs.

Does the error only intermittently occur? It sounds like it from your description.

Are you enclosing the close that you want to be done as a transaction in a using TransactionScope block as Microsoft recommends? This should help avoid weird transaction behavior. Recall that a using block makes sure that the object is always disposed regardless of exceptions thrown. See here: http://msdn.microsoft.com/en-us/library/ms172152.aspx

If you're using TransactionScope there is an argument System.TransactionScopeOption.RequiresNew that tells the framework to always create a new transaction for this block of code:

    Using ts As New Transactions.TransactionScope(Transactions.TransactionScopeOption.RequiresNew)         ' Do Stuff     End Using 

Also, if you're suspicious that a connection is getting faulted and then put back into the connection pool, the likely solution is to enclose the code that may fault the connection in a Try-Catch block and Dispose the connection in the catch block.