Udostępnij za pośrednictwem

Connection Pooling and the "Timeout expired" exception FAQ

  • Artykuł

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.

Well, it has happened again, Yet Another Thread On Leaked Connections (YATOLC). This is one of the most common and painful pooling problems out there, so I guess it is time to post a complete FAQ type answer. The reason this is a painful issue is that it rarely shows up during development, it is only once you deploy that your finely tuned app is brought down to its knees by this strange client side exception.

What is really happening?

Well, there is only two ways this exception can happen lets take a look:

1) You use more than Max Pool Size connections (Max Pool Size default=100)

This is fairly rare in most applications, 100 concurrent connections is a very large number when you are using pooling. In my experience the only time this has been the cause of the exception above is when you open all 100 connections in a single thread as shown below:

      SqlConnection[] connectionArray = new SqlConnection[101];

      for (int i = 0; i <= 100; i++)

      {

                  connectionArray[i] = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5");

                  connectionArray[i].Open();

      }

Solution: Once you have determined that you are using more than 100 concurrent connections (with the same connection string) you can increase Max Pool Size.

 

2) You are leaking connections

My definition of a leaked connection is a connection that you open but you do not Close _OR_ Dispose explicitly in your code. This covers not only the times when you forget to make the connection.Close() or Dispose() call in your code, but the much harder to catch scenarios where you _do_ call connection.Close but it does not get called! See below:

using System;

using System.Data;

using System.Data.SqlClient;

public class Repro

{

      public static int Main(string[] args)

      {

                  Repro repro = new Repro();

                  for (int i = 0; i <= 5000; i++)

                  {

                              try{ Console.Write(i+" "); repro.LeakConnections(); }

                              catch (SqlException){}

                  }

                  return 1;

      }

      public void LeakConnections()

      {

                  SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5");

                  sqlconnection1.Open();

                  SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                  sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";

                  sqlcommand1.ExecuteNonQuery(); //this throws a SqlException every time it is called.

                  sqlconnection1.Close(); //We are calling connection close, and we are still leaking connections (see above comment for explanation)

      }

}

Paste this code into visual studio and place a breakpoint in the sqlconnection1.Close(); line, it will never get called since ExecuteNonQurery throws an exception. After a short while you should see the dreaded Timeout exception, in my computer it happens at around 170 connections. This is definitely a contrived example, I am stacking the deck by lowering the connection timeout and throwing an exception every call, but when you consider moderate to heavy load on an ASP.NET application any leak is going to get you in trouble.

[EDIT: Duncan Godwin has correctly pointed out that there is a known bug with VS where this exception is thrown] 

3) You are rapidly opening or closing connections with sql debugging enabled in Visual Studio.

There is a known bug with Visual Studio 2003 and Sql Debugging, take a look at https://support.microsoft.com/default.aspx?scid=kb;en-us;830118

How to tell whether you are leaking connections in ADO.NET 2.0

It was very hard to figure out if you were leaking connections in v1.0 and v1.1. We have added new performance counters (see my blog below for more information) that not only kind of work (a little tongue in cheek here) but address hard to find areas like this. With ADO.NET 2.0 if you see the NumberOfReclaimedConnections performance counter go up you know that your application is leaking connections.

Beware of fixes involving the connection string! (IMPORTANT!)

Modifying the connection string can give you temporary relief from hitting this exception, so it can be very tempting. this comes at a high performance cost, you really need to fix your leak.

Here is a list of bad things to do to make it “kind of work” (also known as “shoot yourself in the foot”):

(Do not do) Pooling = False.

Fairly straightforward, if you turn pooling off you will never hit the timeout exception Of course you get no pooling with the performance drop that that involves. You are still leaking connections.

(Do not do) Connection Lifetime = 1;

This does not eliminate the exception altogether but it will probably come close. What you are telling us to do is to throw away from the pool any connection that has been used for more than one second (the lifetime check is done on connection.Close()). I see very little difference between this and turning pooling off, it is just plain bad. While I am talking about this connection string keyword here is a general warning. Do not use Connection Lifetime unless you are using a database cluster.

      (Do not do) Connection Timeout= 40000;

