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

• sushmeister

  SSC Enthusiast

  Points: 141

  More actions

  December 4, 2014 at 4:01 am

  #150254

  Please suggest how we can avoid dead lock issue for the below scenario in SQL Server.

  I have multiple instances of windows services running which will process the records which has status as “Ready”.

  There are 3 statuses for each record – Undefined, Processing, Pending. Initially the record which has status as “Undefined” or “Pending” status will be considered.

  There is a procedure which will be invoked and the status will be updated to “Processing” so that other instances will not pick those records which are in “Processing” Status.

  There are 2 transactions in the procedure.

  1.Transaction 1 - Update the status to “Ready” incase if any record has status as “Processing” for more than 20 mins. So that this record will be processed again.

  2.Transaction 2

  a.Pick the minimum record which has status as “Undefined” or “Pending”

  b.Update the status as “Processing”

  We have these inside a transaction since there are multiple instances of services running so we don’t want same record to be selected which was already selected by another instance.

  We have used the below to avoid locking issues and READPAST is used for all the tables which are used in the query. The below is used for each of the transaction along with READPAST.

  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

  BEGIN TRANSACTION

  COMMIT TRANSACTION

  But still we are getting dead lock issue with the error message as “Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

  Also FYI, we are getting this only when multiple instances of the service are running and there is another process which inserts data into the same table. There is no threading used in the service and it’s a synchronous process.

  Please let me know how this issue can be resolved.

  Thanks

  Suresh A

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 4, 2014 at 4:47 am

  #1762671

  Please post the involved queries and the deadlock graph

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass