并行连接达到最大池大小错误 [英] Max pool size was reached error for parallel connections

问题描述

我有一个sql数据库，正在使用Microsoft.Practices.EnterpriseLibrary进行数据库通信.我有一个数据库提供程序类，如下所示:

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
}

大多数情况下，我使用它的无参数构造函数来获取配置文件中具有默认连接字符串的数据库实例. 这是我如何在代码中执行SP的示例:

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
}

当大约有1万用户尝试访问数据库时，出现此错误: 从池中获取连接之前已经过超时时间.发生这种情况的原因可能是所有池化连接都在使用中，并且达到了最大池大小.

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.

在我的配置文件中，我提到最大池大小为100(尽管未提及，这是默认值).我该如何解决?

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?

修改: 当我运行select * from myDB..sysprocesses时，它显示通过AWAITING命令处于睡眠状态的100个进程.但是在我的代码中，我现在正在显式地执行connection.Close().

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.

我认为问题在于AWS RDS没有重新使用来自池的连接.当我在连接到本地数据库时运行相同的代码，效果很好.

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.

推荐答案

这听起来有些怪异，因为我没有任何提及它的参考文献，但我能够通过将DataReader替换为DataTable来进行阅读我的SP返回的数据.

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.

这篇关于并行连接达到最大池大小错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！