March 28, 2011 at 1:09 pm
Those selects are doing a "select top 30" - do you have an order by on this query? If so, check to see if you have index(es) that will cover the order by.
It seems to me that you might be doing a scan to do this, and that is what is causing your deadlock.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 29, 2011 at 2:13 am
Yes, tuning the selects gets rid of the deadlock. Unfortunately the selects are from adhoc reporting so there is only so much tuning we can do in advance. The reason why tuning eliminates the deadlock appears to be either:
1- the pressure on the server is decreased so fewer collisions -> no deadlock. This is acceptable enough, and hopefully a way forward.
2- tuning results in different query plans that avoid the scenario that locks. This strategy makes me feel uncomfortable, I seem to be saying "if something bad happens on the server that you can't explain, just rearrange the plans and hope for the best".
So, what I really would like to be able to do is explain the sequence of events that lead to this deadlock based on the deadlock graphs etc.
The only kind of scenario I can make out is something like this:
"select" S locks an index if2 key 1 during a join
"insert" X locks the index if2 key 2 during insert
"select" requests an S lock on index if2 key 2 but gets blocked and waits
What I can't figure is why the insert gets blocked.
March 30, 2011 at 9:05 am
Ok, now I understand the deadlock. It isn't a single resource being deadlocked, but is a the familiar kind of deadlock that everyone is familiar with.
Looking at the 1204 output and the SQL profiler diagram there was missing information - I could only see the locks on the foreign key index. More information does appear on the 1222 and in the xml behind the diagram, showing that there is a shared table lock on the employee table that is stopping the insert from happening.
I am dissapointed that I can't just look at the diagram caught by profiler and see this simple scenario (it ususally suffices), but on the other hand I suppose it is good to understand the underlying trace output better.
Thanks for all your input.
March 30, 2011 at 10:13 am
This seems to be a sql profiler bug - if you paste the xml posted above into an .xdl file then view it, the diagram does not show the <objectlock> element on dbo.employee.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply