Entlib5 - Max Pool size reached [ExecuteSprocAccessor]

Topics: Data Access Application Block
Apr 28, 2011 at 8:13 PM
Edited Apr 28, 2011 at 8:14 PM

Hello


I've searched the forum and most of the similar issues are related to DataReaders, my case is different.

 

This function seems to time out with 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. 

 

 

 

public static UserLite GetUserByID(Guid userid)
{
    Database db = DatabaseFactory.CreateDatabase("ApplicationServices");
    var result = db.ExecuteSprocAccessor<UserLite>("usp_Users_GetUserByID", userid);

    IEnumerator<UserLite> users = result.GetEnumerator();
    users.MoveNext();
    UserLite usr = users.Current;

   return usr;
}

 

There are other instances in the application that use a DataReader but I am closing the reader (see below).  So I wonder if there is anything else that I missed.

 

 

 public static Customer FindCustomerByID(decimal id)
{
            Database db = DatabaseFactory.CreateDatabase("ApplicationServices");
            DbCommand cmd = db.GetStoredProcCommand("usp_Customer_SearchByID", id);
            Customer customer= new Customer();

            using (IDataReader dbReader = db.ExecuteReader(cmd))
            {

                while (dbReader.Read())
                {

                    customer.CustomerID = (decimal)dbReader["param"];
 
                }

                dbReader.Close();
            }

            return customer;
}

 

 

        
Apr 29, 2011 at 6:34 AM

The code should work fine. I don't think of any possibility right now, but as stated with this thread, have you checked the performance of your stored procedure?

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Apr 29, 2011 at 3:07 PM

They query takes a few milliseconds to execute, so it's not a sql timeout.

Some more information that might be helpful:

 

The application was running on the dev server (Win 2008 R2) for about 6 months without any problems.  We have recently upgraded the hardware to dual-core CPU and 2GB of RAM.  That's when we started noticing some problems.  I don't know how a hardware upgrade might impact connection pooling though.  Also when I run the application locally (Win7) connecting to the same database and run the same test I fail to reproduce the error.

Apr 29, 2011 at 7:04 PM

A further update.....this is getting weirder.

I ran activity monitor for SQL server and it seems that the connections from .net are not being cleaned up.  About 100 connections are opened and they never get closed until I recycle the IIS process.

So we downgraded the server back to 1 CPU, ran the same test and about 36 connections are maintained.

May 1, 2011 at 1:25 AM

Your code is, in fact, incorrect, and you are leaking data readers. This is why your connection count is growing.

A data accessor is an implementation of IEnumerator<T> wrapped around a data reader. The data reader is kept open until the enumerator reaches the end. In your code, you're reading one element and stopping. So the enumerator never reaches the end, and it leaks the open connection. It'll eventually get cleaned up by the garbage collector, but that can take a while.

If you want to continue using the "raw" enumerator, you can fix the code by doing this:

public static UserLite GetUserByID(Guid userid)
{
    Database db = DatabaseFactory.CreateDatabase("ApplicationServices");
    var result = db.ExecuteSprocAccessor<UserLite>("usp_Users_GetUserByID", userid);

    IEnumerator<UserLite> users = result.GetEnumerator();
    users.MoveNext();
    UserLite usr = users.Current;

   // advance enumerator to the end so data reader gets closed
   while (users.MoveNext()) { }

   return usr;
}

There's a much easier way to do this, however, using Linq. If you convert the result into a list or array, that forces the enumerator to read to the end. This is really simple:

public static UserLite GetUserByID(Guid userid)
{
    Database db = DatabaseFactory.CreateDatabase("ApplicationServices");
    var result = db.ExecuteSprocAccessor<UserLite>("usp_Users_GetUserByID", userid);

    return result.ToList().First();
}