asp.net – 超时已过期.从池中获取连接之前经过的超时时间.企业

以下是该应用程序的简要概述.该应用程序驻留在3台服务器上; Web服务器,应用服务器和运行SQL Server 2008的数据库服务器.所有服务器都在Windows Server 2008上运行.Web服务器驻留在公共域中.应用服务器驻留在DMZ中,通过端口80和443通过WCF在Web服务器之间进行开放式通信.数据库服务器驻留在私有域中,通过端口1433与应用服务器进行开放式通信.该应用程序已经生产了一段时间,并且只在上周经常遇到这些问题.没有代码更改,托管服务提供商表示最近没有对服务器进行任何更改.

在Web服务器上,应用程序遇到下面发布的错误(错误1).快速修复解决方案是重新启动应用服务器IIS进程,但是,这是一个反复出现的问题,并导致业务所有者出现重大中断.

应用程序的DAL使用Enterprise Library v4.1打开与数据库的连接.我已经包含了2个代码片段,负责启动呼叫(代码1和代码2).以各种其他方法重复该方法. ExecuteReader方法可能没有正确关闭连接吗？没有覆盖允许我指定ConnectionBehavior来关闭连接的方法.

我们执行了SQL配置文件分析,并确定没有与DB的延迟打开连接.

在研究问题的同时,有人建议企业库可能存在连接未被妥善处理的错误,但是在帖子上没有验证.如何确定问题的原因？或者解决问题的适当行动方案是什么？我可以增加池大小,但这似乎只是一个临时解决方案.

错误1：

***System.ServiceModel.FaultException: 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. at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection() at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(String storedProcedureName,Object[] parameterValues) at CityStoreDAL.NavigationProvider.GetNavigator(Int32 navigatorID) in c:TFSCityStoreDEVSRCCityStoreDALNavigationProvider.cs:line 41 at CityStoreBLL.Navigation.GetNavigator(Int32 navigatorID) in c:TFSCityStoreDEVSRCCityStoreBLLNavigation.cs:line 15 at CityStoreService.CityStoreService.GetNavigator(Int32 navigato rID) in c:TFSCityStoreDEVSRCCityStoreServiceNavigation.cs:line 21 at SyncInvokeGetNavigator(Object,Object[],Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance,Object[] inputs,Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)***
Generated: Wed,12 Nov 2014 19:40:22 GMT

System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.ServiceModel.FaultException: 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.   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection()
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection)
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command)
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(String storedProcedureName,Object[] parameterValues)
   at CityStoreDAL.NavigationProvider.GetNavigator(Int32 navigatorID) in c:TFSCityStoreDEVSRCCityStoreDALNavigationProvider.cs:line 41
   at CityStoreBLL.Navigation.GetNavigator(Int32 navigatorID) in c:TFSCityStoreDEVSRCCityStoreBLLNavigation.cs:line 15
   at CityStoreService.CityStoreService.GetNavigator(Int32 navigatorID) in c:TFSCityStoreDEVSRCCityStoreServiceNavigation.cs:line 21
   at SyncInvokeGetNavigator(Object,Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance,Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Server stack trace: 
   at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation,ProxyRpc& rpc)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action,Boolean oneway,ProxyOperationRuntime operation,Object[] ins,Object[] outs,TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall,ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg,IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData,Int32 type)
   at CityStoreWeb.CityStoreServiceReference.INavigation.GetNavigator(Int32 navigatorID)
   at CityStoreWeb.Product.<>c__DisplayClass1.<BindCategoryMenuAndInfo>b__0(INavigation proxy) in c:TFSCityStoreDEVSRCCityStoreWebProduct.aspx.cs:line 73
   at CityStoreWeb.Common.Service'1.Use(UseServiceDelegate'1 codeBlock) in c:TFSCityStoreDEVSRCCityStoreWebCommonUtils.cs:line 243
   at CityStoreWeb.Product.BindCategoryMenuAndInfo(Int32 navigatorID) in c:TFSCityStoreDEVSRCCityStoreWebProduct.aspx.cs:line 71
   at CityStoreWeb.Product.SetupPage(Int32 navigatorID,Int32 categoryID,Int32 productID) in c:TFSCityStoreDEVSRCCityStoreWebProduct.aspx.cs:line 64
   at CityStoreWeb.Product.Page_Load(Object sender,EventArgs e) in c:TFSCityStoreDEVSRCCityStoreWebProduct.aspx.cs:line 37
   at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp,Object o,Object t,EventArgs e)
   at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender,EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at CityStoreWeb.Common.BasePage.OnLoad(EventArgs e) in c:TFSCityStoreDEVSRCCityStoreWebCommonBasePage.cs:line 26
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,Boolean includeStagesAfterAsyncPoint)
   --- End of inner exception stack trace ---
   at System.Web.UI.Page.HandleError(Exception e)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint,Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequest(HttpContext context)
   at ASP.product_aspx.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step,Boolean& completedSynchronously)

代码1：

public static NavigatorEntity GetNavigator(int navigatorID)
{
        Database db = DatabaseFactory.CreateDatabase();
        object[] spParams;
        spParams = new object[1];
        spParams[0] = navigatorID;
        using (IDataReader reader = db.ExecuteReader("GetNavigator",spParams))
        {
            if (reader == null)k
            {
                throw new ArgumentNullException("reader");
            }
            NavigatorEntity myNav = null;
            if (reader.Read())
            {
                myNav = GetNavigatorEntityFromReader(reader);
            }

            if (myNav != null)
            {
                reader.NextResult();
                myNav.Categories = GetCategoriesCollectionFromReader(reader);

                reader.NextResult();
                myNav.RecommendedProducts = 
                    ProductProvider.GetRecommendedProductCollectionFromReader(reader,false);
            }

            return myNav;
        }
    }

代码2：

public static List<NavigatorEntity> GetNavigatorsAll()
{
    Database db = DatabaseFactory.CreateDatabase();

    using (IDataReader myReader = db.ExecuteReader("GetNavigatorsAll"))
    {
        return GetNavigatorCollectionFromReader(myReader);
    }
}

private static List<NavigatorEntity> GetNavigatorCollectionFromReader(IDataReader reader)
{
    List<NavigatorEntity> navigators = new List<NavigatorEntity>();
    while (reader.Read())
    {
        navigators.Add(GetNavigatorEntityFromReader(reader));
    }
    return navigators;
}

更新：

显然,问题不在于ASP.NET错误中引用的任何方法.他们抛出了异常,因为到达它们时,问题(耗尽的连接池)已经发生,并且会在任何打开新连接的进程方法上抛出异常.

在分析了数据访问层中的所有代码之后,我确定了一些未正确使用using语句的rouge方法,从而使池中的开放连接保持累积.在隔离方法并使用性能监视器观察NumberOfPooledConnections之后,我确认这些方法应该受到指责.

修复是将rouge方法包装在适当的using语句中.

导致问题的方法之一：

public static List<TaxCodeEntity> CMSGetTaxCodes()
{
    Database db = DatabaseFactory.CreateDatabase();
    return GetTaxCodeCollectionFromReader(db.ExecuteReader("CMS_GetTaxCodes"));            
}

固定：

public static List<TaxCodeEntity> CMSGetTaxCodes()
{
    Database db = DatabaseFactory.CreateDatabase();
    using (DbCommand dbCmd = db.GetStoredProcCommand("CMS_GetTaxCodes"))
    {
        using (IDataReader myReader = db.ExecuteReader(dbCmd))
        {
            return GetTaxCodeCollectionFromReader(myReader);
        }    
    }
}