Max pool size was reached error for parallel conne

问题:

I have a sql database and I am using Microsoft.Practices.EnterpriseLibrary for db communication. I have a database provider class as below:

public class DBProvider
{
    #region Properties
    #region Database

/// <summary>
/// The db instance.
/// </summary>
private Database database;
     #region Ctors

/// <summary>
/// Initializes a new instance of the <see cref="DBProvider"/> class.
/// </summary>
public DBProvider()
{
    DatabaseProviderFactory dbProvider = new DatabaseProviderFactory();
    database = dbProvider.CreateDefault();
}

/// <summary>
/// Initializes a new instance of the <see cref="DBProvider"/> class.
/// </summary>
/// <param name="dbName">
/// The name of the db.
/// </param>
public DBProvider(string dbName)
{
    DatabaseProviderFactory dbProvider = new DatabaseProviderFactory();
    database = dbProvider.Create(dbName);
}
     #endregion
/// <summary>
/// The db instance.
/// </summary>
public Database Database
{
    get { return database; }
    set { database = value; }
}

    #endregion

    #endregion
}

Mostly I am using it's parameterless constructor to get the instance of the db with default connection string in the config file. Here is the sample of how I am executing the SPs in my code:

DBProvider dbProvider = new DBProvider();
using (DbConnection connection = 
dbProvider.Database.DbProviderFactory.CreateConnection())
    {
        if (connection != null)
        {
            connection.ConnectionString = dbProvider.Database.ConnectionString;
            await connection.OpenAsync();
        }

    using (DbCommand cmd = dbProvider.Database.GetStoredProcCommand(SPName))
    {
        cmd.CommandTimeout = 60;
        cmd.Connection = connection;
        dbProvider.Database.AddInParameter(cmd, ColumnUserId, DbType.StringFixedLength, "someuserID");

        await cmd.ExecuteNonQueryAsync();
    }
}

and in case if I am reading the data I am doing something like:

using (var reader = await cmd.ExecuteReaderAsync())
{
    //read data
}

I am getting this error when there are around 10k users trying to access the db: 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.

In my config file, I have mentioned the max pool size as 100 (though it is the default value if not mentioned). How can I solve this?

Edit: When I run select * from myDB..sysprocesses it shows that it has 100 processes in Sleeping state with AWAITING command. But in my code I am now doing connection.Close() explicitly.

I think the problem is with AWS RDS not reusing the connection from the pool. as I run this same code while connecting to my local db and it works fine.

回答1:

It may sound a bit weird since I do not have any references mentioning it but I was able to resolve by replacing DataReader by DataTable to read through the data returned by my SP.