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? http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/c3fd7555-7fff-44b8-8e1e-030073c20597/ It looks like the recommendation is to start the transaction, then open the connection, then run your queries. — mkedobbs, May 05 '11 at 16:19
The idea was to open the connect, then run all of the queries using one connection -- for performance reasons. — DCNYAM, May 05 '11 at 16:22
There are no performance reasons for this if you use connection pooling. — Ladislav Mrnka, May 05 '11 at 20:42

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

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.

edited May 23 '17 at 12:33

Community

1
1

answered May 11 '11 at 07:59

Adam Houldsworth

63,413
11
150
187

Once you are using more then one connection the transaction must be promoted to distributed one - there is no difference if they connect to the same database or not. Local transaction is handled only on single connection when no other transactional resource is involved. – Ladislav Mrnka May 11 '11 at 08:06
The answer to this forum question suggests it is a limitation and will be addressed: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/3ce488eb-55a8-4535-adc7-c5b29a1523b5/ – Adam Houldsworth May 11 '11 at 08:09
However they aren't quite the same situation as the OP. The situation I seem to be comparing is when one transaction scope has multiple connections, whereas the OP seems to have one connection and multiple transaction scopes... not seen that one before. – Adam Houldsworth May 11 '11 at 08:11
As Adam suggested, I ended up using a new connection object inside each TransactionScope. While testing, there didn't seem to be any performance issues with this and it eliminates the MSDTC problem. – DCNYAM May 11 '11 at 16:11

score 1 · Answer 2 · answered May 11 '11 at 08:11

Did you check description for EnlistTransaction in MSDN? It says:

New in ADO.NET 2.0 is support for using the EnlistTransaction method to enlist in a distributed transaction. Because it enlists a connection in a Transaction instance, EnlistTransaction takes advantage of functionality available in the System.Transactions namespace for managing distributed transactions. Once a connection is explicitly enlisted in a transaction, it cannot be unenlisted or enlisted in another transaction until the first transaction finishes.

It only mentions distributed transaction. You can try to use connection.BeginTransaction instead. It will return instance of EntityTransaction and you will call Commit to complete transaction.

TransactionScope always tries to promote to MSDTC

2 Answers2