NO_DUPLICATES causes deadlock as multiple tasks try to insert data into same global temp table

[ankitbko]

Because the temp table remains unique to a worker for a table, multiple tasks executing on the same worker will result in deadlock,

[pramodnagare]

Hi ankitbko
Try adding below option, it will solve your table lock issue.

option("tableLock", True)

[ankitbko]

@pramodnagare tablelock option is meant to be used with heap tables only. tablelock option is set to true automatically for staging tables.

sql-spark-connector/src/main/scala/com/microsoft/sqlserver/jdbc/spark/connectors/ReliableSingleInstanceStrategy.scala

Line 85 in 526f08d

val newOptions = new SQLServerBulkJdbcOptions(options.parameters + ("tableLock" -> "true"))

cc: @shivsood

[bunkersdev]

Hello, I am encountering the same issue. I need to use the following options
.option("schemaCheckEnabled", False) \ .option("reliabilityLevel","NO_DUPLICATES") \

@shivsood

[bunkersdev]

Any update on this ?

[shivsood]

@ankitbko does this happen only on databricks? Can u send repro step and your scripts for us to repro this. @luxu1-ms as FYI

[gerardwolf]

I also encounter this issue when running multiple instances of the same notebook on the same cluster (DBR 7.3 LTS) using Azure Databricks. My notebook attempts to load data from delta lake table source and overwrite the data in an existing Azure SQL Database table using the following options:

try: dfSource.write \ .format("com.microsoft.sqlserver.jdbc.spark") \ .mode("overwrite") \ .option("url", url) \ .option("dbtable", sinkTable) \ .option("user", userName) \ .option("password", password) \ .option("truncate", "true") \ .option("tablock", "true") \ .option("schemaCheckEnabled", "false") \ .option("batchsize", batchsize) \ .option("reliabilityLevel", "NO_DUPLICATES") \ .save() except ValueError as error : print("Connector write failed", error)

The intent is to preserve the table definition (and indexes) in the sink database. I realise the tablock is only required for heaps, but this is a generic notebook that loads to both indexed and heap tables.
Importantly, I don't get an error when I run the notebook in isolation, so it appears that there is some shared global temp table that is causing deadlocks in parallel execution scenario.

[luxu1-ms]

I think gerardwolf's issue is different from #49, but related with #132.

[gerardwolf]

Certainly looks like it is caused by the name of the temp staging table not being granular enough. Adding the sink table name to the temp table should resolve this yes.

[luxu1-ms]

@ankitbko Could you please provide repro scripts? I am not able to repro this issue.

[luxu1-ms]

Close this issue since no more info provided and cannot repro it. Please reopen if more info provided.