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

Question

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?

Petrochemical

Deadlock on SQL Server, you'll have to add in your question the stored procedure code as well as the associated part of your DB schema. – Alinaaline

In my case I have to update sql server compatibility from sql server 2016 to 2019 ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 150 – Anciently

Answer 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.

Antonina

Answer 2

You can encapsulate your query in a TRY CATCH block, and catching error numbers (related to locks)

1. 1204
2. 1205
3. 1222

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.

Embassy

Why do you use SELECT @RetryNo = @RetryMaxNo; instead of BREAK;? – Bluster

Because at that point we do not want it to fail, we want to reiterate the process and give it a maximum amount of attempts, before throwing the exception (if it's related to timeout). – Embassy

Why would BREAK make it fail? It's not going to throw an exception because it's already passed any code that could possibly make it fail so the BREAK would simply short circuit the loop. – Bluster

retry for SqlConnectionBroken = -1, SqlTimeout = -2, SqlOutOfMemory = 701, SqlOutOfLocks = 1204, SqlDeadlockVictim = 1205, SqlLockRequestTimeout = 1222, SqlTimeoutWaitingForMemoryResource = 8645, SqlLowMemoryCondition = 8651, SqlWordbreakerTimeout = 30053 – Viator

Answer 3

I had this problem too, so I have used WITH (NOLOCK) after all joins in my stored procedure, so It works and I could fix my problem.

Canonicals

See Bad Habits to kick - putting NOLOCK everywhere - it is not recommended to use this everywhere - quite the contrary! – Assyriology

Answer 4

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.

Tamtam

Answer 5

I had a similar problem with a Microsoft SQL Server database running in a Docker container in my development environment. Restarting the container solved the issue.

Symmetrize

Answer 6

Here is a solution from MSDN by S Kumar Dubey

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/171d9fa9-0a39-48ce-bc38-35623e0c1075/how-can-i-release-lock-on-tables?forum=transactsql

Execute SP: SP_LOCK In Results you will get SPID, DBID, OBJID, INDID, TYPE, RESOURCE, MODE, STATUS Now check the status column, if it is showing wait then kill that SPID. To kill a particular SPID Execute SP: Kill 65 (Where 65 is SPID)

It seems you need to be the SQL server admin to resolve this issue.

Julio

Question is how to resolve deadlock in multi-thread app, not how to kill other sessions – Marci

Answer 7

use With(NoLock) in the query to avoid deadlock while inserting/updating the records

Groundsheet

Please do not repeat the previous answer (moreover rightly flagged as a bad recommendation by @marc_s). – Kirakiran