August 26, 2003 at 1:21 pm
Deadlock occurs in our server at least once a week. I captured the information with Trace and Profiler.
We have a program that do the select on table A every second via SP. Another program updates table A almost every minute via ADO (sp_executesql). Table A has a unique cluster index. The Update is based on the cluster index. The Select is based on another noncluster index, however, order by the cluster index.
Based on the information I captured, deadlock occured between these two process.
1) Since both select and update usually take not more than 16 miliseconds, I don't understand why there is a deadlock!?
2) I tried the ReadPast hint on the select SP, but deadlock still occured.... What should I do?
August 29, 2003 at 8:00 am
This was removed by the editor as SPAM
August 29, 2003 at 8:38 am
Although I don't have enough experience in resolving deadlocks to make a recommendation for you, I did just recently find an excellent resource on deadlocks. The Professional Association for SQL Server (PASS) web site now has three presentations from last year's conference that non-members can access for free, including a PowerPoint about deadlocks. Here is the link:
August 29, 2003 at 1:27 pm
Thanks!
August 31, 2003 at 10:19 pm
One way is to set the isolation level to the highest i.e. seriallizable.However this may result in command time out,so you may need to increase the command time out of the select procedure.
Also try to identify if in any ways the update procedure can be made to execute faster. I guess you have done it since it's just taking 16 milliseconds.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply