SNOW-297231: Is SnowflakeDbConnection thread safe?

[shawnjones253]

Since connection pooling isn't implemented, and because the connection itself is backed by HttpClient, and we know that HttpClient should be reused rather than created/disposed constantly, what's the best way to use the snowflake .NET connector when you'll be making many queries across threads?

The docs suggest that you simply create a new connection for each query and dispose when done, but that can lead to thread exhaustion due to repeated create/dispose of HttpClient.

Note: I'm not planning on changing any properties of the connection once it's created (schema, database, etc.).

Would this work?
For example:

// application startup registers this provider as a singleton
public class SnowflakeConnectionProvider : IDisposable
{
  private IDbConnection _conn;

  public SnowflakeConnectionProvider()
  {
    _conn = new SnowflakeDbConnection();
    _conn.ConnectionString = "connectionString";
    _conn.Open();
  }

  public IDbConnection conn { get => _conn; }

  public Dispose() => _conn.Close();
}

Now, is it safe for multiple threads to share the one SnowflakeDbConnection like so:

public class Worker
{
  public Worker(SnowflakeConnectionProvider provider)
  {
    IDbConnection conn = provider.conn;
    
    IDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select * from t";
    IDataReader reader = cmd.ExecuteReader();
                
    while(reader.Read())
    {
        Console.WriteLine(reader.GetString(0));
    }
  }
}

[sfc-gh-fhoffa]

Link to similar stack overflow, to update later: https://stackoverflow.com/q/66557662/132438

[stegus64]

You do not have to worry about HttpClient.

Even if you are constantly creating and disposing connections from multiple different threads, a single HttpClient is created once and then reused and shared by all connections.

After looking at the code I believe that it should be ok to have multiple threads share a single connection, just like in your sample code.

This is might be a good thing to do since every time you open a new connection a new authentication is performed which takes some time.

However note that if your queries are using snowflake session variables, these variables are shared by all commands using the same connection. So in that case you might want to implement your own connection pool so each thread gets its own private connection to use for a series of related statements.

[shawnjones253]

@stegus64 I should probably have been more concrete with my use case. I'm using this within a web API for each request, so I don't have n fixed threads, but rather a thread for each API request, so new threads are constantly being created.

[stegus64]

@shawnjones253 As long as you do not use any session variables in your snowflake code, you can have multiple threads sharing the same SnowflakeDbConnection. It does not matter if you are reusing threads or if you are creating new threads all the time.

[shawnjones253]

Thanks so much @stegus64 -- this all makes sense now! :)

[whudgins]

@shawnjones253 did this end up working for you as described? We're trying to solve a very similar problem using the connector.

[shawnjones253]

@whudgins We're using session variables, and I didn't want them to leak between requests, so I ended up just having the connection provider create a new SnowflakeDbConnection() for each request. So far no issues.

[richardware]

@shawnjones253 Are session variables still supported as part of the connection string?