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.
| 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 READBEGIN 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.ThanksSuresh A |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-12-04 : 10:41:36
|
| need to see the queries to anaylize |
 |
|
|
|
|
|
Dead lock issue