Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Solution 1
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.
Solution 2
You can encapsulate your query in a TRY CATCH block, and catching error numbers (related to locks)
Then you can automate retries, up to a certain number.. So you would do something like the following;
DECLARE @RetryNo Int = 1
,@RetryMaxNo Int = 5;
WHILE @RetryNo < @RetryMaxNo
BEGIN
BEGIN TRY
-- put your query that generates locks here....
SELECT @RetryNo = @RetryMaxNo;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() IN (1204, 1205, 1222)
BEGIN
SET @RetryNo += 1;
-- it will wait for 10 seconds to do another attempt
WAITFOR DELAY '00:00:10';
END
ELSE
THROW;
END CATCH
END
You can also use table hints such as UPDLOCK.
Solution 3
Be sure what field you are going to update or insert, this field have non clustered index. If not availble you can first create nonclustered index of this field on this table and after create follow below steps.
Right click on table and select properties.
Select Option in right panel in properties.
In lock tab Allow page lock make 'False' and Allow row lock must be 'True' and then press Ok.
- Press New Query button and write command 'update statistics tablename' and execute
- Rebuild non clustered index.
user1018213
Updated on February 19, 2020Comments
-
user1018213 about 4 years
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?