TransactionScope always tries to promote to MSDTC

Question

I am trying to use a transaction scope inside a loop. The entire loop takes place using a single connection to the database. I am using entity framework 4 for database access. During the second iteration of the loop, when the LINQ to Entites query executes, an exception is thrown stating that the MSDTC on the server is unavailable.

I've read that explicitly opening the connection and then enlisting the transaction is supposed to solve this problem, but it has not. Below is sample code that mirrors the basic operation that is taking place.

Any ideas on how to prevent an escalation to MSDTC?

Using context = New MyEntities()
    Dim connection = context.Connection

    connection.Open()

    For index = 0 to (Me.files.Count - 1)
        Dim query = From d In context.Documents
                    Where (d.DocumentID = documentID)
                    Select d.Status

        Dim status = query.FirstOrDefault()

        Using trans = New TransactionScope()
            connection.EnlistTransaction(Transaction.Current)

            Dim result = context.UpdateStatus(True)

            If (result = 1) Then
                WriteToFile()
                trans.Complete()
            End If
        End Using
    Next
End Using

Edit: Instead of TransactionScope, if I use connection.BeginTransaction(), transaction.Commit(), and transaction.Rollback(), it works fine. However, I would still like to find a way to make TransactionScope work.

Does this forum post help? social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/… It looks like the recommendation is to start the transaction, then open the connection, then run your queries. — mkedobbs
The idea was to open the connect, then run all of the queries using one connection -- for performance reasons. — DCNYAM
There are no performance reasons for this if you use connection pooling. — Ladislav Mrnka

Adam Houldsworth Adam Houldsworth · Accepted Answer · 2011-05-11T07:59:04

TransactionScope originally had an issue where it would promote a transaction to a distributed transaction when it met another connection, even if all connections were to the same database. This was a known issue in the framework.

I believe they addressed this in .NET 4, what version are you using?

~~A workaround has been provided in this answer:~~

Why is TransactionScope using a distributed transaction when I am only using LinqToSql and Ado.Net

Basically the same as the comment to your question suggesting to actually only use one physical connection from the pool - so only one connection gets enlisted.

Reviewing your question again I can see the above won't likely make a different, as you only use one connection anyway. Perhaps try closing and re-opening the connection on each iteration explicitly, and use the benefits of connection pooling.

Or more ideally, drop the use of TransactionScope as IDbTransaction has enough scope here to cover your code.

TransactionScope always tries to promote to MSDTC

2 Answers