Is the Connection object thread-safe?

[gregakespret]

We'd like to:

• start a transaction on a connection (connection.setAutoCommit(false))
• issue several statements, each on its own thread, reusing the same java.sql.Connection
• commit a transaction (connection.commit())

Can we do that and rely on it to be thread-safe?

[howryu]

Yes. Connection is thread-safe and it can be shared by multiple statements.

[gregakespret]

Awesome! Can we rely on this in the future as well (given that this is not documented anywhere)?

[howryu]

Yes. You can rely on it. If it does not work, it will be treated as a bug.

I will add documenting this to my todo list.

[gregakespret]

Perfect, thanks!

[vaibhavsingh007]

Hi, so I just ran into an issue where k records from across n Spark dataframes are written as k+y where y is non-deterministic, using concurrent write using same SF jdbc connection (drivers: spark-snowflake_2.11-2.5.2-spark_2.4.jar, snowflake-jdbc-3.9.1.jar).

write config:

SNOWFLAKE_SOURCE_NAME = "net.snowflake.spark.snowflake"
    
    # Set options below
    sfOptions = {
        "sfURL" : "****",
        "sfAccount" : "****", # Also needed create stage privilege.
        "sfUser" : username,
        "sfPassword" : password,
        "sfDatabase" : database,
        "sfSchema" : schema,
        "sfWarehouse" : warehouse,
        "sfRole" : role
    }
    
    sparkDF.write \
    .format(SNOWFLAKE_SOURCE_NAME) \
    .options(**sfOptions) \
    .option("dbtable", f"MY_WORKSPACE.{table}") \
    .mode('append') \
    .save()

How can I synchronize this to achieve data integrity?

[vaibhavsingh007]

@howryu any thoughts?

[wheezil]

Is it also safe to use a Connection for concurrent client-side PUT statements? For example
PUT 'file:///tmp/data1.gz' @stage/load
We are creating a series of local stage files, and as each file is finished we want to issue a PUT into the same internal stage.

[sfc-gh-sghosh]

Hello Wheezil,

Yes, its safe to use a connection for concurrent client-side PUT statements in Snowflake. Snowflake's architecture is designed to handle concurrent operations efficiently, including file uploads using PUT statements.

Regards,
Sujan Ghosh