SQL timeout: "All pooled connections were in use"

raphael-m commented 7 years ago

After saving some entities, an error message appears:

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.

All 2sxc modules stop working until the application is restarted. 2sxc version: 9.1.2

AbsoluteURL:/Default.aspx
DefaultDataProvider:DotNetNuke.Data.SqlDataProvider, DotNetNuke
ExceptionGUID:a349c516-477f-4e46-9d4f-06fee1c93cbb
AssemblyVersion:
PortalId:-1
UserId:-1
TabId:-1
RawUrl:
Referrer:
UserAgent:
ExceptionHash:Unl4tGsJctX1FNutJWLf7A==
Message: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.
StackTrace:
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_Include>d__30`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_Include>d__30`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_Include>d__30`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ToSic.Eav.Persistence.Efc.Efc11Loader.LoadContentTypesIntoLocalCache(Int32 appId)
   at ToSic.Eav.Persistence.Efc.Efc11Loader.ContentTypes(Int32 appId)
   at ToSic.Eav.Persistence.Efc.Efc11Loader.AppPackage(Int32 appId, Int32[] entityIds, IDeferredEntitiesList source, Boolean entitiesOnly)
   at ToSic.Eav.DataSources.SqlSources.EavSqlStore.GetDataForCache(IDeferredEntitiesList targetCacheForDeferredLookups) in C:\Projects\eav-server\ToSic.Eav.DataSources\SqlSources\EavSqlStore.cs:line 49
   at ToSic.Eav.DataSources.Caches.BaseCache.EnsureCache() in C:\Projects\eav-server\ToSic.Eav.DataSources\Caches\BaseCache.cs:line 108
   at ToSic.Eav.DataSources.Caches.BaseCache.GetPublishedEntities() in C:\Projects\eav-server\ToSic.Eav.DataSources\Caches\BaseCache.cs:line 36
   at ToSic.Eav.DataSources.DataStream.get_List() in C:\Projects\eav-server\ToSic.Eav.DataSources\DataStream.cs:line 94
   at ToSic.Eav.DataSources.DataStream.get_LightList() in C:\Projects\eav-server\ToSic.Eav.DataSources\DataStream.cs:line 129
   at ToSic.Eav.DataSources.DataStream.get_LightList() in C:\Projects\eav-server\ToSic.Eav.DataSources\DataStream.cs:line 139
   at ToSic.Eav.DataSources.EntityTypeFilter.GetList() in C:\Projects\eav-server\ToSic.Eav.DataSources\EntityTypeFilter.cs:line 48
   at ToSic.Eav.DataSources.DataStream.get_LightList() in C:\Projects\eav-server\ToSic.Eav.DataSources\DataStream.cs:line 139
   at ToSic.Eav.DataSources.DataStream.get_List() in C:\Projects\eav-server\ToSic.Eav.DataSources\DataStream.cs:line 82
   at ToSic.SexyContent.ContentGroupManager.GetContentGroup(Guid contentGroupGuid) in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\SexyContent\ContentGroupManager.cs:line 0
   at ToSic.SexyContent.ContentGroupManager.GetContentGroupOrGeneratePreview(Guid groupGuid, Guid previewTemplateGuid) in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\SexyContent\ContentGroupManager.cs:line 142
   at ToSic.SexyContent.ContentBlocks.EntityContentBlock._constructor(IContentBlock parent, IEntity cbDefinition) in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\SexyContent\ContentBlocks\EntityContentBlock.cs:line 79
   at ToSic.SexyContent.ContentBlocks.Renderers.Simple.Render(IContentBlock parentCb, IEntity entity) in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\SexyContent\ContentBlocks\Renderers\Simple.cs:line 24
   at ToSic.SexyContent.DynamicEntity.Render() in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\SexyContent\DynamicEntity.cs:line 141
   at ASP._Page_Portals_0_2sxc_Referenzen__Reference___Reference_detail_view_cshtml.Execute() in \\nasw1\p\Beeler 2017\Web\Portals\0\2sxc\Referenzen\_Reference - Reference detail view.cshtml:line 43
   at System.Web.WebPages.WebPageBase.ExecutePageHierarchy()
   at System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage)
   at ToSic.SexyContent.Engines.RazorEngine.Render(TextWriter writer) in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\2Sexy Content Razor\RazorEngine.cs:line 65
   at ToSic.SexyContent.Engines.RazorEngine.RenderTemplate() in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\2Sexy Content Razor\RazorEngine.cs:line 75
   at ToSic.SexyContent.Engines.EngineBase.Render() in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\SexyContent\Engines\EngineBase.cs:line 87
   at ToSic.SexyContent.SxcInstance.Render() in C:\Projects\2SexyContent\Web\DesktopModules\ToSIC_SexyContent\SexyContent\SxcInstance.cs:line 203
InnerMessage:
InnerStackTrace:
Source:System.Data
FileName:
FileLineNumber:0
FileColumnNumber:0
Method:
Server-Name: SRV-WEB-60

iJungleboy commented 7 years ago

According to research by @raphael-m this seems to be an Entity Framework Core issue. Some object seems to keep an open connection to the DB, till a ToList happens.

raphael-m commented 7 years ago

The temporary fix should prevent the issue. Some background information:

ServiceProvider has a private List<IDisposable> _transientDisposables;
2sxc keeps the ServiceProvider as a static variable, which causes that all ever resolved (disposable) dependencies are kept in memory and are not being disposed.
If I understand it correctly, in a standard ASP.NET Core application, ServiceProvider should be automatically disposed after a request finishes (which in turn disposes all _transientDisposables) [source]

iJungleboy commented 7 years ago

@raphael-m I think you missunderstood the asp.net core scenario. AFAIK the ServiceProvider is not disposed, but the DB-object is "scoped" not "transient", so it never goes to _transientDisposables but is kept till the request completes, then removed.

In our case, using scoped actually makes the DI very slow for reasons unknown. The page gets dramatically slower in that case. This is why I switched from scoped to transient. My guess is that in asp.net full, the DI doesn't know about the scopes. Apparently there are manual ways to start/stop a scope, but I'm not sure where the ideal hook for this would be, so I would be very careful about that...

Edit Pls also check http://www.inversionofcontrol.co.uk/asp-net-core-1-0-dependency-injection-what-is-is-and-what-it-is-not-2/

Edit 2 Note that I'm also experimenting with this in the factory.cs - we'll have to test it with your test-code:

                return _sp.CreateScope().ServiceProvider;

2sic / 2sxc

SQL timeout: "All pooled connections were in use" #1200