A Deadlock Occurence and Resolution

  • praveen voleti

    SSC Veteran

    Points: 213

    Comments posted to this topic are about the item A Deadlock Occurence and Resolution

  • MarkusB

    SSC-Dedicated

    Points: 37369

    Very good article.

    [font="Verdana"]Markus Bohse[/font]

  • Jeremy Huppatz

    SSC-Addicted

    Points: 420

    Just a quick one - don't forget that SQL Server isn't the only source of deadlocks. I recently helped out on an issue where a single-threaded application DLL on one thread was blocked by a SQL Server lock, but at the same time, access to the application DLL from the locking process was also blocked. No deadlock victim, just timed-out queries and a lot of pain to the system's users.

    Classic deadlock... but only one of the locks was within SQL Server. 1204 is great, but troubleshooting SQL Server deadlocks is simple compared with tracking down the problem I was helping to diagnose.

  • Gerhard Schmeusser

    SSChasing Mays

    Points: 611

    Nice how-to article. But I wonder why Microsoft does not provide better support for deadlock situations. Hunting SQL server deadlocks is still like in the stone age days of programming ( in the above article even with non-documented features). Assumed SQL Server "knows" the reason, why not simply tell it?

  • bondistrict

    Ten Centuries

    Points: 1269

    😉 Nice article, thanks for sharing!

  • sureshot

    SSC Veteran

    Points: 261

    Great article on investigating the deadlock. Just a note that if you're running SQL 2005/2008, the trace flag 1222 (http://msdn.microsoft.com/en-us/library/ms178104.aspx) provides a lot more information so you don't have to do quite a much undocumented digging (though I found it very interesting).

    Instead of the trace flag, I'd personally recommend setting up WMI to log the deadlock info: http://technet.microsoft.com/en-us/library/ms186385(SQL.90).aspx.

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    sureshot (2/3/2009)


    Great article on investigating the deadlock. Just a note that if you're running SQL 2005/2008, the trace flag 1222 (http://msdn.microsoft.com/en-us/library/ms178104.aspx) provides a lot more information so you don't have to do quite a much undocumented digging (though I found it very interesting).

    Instead of the trace flag, I'd personally recommend setting up WMI to log the deadlock info: http://technet.microsoft.com/en-us/library/ms186385(SQL.90).aspx.

    The WMI alerts can actually be a bit more resource intensive and problemattic to setup. There are a number of more moving parts involved with the WMI alerts where as the Trace Flags or a SQL Trace has minimal configuration required. In the end, if you use Trace Flag 1222 in SQL Server 2005/2008, the deadlock graph is the same from WMI, SQL Trace, Profiler, and the Trace Flags.

    SQL 2008 offers Extended Events, watch for an upcoming article that I have already submitted which capture deadlock information natively while your server is running. Watch out though, the deadlock information changed to a new format in SQL 2008 that is only output by Extended Events and only Extended Events can capture and display a multi-victim deadlock, which is why there was a change made to the deadlock graph formatting in 2008 from Extended Events.

    As I mentioned yesterday, deadlocks aren't necessarily the problem. If your app is coded correctly it can handled the error raised and rerun the statement and it will most likely succeed on the second attempt.

    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]

  • Dallas Martin

    SSC Veteran

    Points: 231

    SQL 101 - Always make sure your tables have indexes to avoid full table scans.

    Looks as though the DBA and/or developers need a little coaching.

  • sureshot

    SSC Veteran

    Points: 261

    Very interesting news about SQL 2008 and Extended Events. I'm looking forward to when we can upgrade our production servers to that.

    For now the WMI deadlock system has provided a nice and easy to read history of deadlocks for us and we've hooked in an e-mail notification as well. CPU usage for WMI is roughly 2-3% on our box and we have capacity to spare so it's been worth it.

  • DataDog

    Hall of Fame

    Points: 3305

    SQL 2005 has xml deadlock report in profiler

    which will track deadlock down to tables and stored procedure statements

    I think your solution does not solve the deadlock - just makes it less likely

    If the db+app had been designed correctly the omission of the index would have reduced performance but not created deadlocks

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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.

    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]

  • amiller-724327

    SSC Rookie

    Points: 28

    🙂 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

  • SonomaRik

    Old Hand

    Points: 367

    totally agree: how hard would an interface using the simple steps outlined in the article be to generate a quick pull of that page.

    Would help in profiling and devo evoluton too.

    sigh.....

  • SonomaRik

    Old Hand

    Points: 367

    great response, except, what if the indexes exist? The article is valid as you say, for finding deadlocks in general and therefore has worth.

    r

  • DataDog

    Hall of Fame

    Points: 3305

    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!

Viewing 15 posts - 1 through 15 (of 33 total)

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