SNOW-2044393: Connection pool leak if user cancels connection

[john-uipath]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of .NET driver are you using?
   Snowflake.Data 4.4.0

2. What operating system and processor architecture are you using?
   Windows

3. What version of .NET framework are you using?
   .net 9.0

4. What did you do?

Cancel query, the connection didn't return back to pool, and connection pool leak.

        private static async Task RunQueryAsync(int threadId, CancellationToken cancellationToken)
        {
            var connectionString = $"scheme=https;ACCOUNT=<>;HOST=<>.west-europe.azure.snowflakecomputing.com;port=443; ROLE=PUBLIC; USER=<>; PASSWORD=<>; CONNECTION_TIMEOUT=30;insecuremode=false;maxPoolSize=2;PoolingEnabled=true";

            {
                using var connection = new SnowflakeDbConnection { ConnectionString = connectionString };
                await connection.OpenAsync(cancellationToken);

                using var command = connection.CreateCommand();
                command.CommandText = "SELECT SYSTEM$WAIT(6);"; 

                try
                {
                    await command.ExecuteNonQueryAsync(cancellationToken);
                    Console.WriteLine($"Thread {threadId}: Query completed.");
                }
                catch (OperationCanceledException)
                {
                    Console.WriteLine($"Thread {threadId}: cancelled, connection state {connection.State}");
                }
            }
        }

        static void Main(string[] args)
        {
            int ThreadCount = 5;
            var tasks = new Task[ThreadCount];
            var ctsList = new CancellationTokenSource[ThreadCount];

            for (int i = 0; i < 2; i++) // start 2 threads, the connection pool size is 2 as well
            {
                int threadId = i; // Capture the value of i in a local variable
                var cts = new CancellationTokenSource();
                tasks[i] = Task.Run(() => RunQueryAsync(threadId, cts.Token));
                ctsList[i] = cts;
            }
            Thread.Sleep(3000);
            for (int i = 2; i < 5; i++)
            {
                int threadId = i; // Capture the value of i in a local variable
                var cts = new CancellationTokenSource();
                tasks[i] = Task.Run(() => RunQueryAsync(threadId, cts.Token));
                ctsList[i] = cts;
            }
            Thread.Sleep(3000);
            for (int i = 0; i < 2; i++) // cancel the first 2
            {
                ctsList[i].CancelAsync(); // don't await
            }

            Task.WhenAll(tasks).Wait();
            Console.WriteLine("All queries completed.");
   }

5. What did you expect to see?

Expect the first 2 threads abort with OperationCanceledException, and these 2 connections should be automatically disposed and returned back to the connection pool.
The later 3 threads should be able to finish query.

Actually the first 2 connections was not returned to the pool due to their State is already Closed, check out IsNonClosedWithSession() in SnowflakeDbConnection.Close(). Since connections are already Closed, so won't try to TryToReturnSessionToPool().

        public override void Close()
        {
            logger.Debug("Close Connection.");
            if (IsNonClosedWithSession())
            {
                var returnedToPool = TryToReturnSessionToPool();
                if (!returnedToPool)
                {
                    SfSession.close();
                }
                SfSession = null;
            }
            _connectionState = ConnectionState.Closed;
        }

The later 3 threads are not able to OpenAsync() with error: Could not obtain a connection from the pool within a given timeout.

6. Can you set logging to DEBUG and collect the logs?

Unfortuantely I tried, but not able to see any logs generated.

[john-uipath]

I commented out line "if (IsNonClosedWithSession())", seems working, but not sure if there's any other side effect.

[john-uipath]

Unfortuantely I could not find a workaround on my client side.

[john-uipath]

Opened a support ticket as well: 01005066

[sfc-gh-dszmolka]

thank you for reporting this issue also for raising the support case. Since it has strict SLAs (and this ticket has none), it is indeed better suited for production type issues.

Closing this one out and the support team will handle 01005066.