Deadlock on a single resource ?

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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.

  • 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.

  • 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