none
Begin-EndExecuteNonQuery prematurely invokes Callback and throws a command timeout Exception when invoking a stored procedure. RRS feed

  • Question

  • Per the MSDN documentation, all asynchronous ADO.NET SqlCommand operations ignore the CommandTimeout property setting and will execute until the asynchronous operation is complete.

    I have found a case where this is not true.    I first encountered this as a seemingly random duration timeout exception.  This occured when executing a background purge stored procedure.   This background stored procedure executes a large number of small transactions to prevent blocking access to other portions of the system during cleanup.     

    Because we cannot release an application with random crashes in it; I pined this down by writing a simple application :-).  I have isolated this case to a stored procedure execution with BeginExecuteNonQuery.   The stored procedure must BEGIN and COMMIT (not nested) a large number of transactions.   I empirically found this was 286 or more transaction in two different test environments.  I tested against both local and remote SQL instances.  

    After this threshold of transactions are initiated/completed the Callback is invoked even though the operation is not complete.   After this occurs, the call to EndExecuteNonQuery blocks for whatever the value of "CommandTimeout" is set to. 

    I confirmed with WireShark that SQL Server sends a TDS message that triggers the callback.  Once the callback is fired, a timer is initiated that waits "CommandTimeout" seconds.  

    This is obviously either a bug or a major gap in the documentation.   The only viable workarounds are to set CommandTimeout to 0 or convert this to a synchronous call and spin up a thread to manage the lifetime.   Unfortunately, setting the timeout to 0 makes this a blocking threadpool thread for a potentially very long operation.  This is not really acceptable for a high performance app that utilizes the threadpool.

    Has anyone else encountered this issue?   We're you able to alter your stored procedure or SQL statement to prevent the callback from being triggered?

    My test environment:

    I reproduced the issue on both my physical dev system and a VM.   I used remote and local SQL instances.

    ·          Software Environment:

    o    Windows 7 Enterprise (fully patched)

    o    SQL Server

    §   Local instance of SQL 2008 R2 Developer edition.

    §   Remote instance of SQL 2008 Express edition.

    o    .NET 4.

    Any help is appreciated.   I'll be filing a bug with sample code to reproduce the issue.

    - Thanks,

    HazMat

    • Edited by HazzMatt Wednesday, December 15, 2010 10:41 PM readability
    Wednesday, December 15, 2010 10:38 PM

All replies

  • Please, post your code and TSQL that you are using, so we can be more specific.

    Tks


    What can I do for you?
    Tuesday, July 12, 2011 10:34 AM