DB Connection Pool Exhaustion

[gurry]

I'm facing a problem with an Asp.Net Core app where database connections get exhausted the application crashes with this exception:

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.

The action method in question is as below:

       [HttpPost]
        public async void Post(DeviceState state)
        {
            using (var connection =  new SqlConnection("Data Source=localhost;Integrated Security=True");) // Using the default max pool size of 100
            {
                await connection.OpenAsync();
                (var command = new SqlCommand($INSERT INTO test_db.dbo.DeviceState (fqdn, content_name, bytes_from_peer, bytes_from_source, total_size) VALUES(@fqdn, @content_name, @bytes_a, @bytes_b, @total_size)",
                    connection))
                {
                    AddParameter(command, "@fqdn", SqlDbType.VarChar, state.Fqdn + Guid.NewGuid());
                    AddParameter(command, "@content_name", SqlDbType.VarChar, state.ContentName);
                    AddParameter(command, "@bytes_a", SqlDbType.BigInt, state.BytesA);
                    AddParameter(command, "@bytes_b", SqlDbType.BigInt, state.BytesB);
                    AddParameter(command, "@total_size", SqlDbType.BigInt, state.TotalSize);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

And this is the AddParamter() helper method:

 private static void AddParameter(SqlCommand command, string name, SqlDbType type, object value)
        {
            var param = command.CreateParameter();
            param.ParameterName = name;
            param.SqlDbType = type;
            param.Value = value;
            command.Parameters.Add(param);
        }

What the Post() method is doing is INSERTing a row into a table called DeviceState every time it is called. DeviceState table was created with the below T-SQL snippet:

  create table [cdc_test_db].[dbo].[DeviceState](
   fqdn varchar(255) primary key not null,
   content_name varchar(500) not null,
   bytes_a bigint,
   bytes_b bigint,
   total_size bigint
)

I'm using the default max pool size of 100. I'm seeing these exceptions at 20000 requests/s. But interestingly enough I see that the app starts to use all 100 connections even at very low request rates like 1000 requests/s. This is different from the behaviour of classic Asp.Net (4.8) which never goes beyond 10 connections even while delivering 4000-5000 requests/s. Is Asp.Net Core designed to be aggressive when opening DB connections?

I've also observed this problem occurs only when my action method is async and uses async versions of all data APIs. When I switch everything to synchronous code, the problem goes away. So I wonder if this is something caused by async tasks holding onto the DB connections for too long while waiting to be scheduled on the thread pool. What am I doing wrong?

Or is it just a manifestation of this issue: #18, which from what I know should've been fixed in the Microsoft.Data.SqlClient version I'm using (1.0.19269.1). I've also tested with the latest version of Microsoft.Data.SqlClient, 1.1.0, and saw the same issue.

Details:

• Asp.Net Core version: 3.1
• OS: Windows Server 2016
• Database: SQL Server 2016 Standard Edition
• Microsoft.Data.SqlClient version: 1.0.19269.1

Originally reported this issue under AspNetCore (dotnet/aspnetcore#18089), but they suggested I should move it here.

[gurry]

Also tested using the same code with Asp.Net Core 2.1 and System.Data.SqlClient 4.8.0 (note System instead of Microsoft). Ran into the same issue :(.

In the Asp.net Core 2.1 project, also tested by increasing Max Pool Size to 5000. This time it didn't crash (because it didn't breach the limit), but the no. of connections did grow up to ~1900 and was still growing (at about 1 connection/s) when I ended the test. It was delivering a request rate of ~35000/s at that time.

[gurry]

Apologies. This was due to my mistake not an issue in SqlClient. My Post() action method was returning void. It should've returned Task instead.

Returning void from an async action method causes Asp.Net Core to immediately return 200 OK to client before waiting for the async method to finish -- apparently it can't await because there's no Task to await on. This can cause clients to come back with the next request (especially if the client is an HTTP load generator) right away. Pretty soon the system is jammed with unfinished requests causing among other things DB connections to grow quickly.