Terrible choice, you are telling us to wait forever for a connection to become available before throwing the timeout exception. Fortunately ASP.NET will do a thread abort after three minutes.

(Do not do) Max Pool Size=40000;

If you raise Max Pool Size high enough you will eventually stop getting this exception, the downside is that you will be using a much larger number of connections than what your application really needs. This does not scale well.

Solution :

You need to guarantee that the connection close _OR_ dispose gets called. The easiest way is with the “using” construct, modify your LeakConnections() method as follows:

      public void DoesNotLeakConnections()

      {

                  Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5")) {

                              sqlconnection1.Open();

                              SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                              sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";

                              sqlcommand1.ExecuteNonQuery(); //this throws a SqlException every time it is called.

                              sqlconnection1.Close(); //Still never gets called.

                  } // Here sqlconnection1.Dispose is _guaranteed_

      }

SqlClient Pooling Q and A:

Q:Why does this work?

A:The Using construct is equivalent to a Try/…/Finally{ <using object>.Dispose() ). Even when ExecuteNonQuery tries to throw out of the execution scope we guarantee that the code in the Finally block will get called.

Q:In the code above, wouldn’t we be calling Close and Dispose if no exception is thrown?

A:We can call Close or Dispose (or both) multiple times without any problems. Calling Close or Dispose on a Closed or Disposed connection is a no-op

Q:What is the difference between Close and Dispose and which one should I call?

A: You can call either one or both, they do practically the same thing.

Q:What do you mean by “practically the same thing”

A: Dispose will clean the connection string information from the SqlConnection and then call Close. There are no other differences, you can verify this by using reflector.

Q: Does connection.Dispose() remove the connection from the pool versus Close()?

A: No, see above.

Q: Do I also need to explicitly close an open data reader on the connection, which would require nested using statements.

A: I would recommend explicitly disposing any ado.net object that implements IDisposable. In many cases this is overkill but it is guaranteed to work (or to be a high priority bug that we need to fix yesterday) and it protects you against future changes in the framework.

Rambling out. Standard disclaimer: this post is provided AS IS and confers no rights.

