SNOW-1914311: Getting "Could not obtain a connection from the pool within a given timeout" once upgraded to 4.3.0

[gdoron]

After upgrading our .snowflake nuget package from 3.1.0 to 4.3.0, we are starting to get randomly error of:
Could not obtain a connection from the pool within a given timeout

You can see from the graph below, once a pod gets to this state, nothing works anymore and it must be restarted, which fixes it immediately.
Any idea what this is all about and how it can be addressed?

1. What version of .NET driver are you using?
   4.3.0

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

3. What version of .NET framework are you using?
   E.g. .NET core 8

4. What did you do?

Snowflake.Data.Client.SnowflakeDbException (0x80004005): Error: Snowflake Internal Error: Unable to connect SqlState: 08006, VendorCode: 270001, QueryId:
---> System.AggregateException: One or more errors occurred. (Could not obtain a connection from the pool within a given timeout)
---> System.Exception: Could not obtain a connection from the pool within a given timeout
at Snowflake.Data.Core.Session.SessionPool.WaitForSession(String connStr)
at Snowflake.Data.Core.Session.SessionPool.GetIdleSession(String connStr, Int32 maxSessions)
at Snowflake.Data.Core.Session.SessionPool.GetSessionAsync(String connStr, SecureString password, SecureString passcode, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at Snowflake.Data.Client.SnowflakeDbConnection.b__55_0(Task`1 previousTask)
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---

[AndrewRybka]

I'm getting the same error on Windows 10 and .NET Framework 4.8 after upgrading to 4.3.0 from 2.2.0. The driver works fine while I execute queries consecutively but fails when I try to open two parallel connections. After the first error, all subsequent attempts to open a connection result in the same error.

[sfc-gh-dszmolka]

hi, thanks for submitting this issue. the pooling has been significantly reworked in the new versions at v4 and above, and there's a couple of parameters now which you can consider to use, to fine-tune the behaviour.

Please take a look at https://github.com/snowflakedb/snowflake-connector-net/blob/master/doc/ConnectionPooling.md. What one can experiment with is perhaps specifying a bigger MaxPoolSize . It is also important to close every connection at the end of using it, because if connection is not closed then the connection pool thinks that it is still being used, and count this connection as the used one. If connections are not properly closed it is very easy to exhaust the pool.

If there is a suspicion that connections are not properly closed so the busy sessions counter is too high, then the pool can be reset by SnowflakeDbConnectionPool.GetPool(connectionString).ClearPool().

Furthermore, ExpirationTimeout is also something to look at; it's by default set to wait 1 hours to expire the connection after it becomes idle. Maybe it's not necessary to wait that long in this particular use-case.

Please also note that now the connection string (options, and the order of options) now uniquely identifies the pool, and it's possible to create multiple pools for e.g. multiple use-cases within the application (e.g. one needs 5 connections and the other 15, it's absolutely possible to configure 2 different pools for the both of them)

Hope this helps in tackling this issue - everything is configurable now, so hopefully with some testing, the optimal configuration can be found in each of your use-cases.

[AndrewRybka]

Hi @sfc-gh-dszmolka, thanks for looking into this. I was wrong about two parallel connections. I was actually hitting the MaxPoolSize limit. However, it appeared that I was hitting the limit because of the following:

• The SnowflakeDbCommand.ExecuteDbDataReader and SnowflakeDbCommand.ExecuteDbDataReaderAsync doesn't support the CommandBehavior parameter and silently ignores non-default values (my code is using CommandBehavior.CloseConnection)
• A connection is not released to the pool when a SnowflakeDbConnection object that wasn't explicitly closed or disposed is collected by the garbage collector

Should I file a separate bug report on those issues, or are they intentional by design?

[sfc-gh-dszmolka]

hey @AndrewRybka good to hear you found the reason for the error ! Regarding your remarks, I believe both are related
to this part link of the documentation specifically mentions you'll need to explicitly close/dispose of the connections, unless that happens, the connection is not returned to the pool, thus to me looks like working by design.

Busy
Busy connection is provided by the pool and it is counted to the pool size. It is returned to be reused during Close operation. When application does not close connections it may hit the limit of Maximum pool size.

even though on the same page, we provide examples on how to close the connection, you're right that this part

It is returned to be reused during Close operation

could be maybe even more explicit, by providing an example, like

It is returned to be reused during Close operation (connection.Close())

[AndrewRybka]

Hey @sfc-gh-dszmolka, thank you for the clarification.

So no plans to support CommandBehavior.CloseConnection argument in the SnowflakeDbCommand.ExecuteDbDataReader and SnowflakeDbCommand.ExecuteDbDataReaderAsync methods?

[sfc-gh-dszmolka]

Indeed, i'm not aware of such plans for the near future.