SQLServerException: deadlock

[swzzzsw]

I'm using databricks to connect to a SQL managed instance via JDBC. SQL operations I need to perform include DELETE, UPDATE, and simple read and write. Since spark syntax only handles simple read and write, I had to open SQL connection using Scala and perform DELETE and UPDATE queries.

Here's a sample scala code I use to execute delete queries:

val connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
 
val statement = connection.createStatement()
 
val queryStr = "DELETE FROM SAMPLE"
 
val res = stmt.execute(queryStr)
 
connection.close()

These lines work perfectly fine if I run one notebook at a time. However, when I run several notebooks in parallel, I can get into deadlock issues (see below)

How can I resolve this error?

[-werners-]

this is not a spark error but purely the database.

There are tons of articles online on how to prevent deadlocks, but there is no single solution for this.

[swzzzsw]

I'm not a fluent Scala user. Do you happen to know one solution that deals with JDBC in Scala?

[-werners- · Accepted solution]

the issue is not your code but the fact that you run the queries in parallel. The SQL server database cannot handle that for some reason.

f.e. one notebook run is doing an update while another wants to delete that record.

[swzzzsw]

Got it! Thank you so much! It looks like I can use error handling to rerun the deadlock victim until it works. Thanks for pointing me to the right direction!