Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Dead lock issue

Author  Topic 

samsuree
Starting Member

2 Posts

Posted - 2014-12-04 : 05:59:18
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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-04 : 10:41:36
need to see the queries to anaylize
Go to Top of Page
   

- Advertisement -