 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008DeadLock on Index
Hi, I found a deadlock situation during stress testing our ASP.Net app. The deadlock is on a primary key index between two select statements select against the same table where the index sits. How could deadlock happen in Select statements is just beyond me. One statement is SELECT Comments, DelegatedByClientID, DelegatedByEmployeeID, NewStatus, OldStatus, ProcessedDate, ProcessedRoleCode, ProcessedUsername, WorkflowHistoryID, WorkflowID FROM WorkflowHistory WHERE (WorkflowID = @WorkflowID) ORDER BY ProcessedDate The other one is SELECT Comments, DelegatedByClientID, DelegatedByEmployeeID, NewStatus, OldStatus, ProcessedDate, ProcessedRoleCode, ProcessedUsername, WorkflowHistoryID, WorkflowID FROM WorkflowHistory WHERE (WorkflowID = @WorkflowID) AND (NewStatus = @NewStatus) ORDER BY WorkflowHistoryID The primary key is the WorkflowHistoryID. And there is no other index. Any help will be appreciated very much. > Hi, > [quoted text clipped - 20 lines] > > Any help will be appreciated very much. what isolation level are you using? Hi Alex, it is 'read committed'. > > Hi, > > [quoted text clipped - 22 lines] > > what isolation level are you using? > The primary key is the WorkflowHistoryID. And there is no other index. Well that means you are scanning the table for sure then and that always increases the chance of a deadlock. Why don't you have an index on WorkFlowID? And why are you ordering by WorkFlowHistoryID? If it is jsut an ID how can you guarantee it will be in the order you want? In any case I am not sure why this deadlocked but you might want to do a trace so you can see the actual Deadlock graph and then you don't have to guess. I would also add appropriate indexes so it will scale.  SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Hi, > [quoted text clipped - 20 lines] > > Any help will be appreciated very much. Just in case somebody interested in this post: It turned out that the lock is on the table not the index when sql server tries to do a full scan of the table. Created some indexes and statistics and the issue is resolved. So deadlock could happen between 2 select queries. > Hi, > [quoted text clipped - 20 lines] > > Any help will be appreciated very much. See this series for some wonderful information on deadlock investigation and resolution: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Pa rt-1.aspx SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Just in case somebody interested in this post: > It turned out that the lock is on the table not the index when sql server [quoted text clipped - 27 lines] >> >> Any help will be appreciated very much. >Just in case somebody interested in this post: >It turned out that the lock is on the table not the index when sql server >tries to do a full scan of the table. Created some indexes and statistics and >the issue is resolved. >So deadlock could happen between 2 select queries. Creepy. Glad you solved it. Never seen it happen, probably because I'm pretty generous with indexes. Unless this is a new behavior in SQL 2005? Josh |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.