Deadlock issue

Question

if insertRecordCount > 0:
insertDf.write \
.format("com.microsoft.sqlserver.jdbc.spark") \
.option("url", sql_url) \
.option("dbtable", "xyz.abc") \
.option("user", username) \
.option("password", password) \
.option("bulkCopyBatchSize", 10000) \
.option("bulkCopyTableLock","true") \
.option("bulkCopyTimeout","6000000") \
.mode("append") \
.save()

Above statement working fine earlier. All of sudden job is failing with an error
Job aborted due to stage failure: Task 47 in stage 11120.0 failed 4 times, most recent failure: Lost task 47.3 in stage 11120.0 (TID 244766, 10.139.64.8, executor 38): com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Could any one explain how we can avoid the deadlock.

Answer 1

Hi @Santosh Jodangi ,

“Transaction was deadlocked” error occurs when two or more sessions are waiting to get a lock on a resource which has already locked by another session in the same blocking chain. As a result, none of the sessions can be completed and SQL Server has to intervene to solve this problem. It gets rid of the deadlock by automatically choosing one of the sessions as a victim and kills it allowing the other session to continue. The killed session is rolled back.

You can check the system_health session for deadlocks, create an extended event session to capture the deadlocks. Then analyze the deadlock reports and graphs to figure out the problem. Please refer to the blog How to resolve deadlocks in SQL Server to get detail steps.

For the question about avoiding the deadlock, suggest you read How to Avoid Deadlocks in SQL Server part in this blog, hope this could help you.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A deadlock is when you clash with another process. That process holds a lock that prevents your process to continue. At the same time, your process holds another lock, that this other process wants.

SQL Server regularly checks for deadlocks, and when it finds a deadlock chain, it injects an error in one of the processes.

There are means to get details about the deadlock. I suggest that you discuss with your DBA, who may know where to find the deadlock trace.

Also, what actual version of SQL Server are you using? I see spark above, so I cannot really piece it together with the on-prem version.