CODEWITHSUNDEEP
c#.nettransactionstransactionscopesqltransaction
Baahubali
Baahubali

Reputation: 4802

TransactionScope with nested sql transactions

I have inherited a code that is executing 5 different stored procedures which are updating in the back-end and each have their own transaction object. i wrapped all 5 sql transactions within the transaction scope object as they all need to commit or rollback at the same time and now it's throwing the following error:

An exception of type 'System.Transactions.TransactionManagerCommunicationException' 
occurred in applicationname.dll but was not handled in user code

Additional information: 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.

This is the function where as soon as the code hits, it throws the exception:

 Protected Overridable Function GetParameters(ByRef inConnection As SqlClient.SqlConnection _
                                    , ByVal inStoredProcedureName As String _
                                    , ByVal inIncludeReturnValue As Boolean) As SqlClient.SqlParameter()

    Return SqlHelperParameterCache.GetSpParameterSet(inConnection, inStoredProcedureName, inIncludeReturnValue)
End Function

This article is explaining how to fix this issue: The transaction manager has disabled its support for remote/network transactions

but i do not want to enable these options without an understanding of the issue. can't i wrap sqltransactions inside a transactionscope or this issue is happening because they are using their connections?

--Update 1

Our DBA has now enabled MSDTC and it works on all calls except where i am calling a WCF webservice. It just throws me a timeout exception now:

 The operation has timed out 
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

  Exception Details: System.Net.WebException: The operation has timed out

 Source Error: 


  An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 

[WebException: The operation has timed out]
System.Net.HttpWebRequest.GetResponse() +8420880
System.ServiceModel.Channels.HttpChannelRequest.WaitForReply(TimeSpan timeout) +234

[TimeoutException: The HTTP request to 'http://test/testValidationService/test.svc' has exceeded the allotted timeout of 00:01:00. The time allotted to this operation may have been a portion of a longer timeout.]

System.ServiceModel.Channels.HttpChannelUtilities.ProcessGetResponseWebException(WebException webException, HttpWebRequest request, HttpAbortReason abortReason) +7074108
   System.ServiceModel.Channels.HttpChannelRequest.WaitForReply(TimeSpan timeout) +16650152
   System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout) +388


[TimeoutException: The request channel timed out while waiting for a reply after 00:00:59.9843749. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout.]
System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +14579646
System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +622

Update 2

The timeout seemed to be caused by a lock. So i have a transaction within a transactionscope and i thought that transaction will be ignored now since transactionscope is the wrapper around but apparently i still have to commit or rollback that individual transaction, which seems to have fixed the issue. from what i read, that individual commit/rollback will be ignored as last say will be by transactionscope? is that right? could someone please shed any light on it?

Upvotes: 2

Views: 2226

Answers (1)

shbht_twr
shbht_twr

Reputation: 540

Which SQL Server version are you using?

In SQL server 2005 and earlier:

  1. DTC escalation will take place as soon as you open multiple connections to db.

  2. To prevent escalation use same SqlConnection object in all queries within the transaction.

In SQL server 2008 and above:

  1. Opening and closing connections so that only one connection is open at a time won't escalate.
  2. Nested sqlconnections will immediately escalate to DTC.

Refer this question for more details on this. TransactionScope automatically escalating to MSDTC on some machines?

Upvotes: 4

Related Questions