14

I have a routine which uses a recursive loop to insert items into a SQL Server 2005 database The first call which initiates the loop is enclosed within a transaction using TransactionScope. When I first call ProcessItem the myItem data gets inserted into the database as expected. However when ProcessItem is called from either ProcessItemLinks or ProcessItemComments I get the following error.

“The operation is not valid for the state of the transaction”

I am running this in debug with VS 2008 on Windows 7 and have the MSDTC running to enable distributed transactions. The code below isn’t my production code but is set out exactly the same. The AddItemToDatabase is a method on a class I cannot modify and uses a standard ExecuteNonQuery() which creates a connection then closes and disposes once completed.

I have looked at other posting on here and the internet and still cannot resolve this issue. Any help would be much appreciated.

using (TransactionScope processItem = new TransactionScope())
{
    foreach (Item myItem in itemsList)
    {
        ProcessItem(myItem);
    }   
    processItem.Complete();
}    
private void ProcessItem(Item myItem)
{
    AddItemToDatabase(myItem);
    ProcessItemLinks(myItem);
    ProcessItemComments(myItem);
}    
private void ProcessItemLinks(Item myItem)
{
    foreach (Item link in myItem.Links)
    {
        ProcessItem(link);
    }
}   
private void ProcessItemComments(Item myItem)
{
    foreach (Item comment in myItem.Comments)
    {
        ProcessItem(comment);
    }
}

Here is the top part of the stack trace. Unfortunately I can’t show the build up to this point as its company sensitive information which I cannot disclose.

   at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
   at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
John Saunders
  • 160,644
  • 26
  • 247
  • 397
Cragly
  • 3,554
  • 9
  • 45
  • 59
  • Could you post the stack trace of the exception ? – Thomas Levesque Mar 23 '10 at 17:31
  • I have added as much of the trace as allowed. Hope it is enough! – Cragly Mar 23 '10 at 18:31
  • Can you post the entire exception? Post the output of ex.ToString(), then redact anything sensitive. I'm wondering if there's an InnerException or two. – John Saunders Mar 23 '10 at 19:50
  • Hi Cragly, Did you solve this issue? We are facing same problem, and not able to find any hint on how to fix it. – Samir Vaidya Feb 18 '11 at 10:47
  • 2
    It was due to my transaction timing out for some reason (suggested by shake). I spent days hunting down the issue but could not get to the bottom of it. In the end I AddItemToDatabase(myItem) and if that worked processed the rest. If it didn't then threw exception up the stack. If the add to database worked and anything else failed I had a try catch to remove the item I added to the database. Not an ideal solution but was the only way to get it to work and for me to hit my headline :). Hope that helps. – Cragly Feb 20 '11 at 11:47

2 Answers2

26

Distributed transactions made my hair go gray prematurely :)

Usual suspects

  1. Firewall is blocking MSDTC
  2. Your transaction is timing out for some reason (try increasing the timeout)
  3. You have another transaction scope somewhere on the top of the code that is messing with the current transaction

Test whether MSDTC is working fine using tools like dtcping

Also test by inserting a small number of elements at first. Your code seems to be in a recursive loop which can process a large amount of data. Maybe you are running to many queries and the transaction is timing out.

Sometime System.Transactions.Transaction.Current has some clues on what happened. Add a watch against this global variable

shake
  • 1,752
  • 4
  • 18
  • 22
  • Yeah my hair has started falling out! Will provide an update on how I get on with the above suggestions. – Cragly Mar 23 '10 at 22:32
  • 1
    Timeout has caught me a couple of times. It's easy to forget about the distributed transaction, especially when debugging. It's worth noting the default is only 1 minute. You can increase it (e.g. to 10mins) by adding this to your app.config - ``, or passing as a parameter to `TransactionScope()` – Rhumborl Sep 26 '16 at 11:06
2

The default max timeout is 10 minutes. You can override this is in the machine.config however:

<configuration>
    <system.transactions>
        <machineSettings maxTimeout="00:00:30" />
    </system.transactions>
</configuration>

Or you can use reflection to override it in code:

    private static void OverrideTransactionScopeMaximumTimeout(TimeSpan timeOut)
    {

        // 1. create a object of the type specified by the fully qualified name

        Type oSystemType = typeof(global::System.Transactions.TransactionManager);

        System.Reflection.FieldInfo oCachedMaxTimeout = oSystemType.GetField("_cachedMaxTimeout", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Static);

        System.Reflection.FieldInfo oMaximumTimeout = oSystemType.GetField("_maximumTimeout", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Static);

        oCachedMaxTimeout.SetValue(null, true);

        oMaximumTimeout.SetValue(null, timeOut);

        // For testing to confirm value was changed

        // MessageBox.Show(string.Format(&quot;DEBUG SUCCESS!! &nbsp;Maximum Timeout for transactions is &#39;{0}&#39;&quot;, TransactionManager.MaximumTimeout.ToString()));

    }

More information: https://blogs.msdn.microsoft.com/ajit/2008/06/18/override-the-system-transactions-default-timeout-of-10-minutes-in-the-code/

Gabe
  • 837
  • 9
  • 13