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

c# sql-server multithreading deadlock
user1018213 picture user1018213 · Feb 9, 2012 · Viewed 72.2k times · Source

I have a C# application which is inserting data into SQL Server (2008) table using stored procedure. I am using multi-threading to do this. The stored procedure is being called from inside the thread. Now my stored procedure is using "tablock" while inserting data. While executing this code I am getting the following error: "Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Can anyone please help me with any solution to this?

Answer

Ben picture Ben · Feb 9, 2012

This occurs when two Sql Server processes are accessing the same resources, but in a different order. Therefore they end up both waiting for the other process, which is a deadlock.

There are a number of ways to prevent it, including:

  • Avoid taking unneccessary locks. Review the transaction isolation level required for the query, use with (nolock) locking hint for queries where appropriate.
  • Make sure that when taking locks you take locks on objects in the same order in each query.

E.g. if Proc1 locks table1 and then table2, but Proc2 locks table2 and then table1, the problem can arise. You can rewrite either proc to take locks in the same order to avoid this problem.

Related questions

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host

I'm using threading in my SQLCLR Project and I'm getting the following error when I try to run my user defined function. What can I do to enable threading things such as the Parallel library and await/async in my …

Read More
How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @…

Read More
How can I set an SQL Server connection string?

I'm developing a simple C# application, and I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password, etc.), but when I connect it to another PC, …

Read More