A Deadlock Occurence and Resolution

Question

Post reply

A Deadlock Occurence and Resolution

praveen voleti

SSC Veteran

Points: 213
More actions
February 3, 2009 at 1:12 am

#199709

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

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

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Very good article.

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

Jeremy Huppatz SSC-Addicted Points: 420 More actions · Answer 2

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: 621 More actions · Answer 3

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 More actions · Answer 4

bondistrict

Ten Centuries

Points: 1269

February 3, 2009 at 8:32 am

#937171

😉 Nice article, thanks for sharing!

sureshot Old Hand Points: 321 More actions · Answer 5

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: 26778 More actions · Answer 6

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.

Dallas Martin SSC Veteran Points: 231 More actions · Answer 7

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 Old Hand Points: 321 More actions · Answer 8

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 More actions · Answer 9

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: 26778 More actions · Answer 10

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.

amiller-724327 SSC Rookie Points: 28 More actions · Answer 11

🙂 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 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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!