locked
SqlConnection.ClearPool and Changes in .Net 4.0 RRS feed

  • Question

  • Does anyone know of problems with SqlConnection.ClearPool(SqlConnection) in .Net 3.5?

    Very frequently when disposing transaction scopes, I'll see exceptions like so:

    System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.InvalidOperationException: The requested operation cannot be completed because the connection has been broken.

     

     

    This issue seems to be a hard-to-reproduce timing issue and it goes away permanently if we remove all usage of SqlConnection.ClearPool(SqlConnection).

    At first glance, it also seems to go away by upgrading to .Net 4.0.  Has anyone else ever seen problems with ClearPool(SqlConnection) or fixed them by upgrading to .Net 4.0?

     Thanks in advance.
     

    Friday, April 22, 2011 8:28 PM

Answers

  • It doesn't happen in ClearPool(SqlConnection) itself.  It happens after the very next time a TransactionScope is started and the connection string is used to connect to do work in SQL.  What I find is that there are occasions when the SqlConnection does a "hard" disconnect from SQL immediately after the first command finishes.  The SqlConnection is disconnected before the disposal of the TransactionScope.  This can be confirmed in SQL profiler and SSMS.

    ie. 

    ClearPool(SqlConnection)
    using(TransactionScope TransScope)
    {
    Command1(SqlConnectionString)
    TransScope.Complete()
    } <<< ****
    

    The end result is that, during TransactionScope disposal (the end of the using block), I get the TransactionAbortedException.  (copied below).

    I also get TransactionAbortedException's if I try to do any other work on a SqlConnection with the same connection string prior to the end of the TransactionScope.

    Unfortunately all the mechanics of SQL pooling is hidden so I find it hard to troubleshoot.  I suspect something asynchronous is going on with ClearPool whereby it doesn't leave things in a totally consistent state, but things are "marked appropriately" and cleaned up later, perhaps on a different thread.  Perhaps this connection is somehow "marked" and, after the next time it is used, it is disconnected immediately (without waiting for the end of the TransactionScope).  There definitely seems to be an asynchronous/timing-related component to the bug.  My own code is single-threaded.

    I've spent long enough at this that I'm ready to give up and make the upgrade to .Net 4.0 (which doesn't have the problem).  I've noticed there is a KB article about a bug with the same message: http://support.microsoft.com/kb/971654

     

     

     ----------------------------------------

     

    System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.InvalidOperationException: The requested operation cannot be completed because the connection has been broken.

       --- End of inner exception stack trace ---

       at System.Transactions.TransactionStateAborted.EndCommit(InternalTransaction tx)

       at System.Transactions.CommittableTransaction.Commit()

       at System.Transactions.TransactionScope.InternalDispose()

       at System.Transactions.TransactionScope.Dispose()

       at UFP.Reprogress.Services.InfoMotive.BusinessLogic.Loaders.MiscellaneousKpiLoader.GetWaitingConversationTable(String p_QueueIdentifier, String p_UfpWarehouseConnectionString)

     


    David Beavon
    • Marked as answer by Alan_chen Tuesday, May 10, 2011 5:50 AM
    Monday, April 25, 2011 9:27 PM

All replies

  •  

    Hi David,

    Welcome!

    According to your description, I watched ClearPool method in the both versions by my reflector, but I found they are same code. I'm not sure why the exception will be thrown. please try to set it. would you please give us more information, thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 25, 2011 8:32 AM
  • It doesn't happen in ClearPool(SqlConnection) itself.  It happens after the very next time a TransactionScope is started and the connection string is used to connect to do work in SQL.  What I find is that there are occasions when the SqlConnection does a "hard" disconnect from SQL immediately after the first command finishes.  The SqlConnection is disconnected before the disposal of the TransactionScope.  This can be confirmed in SQL profiler and SSMS.

    ie. 

    ClearPool(SqlConnection)
    using(TransactionScope TransScope)
    {
    Command1(SqlConnectionString)
    TransScope.Complete()
    } <<< ****
    

    The end result is that, during TransactionScope disposal (the end of the using block), I get the TransactionAbortedException.  (copied below).

    I also get TransactionAbortedException's if I try to do any other work on a SqlConnection with the same connection string prior to the end of the TransactionScope.

    Unfortunately all the mechanics of SQL pooling is hidden so I find it hard to troubleshoot.  I suspect something asynchronous is going on with ClearPool whereby it doesn't leave things in a totally consistent state, but things are "marked appropriately" and cleaned up later, perhaps on a different thread.  Perhaps this connection is somehow "marked" and, after the next time it is used, it is disconnected immediately (without waiting for the end of the TransactionScope).  There definitely seems to be an asynchronous/timing-related component to the bug.  My own code is single-threaded.

    I've spent long enough at this that I'm ready to give up and make the upgrade to .Net 4.0 (which doesn't have the problem).  I've noticed there is a KB article about a bug with the same message: http://support.microsoft.com/kb/971654

     

     

     ----------------------------------------

     

    System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.InvalidOperationException: The requested operation cannot be completed because the connection has been broken.

       --- End of inner exception stack trace ---

       at System.Transactions.TransactionStateAborted.EndCommit(InternalTransaction tx)

       at System.Transactions.CommittableTransaction.Commit()

       at System.Transactions.TransactionScope.InternalDispose()

       at System.Transactions.TransactionScope.Dispose()

       at UFP.Reprogress.Services.InfoMotive.BusinessLogic.Loaders.MiscellaneousKpiLoader.GetWaitingConversationTable(String p_QueueIdentifier, String p_UfpWarehouseConnectionString)

     


    David Beavon
    • Marked as answer by Alan_chen Tuesday, May 10, 2011 5:50 AM
    Monday, April 25, 2011 9:27 PM