Comments

  • Anonymous
    August 25, 2004
    Another cause is rapidly opening or closing connections with sql debugging enabled in Visual Studio. See http://support.microsoft.com/default.aspx?scid=kb;en-us;830118.

    Sql debugging disables connection pooling (which makes sense) so the same error is returned.
  • Anonymous
    August 25, 2004
    Q: Do I also need to explicitely close an open data reader on the connection, which would require nested using statements or try/catch block (which is what I'm using right now)? Or can I just "close" the connection and not worry about whether it has an open data reader on it or not?
  • Anonymous
    August 25, 2004
    Does connection.Dispose() remove the connection from the pool versus Close()?
  • Anonymous
    August 25, 2004
    We often find the following error in our event log. Seems to occur randomly atleast 4 or 5 times a day. Any ideas?
    The stored procedure call is not expensive. It just reads a record from the table.


    1) Exception Information

    Exception Type: System.Data.SqlClient.SqlException
    Errors: System.Data.SqlClient.SqlErrorCollection
    Class: 10
    LineNumber: 0
    Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
    Number: -2
    Procedure: ConnectionRead (recv()).
    Server:
    State: 0
    Source: .Net SqlClient Data Provider
    TargetSite: System.Data.SqlClient.SqlDataReader ExecuteReader(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior, Boolean)
    HelpLink: NULL

    StackTrace Information

    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
    at System.Data.SqlClient.SqlCommand.ExecuteReader()
    at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
    at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
    at Deloitte.Platform.Data.SqlHelper.ExecuteReader(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
    at Deloitte.Platform.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
    at ContentManagementHelper.Data.UserEmailDAO.GetUserInfo(String userId)
    at
  • Anonymous
    August 25, 2004
    The comment has been removed
  • Anonymous
    August 26, 2004
    The comment has been removed
  • Anonymous
    August 26, 2004
    Balaji,
    In whidbey you can use the new performance counter to see if connections are leaking and I believe that you can figure out which connection is leaking from the new Tracing support added. I am trying to find out more information about how Tracing is exposed in beta 1 but so far I have nothing.


    It is very possible that a tool that checks for leaked connections could be made, I am going to have to look into it in more detail.
  • Anonymous
    August 26, 2004
    Really great post - it needs to be on MSDN ;-)
  • Anonymous
    September 15, 2004
    The comment has been removed
  • Anonymous
    September 23, 2004
    I'm a bit confused about this:

    ----
    Q:What do you mean by “practically the same thing”

    A: Dispose will clean the connection string information from the SqlConnection and then call Close. There are no other differences, you can verify this by using reflector.
    ----

    When you say "clean the connection string information", do you mean it does "<SqlConnection>.ConnectionString = null", or do you mean it clears internal state?

    Also, under what circumstances would I want to clear the connection string information or to not clear it?
  • Anonymous
    September 23, 2004
    John, yes exactly. Dispose will null out the ConnectionString and then call close. That is all. I will update the blog to make this clear.
  • Anonymous
    September 23, 2004
    Forgot to address your second question. The only difference would be that code like this:

    con.open
    con.close
    con.open

    would work, where this
    con.open
    con.dispose.
    con.open

    would not since the connection string has been cleared. There is nothing stopping you from doin the following though:

    con.open
    con.dispose
    con.ConnectionString=<valid connection string>
    con.open

  • Anonymous
    May 04, 2006
    I'm trying to find leaking connections in some code that I inherited. The code is using Microsoft Data Access Application Block for .NET Version 2.0's SQLHelper.cs, and contains this code, which looks like it requires the caller to close the connection. Unfortunately, there isn't a way of getting the connection from the DataReader (is there?). Maybe I'm wrong. Maybe when the DataReader is disposed it closes the connection (but I doubt it). Anyone got any ideas?


    public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
    if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
    SqlConnection connection = null;
    try
    {
    connection = new SqlConnection(connectionString);
    connection.Open();

    // Call the private overload that takes an internally owned connection in place of the connection string
    return ExecuteReader(connection, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);
    }
    catch
    {
    // If we fail to return the SqlDatReader, we need to close the connection ourselves
    if( connection != null ) connection.Close();
    throw;
    }
               
    }

  • Anonymous
    May 04, 2006
    Apologies. I think I've worked it out now... ExecuteReader is being called with SqlConnectionOwnership.Internal, which means it uses
    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    which does mean that the connection is closed when the DataReader is closed. Sorry.
  • Anonymous
    June 19, 2006
    Your article is prety nice. It's a pity that i didn't see it more later.
  • Anonymous
    September 01, 2006
    (or, how to load test the connection pool without breaking a sweat)

    Recently, I encountered a question...
  • Anonymous
    December 19, 2006
    Erno de Weerd recently posted a blog entry on how SqlConnection.Dispose removes the connection from the
  • Anonymous
    June 28, 2007
    Error: System.Data.SqlClient.SqlException: Timeout expired
  • Anonymous
    September 17, 2007
    PingBack from http://www.mauriziopapini.eu/wordpress/?p=9
  • Anonymous
    January 17, 2008
    Aunque en desarrollo using sólo será una instrucción , cuando hagas deployment verás el poder de using
  • Anonymous
    January 18, 2008
    Aunque en desarrollo using sólo será una instrucción , cuando hagas deployment verás el poder de using
  • Anonymous
    March 01, 2008
    We get a lot of queries from developers around the following error while they are using ADO.NET &quot;Timeout
  • Anonymous
    March 01, 2008
    We get a lot of queries from developers around the following error while they are using ADO.NET &quot;Timeout
  • Anonymous
    March 01, 2008
    PingBack from http://msdnrss.thecoderblogs.com/2008/03/02/indications-that-we-are-leaking-connections/
  • Anonymous
    February 21, 2009
    PingBack from http://www.winnyspot.com/windows-server-2003-problema-con-lapp-pool-the-timeout-period-elap