SQL Azure - Transaction (Process ID 160) was deadlocked on lock resources with another process and has been chosen as the deadlock victim

0
votes

We are experiencing the mentioned dead lock exception while doing CRUD on two SQL Server tables from parallel threads by calling Stored Procedures, here is the detailed scenario:

We have a desktop application where we are spinning up a code block in 100 - 150 parallel threads, the code block does insertion in TableA using SQL Bulk Copy and makes calls to three Stored Procedures, The stored procedures do insertion, updation and deletion in TableB based on some selection from TableA.

Soon as the application starts execution of the threads, SQL Server starts throwing the mentioned dead lock exception for a certain number of threads while some of the threads do run fine.

Exception Message: Transaction (Process ID 160) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Any help in this regard is appreciated in advance.

Thanks.

2
sql-servermultithreadingtransactionsazure-sql-database

2 Answers

4
votes

Is this SQL Server or SQL Azure/Azure SQL DB? If it's "box" SQL Server, you might consider ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON. This will enable read versioning. It's still possible to encounter deadlocks in this state, but it's as close to a silver bullet as you're likely to get.

Read versioning changes the concurrency model in some subtle ways, so be sure to read about it first and make sure it's compatible with your business logic: https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

Otherwise, Srivats's other suggestion about minimizing transaction scope is not always simple to implement but is still solid. To that list, I would add: Ensure that you have well-indexed query access paths, and verify that none of the queries within your transactions require full table or index scans.

0
votes

The message is clearly evident that Transaction (Process ID 160) was deadlocked on lock resources with another process.The lock could be on different levels. The locks are not getting released before another thread could lock that particular resource. Try to kill that Process Id and check the workflow if there are any lock conflicts.