Ef Core v3 Connection Leak #8714

cangunaydin created 4 years ago

Hello, Here is my problem. We have updated our ANZ v7.1 to v8.1. we were using .Net Framework 4 in early version. Now we switched our project to .Net Core 3.1 and we optimized our ef queries so it won't give any runtime errors or heavy load in the server side with ef core v3.1. And it worked very well on our staging environment. Queries were pretty fast. The problem is occured when it was in production environment under load. When i say load it wasn't super heavy load. I have prepared a load test with gattling ( https://gatling.io/) and ramped up my users to 40. And when i monitor the open connections from perfmon tool. I see that there are lots of connections were open and not closed after the requests. I have also checked the sql server with this query while the web site is getting the load

select count(*) as sessions,
         s.host_name,
         s.host_process_id,
         s.program_name,
         db_name(s.database_id) as database_name
   from sys.dm_exec_sessions s
   where is_user_process = 1
   group by host_name, host_process_id, program_name, database_id
   order by count(*) desc;

sessions hit 100 and stayed like that and all the website is locked when there is a load on the website. Also i got lots of timeout expired from sql server while i am doing this procedure. You can see the error log that i was getting below.

    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.
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.BeginTransaction(DatabaseFacade databaseFacade, IsolationLevel isolationLevel)
   at Abp.EntityFrameworkCore.Uow.DbContextEfCoreTransactionStrategy.CreateDbContext[TDbContext](String connectionString, IDbContextResolver dbContextResolver)
   at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.GetOrCreateDbContext[TDbContext](Nullable`1 multiTenancySide, String name)
   at Abp.EntityFrameworkCore.Repositories.EfCoreRepositoryBase`3.get_Table()
   at Abp.EntityFrameworkCore.Repositories.EfCoreRepositoryBase`3.GetQueryable()
   at Abp.EntityFrameworkCore.Repositories.EfCoreRepositoryBase`3.GetAllIncluding(Expression`1[] propertySelectors)
   at Abp.EntityFrameworkCore.Repositories.EfCoreRepositoryBase`3.FirstOrDefaultAsync(TPrimaryKey id)
   at Abp.Threading.InternalAsyncHelper.AwaitTaskWithPostActionAndFinallyAndGetResult[T](Task`1 actualReturnValue, Func`1 postAction, Action`1 finalAction)

Since i was using the framework for a long time, i know framework is running with unit of works in every request. So shouldn't the framework close the connection automatically after the request is finished. Why the connections are still open on the background and reaching max pool size. Or Maybe sth i have done on the project changed the behavior, have any ideas on this issue? Or sth that can lead me to the right path? I do not use datareader or i do not open the connection manually it was the same pattern that we are using all the time. And one more thing is we use hangfire for the background jobs. So when i query the sql for open connections it was giving two different providers. One was Core Microsoft SqlClient Data Provider and the other one was Core .Net SqlClient Data Provider. Is this the way it should be? Thank you for the assistance

5 Answer(s)

0

maliming created 4 years ago
Support Team

hi

Can you try to disable hangfire and try again? ...

Of course, this may also be related to the database, since everything is normal in the local environment. You can try to rule out some environmental effects.

cangunaydin created 4 years ago

Hello i have disabled the hangfire and it didn't change anything. Maybe i mislead you on my first post, it doesn't also work at staging environment. I didn't realize this behavior in staging cause i didn't do any load test before i go to production.

When i was searching i come up with this issue from ef core. https://github.com/dotnet/SqlClient/issues/18

And i was wondering can AsyncHelper.RunSync method do that? cause in

public ClaimsPrincipal ValidateToken(string securityToken, TokenValidationParameters validationParameters, out SecurityToken validatedToken)

method it calls

 AsyncHelper.RunSync(() => ValidateSecurityStampAsync(settingManager, principal));

and it is calling

 private async Task<bool> ValidateSecurityStampFromDb(UserIdentifier userIdentifier, string securityStamp)
        {
            using (var uow = _unitOfWorkManager.Begin())
            {
                using (_unitOfWorkManager.Current.SetTenantId(userIdentifier.TenantId))
                {
                    var user = _userManager.GetUser(userIdentifier);
                    uow.Complete();

                    //cache last requested value
                    await SetSecurityStampCacheItem(userIdentifier.TenantId, userIdentifier.UserId, user.SecurityStamp);

                    return await _signInManager.ValidateSecurityStampAsync(user, securityStamp);
                }
            }
        }

and when i look at the queries from sql most of the queries hanging are AbpUsers and AbpUserToken queries. I will also try the load test on new template.

0

ismcagdas created 4 years ago
Support Team

Hi @cangunaydin

What is your ABP Framework version ? Could you upgrade it to latest version and try again this scenario ?
0

cangunaydin created 4 years ago

Hi @ismcagdas My Abp Framework Version is 5.1.0, Good news is i have found the problem. i have updated all the packages one by one. First i have updated Abp packages and the problem persists. I had also Microsoft.EntityFrameworkCore packages version 3.1.0 i have updated them to 3.1.3 and it didn't help either. Then i have found out that in web.core project there is a system.data.sqlclient package probably it was used in early versions which we have forgot to remove. When i removed that the problem is gone. So probably Microsoft.Data.SqlClient was interfering with System.Data.SqlClient which was causing the problem. Thank you for the help. And sorry to take your time.Have a great day.
0

ismcagdas created 4 years ago
Support Team

Hi @cangunaydin

Thanks a lot for your response, it is really appriciated. A few other customers are having similar problems and this could help them.

Thanks again :)

Have an answer to this question? Log in and write your answer.