none
Timeout Expired error using TransactionScope and Explicit Unbind RRS feed

  • Question

  • We are using System.Transactions.TransactionScope along with Alazel Acheson's ConnectionScope class (http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx) to make sure that connections and transactions to a single database are not promoted to distributed transactions.  We have also added "Transaction Binding=Explicit Unbind" to our connection string per the recommendation at the bottom of this post: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx.

    When an exception gets thrown from inside of a nested TransactionScope Using block, the SqlConnection's inner connection pool does not get cleared, and we get the following error: "System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.."  When debugging through the code, we can see the following values in the watch window before we perform our third loop and get the Timeout Expired error when attempting to open a connection:

    - ((SqlConnection)connection).InnerConnection.Pool.Count will equal 2 (max pool size is set at 2 for easier debugging)
    - ((SqlConnection)connection).InnerConnection.Pool._objectList[0]._enlistedTransaction.TransactionInformation.LocalIdentifier will equal "e4b07623-70cb-4b0d-a93e-8589d834af96:5"
    - ((SqlConnection)connection).InnerConnection.Pool._objectList[1]._enlistedTransaction.TransactionInformation.LocalIdentifier will equal "e4b07623-70cb-4b0d-a93e-8589d834af96:9"
    - Transaction.Current.TransactionInformation.Status is set to Aborted

    If we add ImplicitUnbind to our connection string (or simply remove the Transaction Binding flag), the inner connection pool is cleared appropriately when the transaction is aborted and we do not receive the Timeout Expired error.   However, ImplicitUnbind is not recommended.  Is there any other way to make sure the inner connection pool gets cleared when an exception is thrown and the connection is closed/disposed?

    Test Code:
    Our test case (code below) throws an exception from inside a nested TransactionScope.  When the method is called repeatedly, we get the "Timeout Expired" error.  This test case is a summary of what we were seeing on our web application.

    Here is our Connection String (Max Pool Size set low for easier debugging):

    <ConnectionString Id="Default">Data Source=.;Initial Catalog=FrameworkUnitTest;Integrated Security=SSPI;MultipleActiveResultSets=True;Transaction Binding=Explicit Unbind;Max Pool Size=2</ConnectionString>

    Here are the test methods:

            /// <summary>
            /// Creates test table before test executes.
            /// </summary>
            [TestInitialize]
            public void MyTestInitialize()
            {
                using (new ConnectionScope())
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("IF NOT EXISTS");
                    sb.AppendLine("( ");
                    sb.AppendLine("    SELECT 1 FROM INFORMATION_SCHEMA.TABLES ");
                    sb.AppendLine("    WHERE Table_Schema = 'dbo' ");
                    sb.AppendLine("    AND Table_Name = 'SeanTemp' ");
                    sb.AppendLine(") ");
                    sb.AppendLine("CREATE TABLE SeanTemp");
                    sb.AppendLine("(");
                    sb.AppendLine("    a INT");
                    sb.AppendLine(")");
                    DatabaseHelper.ExecuteNonQuery(sb.ToString());

                    DeleteAllSeanTemp(DatabaseHelper.DefaultConnectionName);
                }
            }

            /// <summary>
            /// Test looping through rollback transaction test functionality to create connection pool error.
            /// </summary>
            [TestMethod]
            public void ConnectionPoolTimeoutWithExplicitUnbindTest()
            {
                int x = 0;

                try
                {
                    for (x = 0; x < 1000; x++)
                    {
                        MainTransactionRollbackProcessing(defaultConnectionName);
                        DeleteAllSeanTemp(defaultConnectionName);
                    }
                }
                finally
                {
                    Console.WriteLine("Exiting at loop {0}", x);
                }
            }
            
                    /// <summary>
            /// Runs the main test functionality for testing transaction rollbacks.
            /// </summary>
            /// <param name="connectionName">Name of the connection string to use for accessing database.</param>
            private static void MainTransactionRollbackProcessing(string connectionName)
            {
                //Make sure we don't have any data in the table before running the test.
                Assert.AreEqual(0, GetSeanTempCount(connectionName), "dbo.SeanTemp should not contain any data before starting the test.");
               
                TransactionOptions options = new TransactionOptions();
                options.IsolationLevel = IsolationLevel.ReadCommitted;
                options.Timeout = new TimeSpan(0, 0, 30, 0);

                try
                {
                    using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required, options))
                    using (new ConnectionScope())
                    {
                        InsertSeanTemp(connectionName, 0);
                        transaction.Complete();
                    }

                    using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required, options))
                    using (new ConnectionScope())
                    {
                        InsertSeanTemp(connectionName, 1);

                        using (TransactionScope transaction2 = new TransactionScope(TransactionScopeOption.Required, options))
                        {
                            InsertSeanTemp(connectionName, 2);
                            transaction2.Complete();
                        }
                        using (TransactionScope transaction3 = new TransactionScope(TransactionScopeOption.Required, options))
                        {
                            InsertSeanTemp(connectionName, 2);
                            ThrowInvalidTimeZoneException();
                            transaction3.Complete();
                        }

                        InsertSeanTemp(connectionName, 3);
                        ThrowInvalidTimeZoneException();
                        transaction.Complete();
                   }
                }
                catch (InvalidTimeZoneException)
                {
                    //If we get here, our test threw the exception, which is what we wanted to happen to test the rollback.
                    int actualRowCount = GetSeanTempCount(connectionName);
                    Assert.AreEqual(1, actualRowCount, "Transactions did not get rolled back.");
                }
                catch (Exception ex)
                {
                    Assert.Fail("An unexpected exception was thrown: {0}", BaseHelper.RenderException(ex));
                }
            }
           
            /// <summary>
            ///
            /// </summary>
            private static void ThrowInvalidTimeZoneException()
            {
                throw new InvalidTimeZoneException();
            }

            /// <summary>
            /// Insert a value into dbo.SeanTemp.a column.
            /// </summary>
            /// <param name="connectionName">Name of the connection string to use when inserting values.</param>
            /// <param name="value">Integer value to insert.</param>
            private static void InsertSeanTemp(string connectionName, int value)
            {
                string sql = string.Format("INSERT SeanTemp(a) VALUES ({0})", value);
                DatabaseHelper.ExecuteNonQuery(connectionName, sql);
            }

            /// <summary>
            /// Deletes all rows from dbo.SeanTemp table.
            /// </summary>
            /// <param name="connectionName">Name of the connection string to use for deleteing data.</param>
            private static void DeleteAllSeanTemp(string connectionName)
            {
                const string sqlDelete = "DELETE FROM SeanTemp";
                DatabaseHelper.ExecuteNonQuery(connectionName, sqlDelete);
            }

            /// <summary>
            /// Returns the count of all rows in dbo.SeanTemp table.
            /// </summary>
            /// <param name="connectionName">Name of the connection string to use for selecting the row count.</param>
            /// <returns>Number of rows in dbo.SeanTemp table.</returns>
            private static int GetSeanTempCount(string connectionName)
            {
                const string sqlSelect = "SELECT ISNULL(COUNT(*), 0) FROM dbo.SeanTemp";
                return DatabaseHelper.ExecuteScalar<int>(connectionName, sqlSelect);
            }
     
    Friday, January 30, 2009 7:21 PM

