cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQLServerException: deadlock

swzzzsw
New Contributor III

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)

image.pngHow can I resolve this error?

1 ACCEPTED SOLUTION

Accepted Solutions

-werners-
Esteemed Contributor III

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.

View solution in original post

4 REPLIES 4

-werners-
Esteemed Contributor III

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
New Contributor III

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

-werners-
Esteemed Contributor III

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
New Contributor III

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!

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!