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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2011-07-09 : 14:28:00
|
| I've got an app in beta that uses a table called TaskState as a queue. The app calls a sproc that gets the next active item based on a few columns, most importantly a bit column called Active. It calls the sproc up to 10 times per second. I'm running a deadlock detector which is telling me that deadlocks are occurring anywhere from 0 to 5 times per minute, always on the clustered index of the queue table and always between multiple instances of the sproc. I don't understand it because I'm wrapping the sproc's contents in a REPEATABLE READ isolation level, and the SELECTs and UPDATEs occur within transactions (at bottom, there are 8 possible paths in the sproc that all are fairly similar). Using Erland's beta_lockinfo as well as my deadlock detector I can see that sometimes X "OBJECT" locks are placed on the clustered index of TaskState, with a count of 64. Does that mean 64 rows are locked? If so why aren't they KEY locks? How do I determine why the locks are sometimes X locks on my production server but never on my test server, which is a slower server with a higher level of activity? Neither my deadlock detector nor beta_lockinfo indicate that insertions into TaskState (or any other table) are related to the deadlocks. It looks like the SELECT below doesn't always use the non-clustered index, could that be the problem? Should I force it to? Or do I need a ROWLOCK hint on the SELECT?BEGIN TRANSACTION Path8UPDATE [TaskState] WITH (UPDLOCK)SET [Active] = @ActiveValueOUTPUT INSERTED.ID into @taskStateIdTableWHERE [ID] = (SELECT TOP 1 ts.ID FROM TaskState ts WITH (readpast) JOIN Task t WITH (nolock) on t.ID = ts.TaskID JOIN TaskCompatibility tc on tc.TaskID = ts.TaskID where ts.StateID in (select StateID from RoleStateMapping where RoleID = @roleId) and ts.Active = 1 and ts.SiteID = @siteId and ts.PreferredUserID = @userId and ((@toolId = @IGTool AND tc.Candidate = 1) OR (@toolId = @RMTool AND tc.RMCandidate = 1)) ORDER BY t.DueDate Asc)COMMIT TRANSACTION Path8 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-09 : 14:51:53
|
| Don't hint unless you are absolutely, 100% sure that you know better than the optimiser in all cases.First try optimising the query and indexes (why a readpast hint? That's an odd one). If you're not sure upload the exec plan somewhere (as a .sqlplan file or zipped .sqlplan) and post table and index definitions--Gail ShawSQL Server MVP |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-09 : 14:55:55
|
| p.s. There's little point in a UPDLock hint on an update. By default it starts by taking U locks then converts to X when it makes the change. The hint's not going to change that.--Gail ShawSQL Server MVP |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-07-09 : 19:06:43
|
| I hate to disagree but the deadlocks (as well as the X locks on TaskState) on my test server went away the moment I added UPDLOCK to the update. Also, if you do an Internet search on READPAST with queue tables you'll see that it's a common way to avoid selecting locked rows. |
|
|
|
|
|
|
deadlocks on clustered index