Blocked sql connections leading to connection pool starvation on Linux with MARS and conn.StateChange

[oncicaradupopovici]

Describe the bug

Connection timeout exceptions when all are met:

1. Runnig on Linux
2. MARS enabled
3. Concurrency level greater than connection pool size (in our case minimum 200)
4. There is some network latency between the client and the server (for example SQL server MI running in Azure)
5. There is conn.StateChange event handler that executes some sql command when connection state is open

The exact exception is:
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.

This happens in both dotnet6 and dotnet7 runtimes, with any version of Microsoft.Data.SqlClient or System.Data.SqlClient packages

After a small period of time all 100 connections maintained by the pool are blocked indefinitely, and all subsequent sql connections time out on connection open. Everything remains blocked.

To reproduce

// See https://aka.ms/new-console-template for more information

using Microsoft.Data.SqlClient;

const string ConnectionString = "Server=YOUR_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PASS;MultipleActiveResultSets=true";

await ExecuteConcurrentCommands();

Console.WriteLine("FINISHED");

Console.ReadLine();

async Task ExecuteConcurrentCommands()
{
    var tasks = Enumerable.Range(0, 1000).Select(ExecuteCommand);

    await Task.WhenAll(tasks);
}

async Task ExecuteCommand(int index)
{
    using var conn = new SqlConnection(ConnectionString);
    conn.StateChange += void (sender, evt) =>
    {
        if (evt.CurrentState == System.Data.ConnectionState.Open)
        {
            var senderConnection = (SqlConnection)sender;

            var commandText = "SET TEXTSIZE " + index;

            using (var command = senderConnection.CreateCommand())
            {
                command.CommandText = commandText;
                command.ExecuteNonQuery();
            }
        }
    };

    try
    {
        await conn.OpenAsync();

        using var command = conn.CreateCommand();
        command.CommandText = $"SELECT TOP 1 {index}  from [__DocumentSignerMigration]";
        var result = await command.ExecuteScalarAsync();

        Console.WriteLine($"Executed select command {index}, result: " + result);
    }
    catch (Exception ex)
    {
        Console.WriteLine("CAUGHT EXCEPTION, " + ex);
    }
    finally
    {
        await conn.CloseAsync();
    }
}

Further technical details

Microsoft.Data.SqlClient version: 4.0.0
.NET target: dotnet7
SQL Server version: Azure Sql Server MI
Operating system: Debian 11

[JRahnama]

@oncicaradupopovici this looks similar to #422. Can you follow with the workarounds on the issue please?

[oncicaradupopovici]

Yes it is simillar to #422 but in our case there is one more thing, all connections maintained by the pool remain blocked, all 100 connections. Indefinitely. The only thing we can do in this case is to restart the process.
The workaraounds in our case, were to either remove MARS were possible, and in other cases wehere MARS rtequired, we have removed the event handler, and executed the "SET TEXTSIZE" cmd after the connection opened, without the event handler.

[JRahnama]

@oncicaradupopovici can you provide the repro in issue #422 and follow the progress there?

Closing the issue as duplicate.