BackgroundService Await Task.Delay in infinite loop leaks SqlConnection objects hard

Question

I have code similar to this running in multiple BackgroundServices (.NET 7). After just a few days of runtime (obviously, delay is in minutes (to hours) between loops) leads to a massive Memory Leak in tens of thousands of dangling SqlConnection handles (probably all of them ever used are still referenced, even if properly disconnected from the DB). I'm using .NET's SqlClient 5.0.1.

MRE

using System;
using System.Threading;
using System.Threading.Tasks;
using System.Diagnostics;

using Microsoft.Data.SqlClient;

namespace Memleak;

static class Program
{
    const string connectionString = "Server=lpc:localhost;"
        + "Integrated Security=True;Encrypt=False;"
        + "MultipleActiveResultSets=False;Pooling=False;";

    static async Task Main(params string[] args)
    {
        using CancellationTokenSource cts = new();
        // not to forget it running
        cts.CancelAfter(TimeSpan.FromMinutes(15));
        CancellationToken ct = cts.Token;

        using Process process = Process.GetCurrentProcess();
        long loop = 1;

        while (true)
        {
            await ConnectionAsync(ct);

            // this seems to be the issue (delay duration is irrelevant)
            await Task.Delay(TimeSpan.FromMilliseconds(1), ct);

            process.Refresh();
            long workingSet = process.WorkingSet64;
            Console.WriteLine("PID:{0} RUN:{1:N0} RAM:{2:N0}",
                process.Id, loop, workingSet);

            ++loop;
        }
    }

    private static async Task ConnectionAsync(CancellationToken ct = default)
    {
        using SqlConnection connection = new(connectionString);
        await connection.OpenAsync(ct);

        using SqlCommand command = connection.CreateCommand();
        command.CommandText = "select cast(1 as bit);";

        using SqlDataReader reader = await command.ExecuteReaderAsync(ct);
        if (await reader.ReadAsync(ct))
        {
            _ = reader.GetBoolean(0);
        }
    }
}

Leak

These following command prompt commands show the leak:

// dotnet tool install --global dotnet-dump
dotnet-dump collect -p pid
dotnet-dump analyze dump_name.dmp
dumpheap -type Microsoft.Data.SqlClient.SqlConnection -stat
dumpheap -mt mtid
dumpobj objid
gcroot objid

Last command shows a huge list of System.Threading.CancellationTokenSource+CallbackNode for a SqlConnection object.

Question

Is this a bug or working as expected (and if so, why)? And is there any easy workaround except getting rid of all async code and just using Threads? I cannot use Timers since Delays are variable upon certain factors (when work is available, delays are shorter; when work is not, delays are longer).

A non-async version does not leak

using System;
using System.Threading;
using System.Threading.Tasks;
using System.Diagnostics;

using Microsoft.Data.SqlClient;

namespace NotMemleak;

static class Program
{
    const string connectionString = "Server=lpc:localhost;" +
        "Integrated Security=True;Encrypt=False;" +
        "MultipleActiveResultSets=False;Pooling=False;";

    static void Main(params string[] args)
    {
        using CancellationTokenSource cts = new();
        // not to forget it running
        cts.CancelAfter(TimeSpan.FromMinutes(15));
        CancellationToken ct = cts.Token;

        using Process process = Process.GetCurrentProcess();

        long loop = 1;
        while (loop < 1000)
        {
            Connection();

            // this seems to be the issue (delay duration is irrelevant)
            ct.WaitHandle.WaitOne(TimeSpan.FromMilliseconds(1));
            // Thread.Sleep();

            process.Refresh();
            long workingSet = process.WorkingSet64;
            Console.WriteLine("PID:{0} RUN:{1:N0} RAM:{2:N0}"
                , process.Id, loop, workingSet);

            ++loop;
        }

        Console.WriteLine();
        Console.WriteLine("(press any key to exit)");
        Console.ReadKey(true);
    }

    private static void Connection()
    {
        using SqlConnection connection = new(connectionString);
        connection.Open();

        using SqlCommand command = connection.CreateCommand();
        command.CommandText = "select cast(1 as bit);";

        using SqlDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            _ = reader.GetBoolean(0);
        }
    }
}

Pinkie

Is this due to an infinite while loop? have you tried running in limited times like 100 than 10000 and .... – Pi

Just ran it 999 times and added a Console.ReadKey(true); at the end. 999 SqlConnection dangling handles... so it's not the number of loops. The number of loops just reveals the leak since memory really starts to grow after a few days. It's the await Task.DelayAsync somehow. – Pinkie

And if you replace Task.Delay with Thread.Sleep? Hard to imagine how this delay could leak SqlConnection in unrelated code path. – Dielu

@Dielu Just rewrote the code without any await (Thread) and it doesn't leak. That's why I was so shocked. You have to run it to believe it. Unless there's something I don't understand in all this. – Pinkie

why are not using the same connection instead of creating/disposing of connection infinitely and that too very fast @Pinkie – Pi

@viveknuna My code is way more complicated than this. This is a functional example. And it doesn't matter. Pooling (which I have enabled but disabled here) handles Connection reuse. Issue here is memory, not any kind of more efficient connection use. – Pinkie

Answer 1

I believe this is related to this issue in GitHub. As I understand it, it's a regression introduced in SqlClient 5.0.1. Basically, on this line:

await reader.ReadAsync(ct)

You pass a token, and the reader will internally register a callback function when this token is cancelled. However, this registration is not properly unregistered on all code paths. This in turn results in your SqlConnection instances being reachable from CancellationTokenSource through that callback registration (which references data reader, which references command, which references connection).

This is fixed in 5.1.0, which had a stable release on 2023-01-19.

Dielu