Filling SqlDataAdapter under TransactionScope triggers Distributed Transaction

Question

The TransactionScope handles perfectly many levels of nested transactions, by adhering to the 'highest' transaction. I'm using nesting TransactionScope with IsolationLevel: ReadCommitted and TransactionScopeOption: Required, exacly as described in this article.

Inside the Scope, the call to

ladpAdapter.Fill(ldstDataSet)

triggers, as I see in Profiler, DTCTransaction. This results in the exception:

System.Transactions.TransactionManagerCommunicationException: Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool. ---> System.Runtime.InteropServices.COMException: The transaction manager has disabled its support for remote/network transactions.

Turning on the MSDTC service and configuring it over network security (Network DTC Acces => yes, AllowInbound => yes, AllowOutbound => yes) fixes the error, however is not an acceptable solution, due to lack of support for AlwaysOn prior to sql server 2016 (source).

This appears to not be a problem with multiple connections, as all are closed after use and there is no multithreading in this section.

The code looks something like this (pseudo code):

Using transactionScope As TransactionScope = CreateTransactionScope()

SomeFunction()'works fine, calls ExecuteScalar
SomeFunction()'works fine, calls ExecuteScalar
SomeFunction()'works fine, calls ExecuteScalar
SomeFunctionWithFill()'breaks, calls Execute

transactionScope.Complete()
End Using

where SomeFunction calls execute or executeScalar, and Execute and ExecuteScalar both do basicly the same:

   Try
            Dim lcnnDB As SqlConnection = New SqlConnection(GetConnectionString())
            Dim lcmdCommand As SqlCommand = New SqlCommand(lstrStoredProcedure, lcnnDB)
            Dim lstrStoredProcedure As String = GetStoredProcedure()
            lcmdCommand.Connection.Open()


            Return lcmdCommand.ExecuteScalar()

    Catch ()'rest ommited

but Execute also fills Adapter like this:

ladpAdapter = New SqlDataAdapter(lstrStoredProcedure, lcnnDB)  
ldstDataSet = New DataSet
ladpAdapter.Fill(ldstDataSet)

Why is a call to Fill() elevating to DTC transaction? Is it because it uses default incompatible serializable transaction under the hood?

Thanks for any clues and observations.

Filling SqlDataAdapter under TransactionScope triggers Distributed Transaction

0 Answers0