EF Core and big traffic leads to max pool size was reached error

Question

We're using ASP.NET Entity Framework Core for querying our MSSQL database in our Web API app. Sometimes when we have big traffic, querying to DB ends with this error:

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.

I wonder if our pattern of using DbContext and querying is correct or if I am missing some using/dispose pattern and error is caused by some memory leak (after some research I read then I should not use using because the lifetime is managed by the framework). I am following documentation...

My connectionString:

"myConnection": "Server=xxx;Database=xxx;user id=xxx;password=xxx;Max Pool Size=200;Timeout=200;"

My Startup.cs

public void ConfigureServices(IServiceCollection services)
    {
    .....
    // scoped context            
    services.AddDbContext<MyDbContext>(
            options => options.UseSqlServer(this.Configuration.GetConnectionString("myConnection")));
    }

then in controllers I used dbcontext by dependency injection:

public class MyController : Controller
   public MyController (MyDbContext context)
    {
        this.Context = context;            
    }

    public ActionResult Get(int id)
    {
        // querying
        return this.Context.tRealty.Where(x=>x.id == id).FirstOrDefault();
    }

Should I use something like:

using (var context = this.Context)
        {
            return this.Context.tRealty.Where(x => x.id == id).FirstOrDefault();
        }

But I think that this is bad pattern when I am using dependency injection of DbContext.

Solution 1

I think problem was caused by storing objects from database context queries to In memory cache. I had one big LINQ query to database context with some other subqueries inside. I called FirstOrDefault() on the end of main query but not inside subqueries. Controller was fine with it, it materialize queries by default.

 return this.Context.tRealty.AsNoTracking().Where(
                x => x.Id == id && x.RealtyProcess == RealtyProcess.Visible).Select(
                s => new
                { .....

// subquery
videos = s.TVideo.Where(video => video.RealtyId == id && video.IsPublicOnYouTube).
                        Select(video => video.YouTubeId).ToList()), // missing ToList()
.....
 }).FirstOrDefault();

And there was problem - subqueries were holding connection to database context when they where storing to In memory cache. When I implemented Redis distributed cache, it was first failing on some strange errors. It helps when I write ToList() or FirstOrDefault() to all my subqueries because distributed cache needs materialized objects.

Now I have all my queries materialized explicitly and I got no max pool size was reached error. So that one must be careful when stored objects from database context queries to In memory cache. It is need to materialize all queries to avoid to holding connection somewhere in memory.

Solution 2

I am adding an alternative answer, in case anyone lands here with a slightly different root cause, as was the case for my .NET Core MVC application.

In my scenario, the application was producing these "timeout expired... max pool size was reached" errors due to mixed use of async/await and Task.Result within the same controller.

I had done this in an attempt to reuse code by calling a certain asynchronous method in my constructor to set a property. Since constructors do not allow asynchronous calls, I was forced to use Task.Result. However, I was using async Task<IActionResult> methods to await database calls within the same controller. We engaged Microsoft Support, and an Engineer helped explain why this happens:

Looks like we are making a blocking call to an Async method inside [...] constructor.

...

So, basically something is going wrong in the call to above highlighted async method and because of which all the threads listed above are blocked.

Looking at the threads which are doing same operation and blocked:

...

85.71% of threads blocked (174 threads)

We should avoid mixing async and blocking code. Mixed async and blocking code can cause deadlocks, more-complex error handling and unexpected blocking of context threads.

https://msdn.microsoft.com/en-us/magazine/jj991977.aspx

https://blogs.msdn.microsoft.com/jpsanders/2017/08/28/asp-net-do-not-use-task-result-in-main-context/

Action Plan

Please engage your application team to revisit the application code of above mentioned method to understand what is going wrong.

Also, I would appreciate if you could update your application logic to not mix async and blocking code. You could use await Task instead of Task.Wait or Task.Result.