All replies

  • Hi Jean,
      It looks like your question might be better answered in the ADO.NET Data Providers forum.


      This forum deals with issues regarding - data platform development using classic ADO.NET and System.Data namespace.


    Thanks!
    Lin. 
      Monday, February 16, 2009 10:48 PM
    • This issue was fixed in .Net 4.0.

      I found the same question asked at http://stackoverflow.com/questions/314459/connections-will-not-close-when-using-transaction-bindingexplicit-unbind-in-con where the following workaround was suggested:

      using (SqlConnection con = new SqlConnection(connectionString))
      {
        con.Open();
        try
        {
          Console.WriteLine("Server is {0}", con.ServerVersion);
          Console.WriteLine("Clr is {0}", Environment.Version);
          for (int i = 0; i < 5; i++)
          {
            using (SqlCommand cmd = con.CreateCommand())
            {
              cmd.CommandText = "insert into TXTEST values ( " + i + " )";
              cmd.ExecuteNonQuery();
            }
            Console.WriteLine("Row inserted");
          }
          Thread.Sleep(TimeSpan.FromSeconds(1));
        }
        catch
        {
          SqlConnection.ClearPool(con);
          throw;
        }
      }
      
      


      Postings are provided "As Is" with no warranties and confer no rights.
      Friday, August 20, 2010 1:44 AM