none
TableAdapter.Update() Throws Timeout Exception Randomly RRS feed

  • Question

  • DataTable contains only 3-4 rows of data.

    When calling the Update() method I get an SQL timeout exception. Increased timeout to 60 seconds but still timed out.

    The data is quite small that is being saved.

    This issue happens randomly. Usually takes 4-40 ms, but occasionally it times out.

    Looks as though the process hangs.

    Using SQL Express 2008 R2 on a desk top machine. Not client server.

    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
       using (SqlConnection connection = new SqlConnection(connectionString))
       {
          TableAdapterA ta_A = new TableAdapterA();
          TableAdapterB ta_B = new TableAdapterB();
          TableAdapterC ta_C = new TableAdapterC();
    
          ta_A.Connection = connection;
          ta_B.Connection = connection;
          ta_C.Connection = connection;
    
          connection.Open();
    
          ta_A.Update(dataTableA);
          ta_B.Update(dataTableB); // <== Times out here
          ta_C.Update(dataTableC)
          scope.Complete();
        }
    }

    Friday, November 7, 2014 1:53 AM

All replies

  • Hello Scott_PKI,

    >>ta_B.Update(dataTableB); // <== Times out here

    If it is means this error occurs in the ta_B.Update() method every time. If so, please try to run the three update method separately(when testing ta_A.Update(), comment the Bs and Cs) to narrow down this issue.

    Since you are using SQL Server, you could use the SQL Server Profiler to trace and watching what happens when you run the three update methods include the deadlock graph to see if you're experiencing deadlocks too.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 7, 2014 7:23 AM
    Moderator
  • I've used SQL Server Profiler and it did not show any deadlocks or any other issues.

    dataTableB contains an XML column each row contains about 6 KB of XML.

    When the timeout occurs I noticed that some of the first rows had their header stripped out which was:

         <?xml version="1.0"?>

    The remaining rows still contain this XML header. So I'm assuming this is where the timeout occurred, meaning that the remaining rows were not updated since SQL timed out.

    Is SQL Server writing this value back to the data table? Is this what may be causing this timeout?

    I replaced taB.Update() with an SqlCommand using an SQL INSERT INTO. This worked and had no timeouts.

    What seems to be the issue using a TableAdapter? 

    Thanks for any help,

    Scott



    • Edited by Scott_PKI Tuesday, November 11, 2014 12:30 AM
    Monday, November 10, 2014 10:01 PM
  • Hello Scott_PKI,

    >>Is SQL Server writing this value back to the data table?

    I do not quite sure about this since we do know how you define you table, could you please share the table schema, the XML data in that column and what you do to the dataTableB after you fill it with the data in database.

    >>Is this what may be causing this timeout?

    For narrowing down this issue, i suggest that you could try to update other column which is (N)Varchar type to see whether this issue is caused by the XML column. And please increase the timeout value to be lareger as 180 seconds, I am wondering the update for the tableb costing much time, please keep an eye on the StartTime and EndTime in the SQL Serevr Profiler for the update tableB, to check how much time it totally costs. If it is more than 60s so that it throws a timeout issue.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, November 11, 2014 9:22 AM
    Moderator
  • Hi Fred,

    I am unable to post the schema and data due to company proprietary issues but the XML is quite flat:

    <TableName>
        <BoolData>False</BoolData>
        <DoubleData>247.456029838483</DoubleData>
        ...160 similar entries as above
    </TableName>

    I did however save the XML in a nvarchar(max) column instead of the XML column and there were no timeouts.

    Times to execute the .Update() call:

    Minimum value was 0ms, maximum value was 562ms the average was 86ms.

    Note this has been done using Visual Studio 2010 and .NET Framework 4.0.

    Regards,

    Scott


    • Edited by Scott_PKI Sunday, November 16, 2014 12:30 AM
    Sunday, November 16, 2014 12:10 AM
  • ta_A.Update(dataTableA); ta_B.Update(dataTableB); // <== Times out here
    ta_C
    .Update(dataTableC)

    Please try to close the connection before allowing a new TableAdapter to work on the connection

    ta_A.Update(dataTableA);

    connection.Close();

    connection.Open();

    ta_B.Update(dataTableB);

    connection.Close();

    connection.Open();

    ta_C.Update(dataTableC);

    Sunday, November 16, 2014 7:19 AM
  • It may have to do with the default IsolationLevel when using TransactionScope. When not specified when creating a new TransactionScope, the default for its IsolationLevel is Serialized … the most restrictive that there is (and could slow things down considerably). On the other hand, for Transactions in SQL Server, the default IsolationLevel is ReadCommitted and, for the most part, that is what you typically will want to use. Take a look at my blog post about this for further explanation and for an easy way to utilize TransactionScope:
    http://geek-goddess-bonnie.blogspot.com/2010/12/transactionscope-and-sqlserver.html

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, November 23, 2014 6:03 AM
  • Hi Bonnie,

    Thanks for the tip about the default Isolation Level condition when using TransactionScope().

    However as a test I removed the TransactionScope and the same timeout issue occurred.

    Sunday, November 23, 2014 10:01 PM
  • Hi Scott,

    Since saving to a varchar(max) column instead of to an XML column solved the timeout problem, is it possible for you to change your database schema to use the varchar(max) permanently? Or would that cause a lot of other headaches? 

    Perhaps there's a bug with regard to XML columns in either .NET 4.0 Table/DataAdapters or SQL Express 2008 R2.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, November 23, 2014 10:23 PM
  • Hi Bonnie,

    Need to keep the XML column for use with xquery.

    I am also starting to believe there is an issue using XML column with table adapters too.

    The solution was to replace the table adapter calls with SqlCommand using INSERT INTO and continue to use XML column.

    Doing this no longer caused timeouts.

    Thanks,
    Scott

    Friday, December 5, 2014 10:16 PM
  • That's good to know, Scott. Another reason to not like TableAdapters (I don't like them, and never use them). I would have suggested not using the TableAdapter earlier in this thread, but some people really like them and you may have been one of them.  ;0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, December 5, 2014 10:25 PM
  • Scott,

    I've never used this class, but I know a little bit what for some strange experiences a transaction can give. 

    First what does it. It physically locks all data which is in use during the transaction. Therefore the transactions should take as less time as possible and never been interrupted by any other handling (I don't see that in your case).

    However, you don't rollback at an error (which is not possible with the transactionscope it seems as it is done automatically). But implement then at least the event which handles that. 

    http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx


    Success
    Cor


    Saturday, December 6, 2014 8:43 AM