A Deadlock Occurence and Resolution

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    amiller (2/3/2009)


    🙂 Very interesting and with many information I didn't know. One more, does the ALLOW_PAGE_LOCKS option on indexes in the tables involved matters for deadlocks?

    thanks

    Albert

    Albert,

    YES. These options make a difference. See the following thread where having ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS OFF caused deadlocking to occur.

    http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/c5356da9-167e-4143-a64b-9a39dbc176b6/

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    doobya (2/3/2009)


    Jonathan Kehayias (2/3/2009)

    Take a look at the article from yesterdy. A missing/incorrect index can most definitely be the cause of a deadlock. In fact most deadlocks are simple index issues. Very rarely do I see a deadlock that doesn't involve cross locking indexes on a single table, though it does happen at times.

    That is an interesting article.

    I am surprised that the lock acquisition against a single table for a single statement isn't batched and serialized!

    I am not sure what you mean in this statement? The SH locks are required to ensure consistency during the lookup process across resources. You can certainly change issolation levels and affect they types of locks taken by the SELECT, but that isn't necessarily a solution to the problem depending on the issolation level that you decide to use.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • mordik

    SSC Veteran

    Points: 202

    😀 thanks for taking the time to put this together...very useful

  • bvandenbrink

    Newbie

    Points: 7

    "As there was only one non-clustered index existing on that table, I created a clustered index on the table on the ID column with this command"

    I understand not having an index on the ID column.

    Would creating a non-clustered index on this column haved solved the problem as well,

    or did you have a specific reason to chose for the clusterred one.

  • blurpyblurp

    Old Hand

    Points: 338

    Useful intro on identifying deadlocks.

    The part that concerned me more was:

    "As part of the upgrade, the application team performed modifications to the stored procedure code such that every insertion may cause up to 10 updates in the table."

    Why would so many updates need to happen on the same table from a single insert? The sprocs/functions used in between must be low level and small to have caused so many updates?

    If it's status updates, that's ALOT of status updates.

    I guess as long as the code works now and it's no deadlocking it's ok?

  • harry9katz

    SSC Enthusiast

    Points: 110

    U can find the deadlock on line by running the script:

    select * from master..sysprocesses where blocked>0 and spid<>blocked

    order by waittime desc

    and find the spid

    then use :

    dbcc inputbuffer (spid no)

  • Tony Webster

    SSChasing Mays

    Points: 610

    harry9katz (2/4/2009)


    U can find the deadlock on line by running the script:

    select * from master..sysprocesses where blocked>0 and spid<>blocked

    order by waittime desc

    and find the spid

    then use :

    dbcc inputbuffer (spid no)

    That isn't quite the same thing - that finds a blocked process. Put simply, a deadlock is when process 1 is locking resource A and wants resource B, and process 2 is locking B and wants A - obviously, if you leave them to it, they'll wait forever, so SQL Server has the lock monitor thread to detect this and pick one of the two processes to summarily execute, leaving the information in the log if you have the trace flag enabled.

  • harry9katz

    SSC Enthusiast

    Points: 110

    Right U are but U can see all the blocked paid's by order of waittime and can see what spid A is Doing by DBCC inputbuffur(spid a)

    and decide if to kill the process or not.

    this is a fast solution not clean as in the article

  • Tony Webster

    SSChasing Mays

    Points: 610

    harry9katz (2/6/2009)


    Right U are but U can see all the blocked paid's by order of waittime and can see what spid A is Doing by DBCC inputbuffur(spid a)

    and decide if to kill the process or not.

    But if it's a genuine deadlock, SQL server will have beaten you to it - more or less as soon as the circular dependency arises (i.e. 1 has A and wants B, and 2 has B and wants A - or perhaps a more complicated case) the lock monitor will choose the one with the least to lose (i.e. usually the one holding the least locks) and kills the transaction it is in, which rolls back and releases the locks, so the other can proceed. Even if you were aware that it had happened, nothing is left blocking anything - this is specifically what a deadlock is (sometimes called a "deadly embrace" in the two-process case particularly) - which is distinct from the case of one process just blocking another (for example if an application had opened a transaction and not closed it)

  • Amit W

    Ten Centuries

    Points: 1028

    Very nice step wise article.

    Thanks for sharing Praveen!

  • Rob-406527

    SSC Eights!

    Points: 907

    Very nice. Quick question, if there was only RID info (Node:1 RID: 8:1:231:0) in the deadlock entry could you still lookup the object info?

  • Ed Sanford

    SSC Journeyman

    Points: 83

    Found it to be an interesting article; but, am unlikely to have that kind of problem as we always have primary keys on our tables. 🙂

  • hunkwangshz

    Newbie

    Points: 7

    Brilliant article and great comments!

  • Mark Cowne

    One Orange Chip

    Points: 26688

    Jonathan Kehayias (2/3/2009)


    doobya (2/3/2009)


    SQL 2005 has xml deadlock report in profilerwhich will track deadlock down to tables and stored procedure statementsI think your solution does not solve the deadlock - just makes it less likelyIf the db+app had been designed correctly the omission of the index would have reduced performance but not created deadlocks

    Take a look at the article from yesterdy. A missing/incorrect index can most definitely be the cause of a deadlock. In fact most deadlocks are simple index issues. Very rarely do I see a deadlock that doesn't involve cross locking indexes on a single table, though it does happen at times.

    Here's a repo of one half of a deadlock scenario where a seemimgly innocent join caused an index scan.

    The solution I used was to use a temporary table instead.

    CREATE TABLE MyTable(PK INT NOT NULL PRIMARY KEY, Val INT)

    INSERT INTO MyTable(PK,Val)

    SELECT 1,10 UNION ALL SELECT 2,10

    --

    --

    DECLARE @x XML

    SET @x=''

    --

    --

    -- Joining directly to XML caused a clustered index scan on MyTable.PK

    UPDATE MyTable

    SET Val=r.value('@VAL','INT')

    FROM @x.nodes('/ROOT/ROW') AS x(r)

    WHERE MyTable.PK=r.value('@PK','INT')

    --

    --

    DECLARE @T TABLE(PK INT NOT NULL PRIMARY KEY, Val INT)

    INSERT INTO @T(PK,Val)

    SELECT r.value('@PK','INT'),

    r.value('@VAL','INT')

    FROM @x.nodes('/ROOT/ROW') AS x(r)

    --

    --

    -- Joining to a temporary table, this turns into a clustered index seek on MyTable.PK

    UPDATE MyTable

    SET Val=r.Val

    FROM @T r

    WHERE MyTable.PK=r.PK

    --

    --

    DROP TABLE MyTable

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Derek Jin

    Grasshopper

    Points: 11

    Beside creating proper indexes, developers also need to lock tables in the same order all the time to avoid deadlock situation.

    Derek

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply