SqlClient connection pool maxed out when using async

Viewed 1013

I have a busy ASP.NET 5 Core app (thousands of requests per second) that uses SQL Server. Recently we decided to try to switch some hot code paths to async database access and... the app didn't even start. I get this error:

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.

And I see the number of threads in the thread pool growing to 40... 50... 100...

The code pattern we use is fairly simple:

using (var cn = new SqlConnection(connenctionStrng))
{
    cn.Open();
    var data = await cn.QueryAsync("SELECT x FROM Stuff WHERE id=@id"); //QueryAsync is from Dapper
}

I made a process dump and all threads are stuck on the cn.Open() line, just sitting there and waiting.

This mostly happens during application "recycles" on IIS, when the app process is restarted and HTTP requests are queued from one process to another. Resulting in tens of thousands requests in the queue, that need to be processed.

Well, yeah,, I get it. I think I know what's happening. async makes the app scale more. And while the database is busy responding to my query, the control is returned to other threads. Which try to open more, and more, and more connections in parallel. The connection pool maxes out. But why the closed connections are not returned to the pool immediately after the work is finished?

Switching from async to "traditional" code fixes the problem immediately.

What are my options?

  • Increasing max pool size from the default 100? Tried 200, didn't help. Should I try, like, 10000?
  • Using OpenAsync instead of Open? Didn't help.
  • I thought I'm running into this problem https://github.com/dotnet/SqlClient/issues/18 but nope, I'm on a newer version of SqlClient and it's said to be fixed. Supposedly.
  • Not use async with database access at all? Huh...
  • Do we really have to come up with our own throttling mechanisms when using async like this answer suggests? I'm surprised there's no built-in workaround...

P.S. Taking a closer look at the process dump - I checked the Tasks report and discovered literally tens of thousands blocked tasks in the waiting state. And there's exactly 200 db-querying tasks (which is the size of connection pool) waiting for queries to finish.

1 Answers

Well, after a bit of digging, investigating source codes and tons of reading, it appears that async is not always a good idea for DB calls.

As Stephen Cleary (the god of async who wrote many books about it) has nailed it - and it really clicked with me:

If your backend is a single SQL server database, and every single request hits that database, then there isn't a benefit from making your web service asynchronous.

So, yes, async helps you free up some threads, but the first thing these threads do is rush back to the database.

Also this:

The old-style common scenario was client <-> API <-> DB, and in that architecture there's no need for asynchronous DB access

However if your database is a cluster or a cloud or some other "autoscaling" thing - than yes, async database access makes a lot of sense

Here's also an old archive.org article by RIck Anderson that I found useful: https://web.archive.org/web/20140212064150/http://blogs.msdn.com/b/rickandy/archive/2009/11/14/should-my-database-calls-be-asynchronous.aspx

Related