So in our case, I pulled the Task.Result out of the constructor and moved it into a private async method where we could await it. Then, since I only want it to run the task once per use of the controller, I store the result to that local property, and run the task from within that method only if the property value is null.

In my defense, I expected the compiler would at least throw a warning if mixing async and blocking code is so problematic. However, it seems obvious enough to me, in hindsight!

Hopefully, this helps someone...

Solution 3

You can set the lifetime of the DbContext in your startup.cs, see if this helps:

    services.AddDbContext<MyDbContext>(options => options
                                       .UseSqlServer(connection), ServiceLifetime.Scoped);

Also if your query is a simple read you can remove tracking by using .AsNoTracking().

Another way to improve your throughput is to prevent locks by using a transaction block with IsolationLevel.ReadUncommitted for simple reads. You can also use the Snapshot isolation level - which is slightly more restrictive - if you do not want dirty reads.

TransactionOptions transactionOptions = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted};
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
  // insert magic here
}

Edit : As the author of the question mentioned, the above code is not (yet?) possible in EF Core.

A workaround can be found here using an explicit transaction:

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
           // transaction.Commit();
           // transaction.Rollback();
        }
    }

I have not tested this.

Edit 2: Another untested snippet where you can have executed commands to set isolation level:

                using (var c1= new SqlConnection(connectionString))
                {
                    c1.Open();
                    // set isolation level
                    Exec(c1, "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
                    Exec(c1, "BEGIN TRANSACTION;");
                    // do your magic here
                }

With Exec:

        private static void Exec(SqlConnection c, string s)
        {
            using (var m = c.CreateCommand())
            {
                m.CommandText = s;
                m.ExecuteNonQuery();
            }
        }

Edit 3: According to that thread, Transactions will be supported from .NET Core version 1.2 onwards.

@mukundabrt this is tracked by dotnet/corefx#2949. Note that TransactionScope has already been ported to .NET Core but will only be available in .NET Core 1.2.

Comments

[Vít Zaoral]

Lifetime of the DbContext is Scoped by default (by definition of AddDbContext method)... Using .AsNoTracking() is good idea because my api is read only, thanks. But I think that problem is somewhere in bad closing of connections, AsNoTracking should make just quicker queries. In EF Core I think I can't use TransactionScope because it's not supported for now link

[Darxtar]

Is your timeout occurring at the connection level or at a query level? You can also try to set the Database.CommandTimeout property in particular cases.

[Vít Zaoral]

Errors occurred when I use some LINQ query. I have set TimeOut=200 in my connection string, I think it's really big value for easy get query (our queries are quick until the all connections are used, then error occured and app is totally dead. We can just restart it.)

[Darxtar]

The Timeout in the connection string is for the connection timeout, not the command. You can specify a CommandTimeout value in Context.CommandTimeout before your query if it's big, and restore it to a default value after. msdn.microsoft.com/en-us/library/…

[Darxtar]

I edited my reply using an UNTESTED explicit transaction code from an other SO answer, you could give it a try.

[Vít Zaoral]

So, now is our api running without max pool size was reached errors. I don't know if there is some connection, but we were using In Memory Caching in our app to cache results from database queries. Now we are using Redis Distributed Cache and everything is working fine. So hard to say...

[Darxtar]

Glad you managed to sort out your issue. This is weird, one would expect in memory caching not to affect the collection pool. Is it possible that in certain conditions something in the cache is preventing the db context from being disposed? What was the expiration time of your cache and did changing it have any effect?

[Vít Zaoral]

Yes, I think problem was with storing objects from database context queries to In memory cache. Expiration time was 2 hours (absolute expiration) a changing has no effect. I will describe my solution in Answer...

[James White]

OUCH, just hit this. Here is a SQL Query to identify the pool poopers : gist.github.com/jakenuts/eeb56da402b571ba6ca30c4fcffc7247

[Emalsha Rasad]

This is a really good point. Thanks @kevin-m-lapio for point out this. I had the same problem. link this blog post helped me to understand the problem and to fix it.