<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Praveen Voleti
»
A Deadlock Occurence and Resolution
33 posts, Page 2 of 4
««
1
2
3
4
»
»»
A Deadlock Occurence and Resolution
Rate Topic
Display Mode
Topic Options
Author
Message
Jonathan Kehayias
Jonathan Kehayias
Posted Tuesday, February 03, 2009 12:18 PM
SSCommitted
Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696,
Visits: 1,742
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
Post #649195
amiller-724327
amiller-724327
Posted Tuesday, February 03, 2009 12:47 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, January 24, 2013 1:58 PM
Points: 6,
Visits: 21
:) 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
Post #649228
rgranucc
rgranucc
Posted Tuesday, February 03, 2009 2:14 PM
Grasshopper
Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 23,
Visits: 32
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.....
Post #649341
rgranucc
rgranucc
Posted Tuesday, February 03, 2009 2:17 PM
Grasshopper
Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 23,
Visits: 32
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
Post #649344
DataDog
DataDog
Posted Tuesday, February 03, 2009 2:18 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:07 AM
Points: 146,
Visits: 421
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!
Post #649347
Jonathan Kehayias
Jonathan Kehayias
Posted Tuesday, February 03, 2009 4:15 PM
SSCommitted
Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696,
Visits: 1,742
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
Post #649432
Jonathan Kehayias
Jonathan Kehayias
Posted Tuesday, February 03, 2009 4:18 PM
SSCommitted
Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696,
Visits: 1,742
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
Post #649434
mordik
mordik
Posted Tuesday, February 03, 2009 9:56 PM
Forum Newbie
Group: General Forum Members
Last Login: Saturday, July 18, 2009 8:39 PM
Points: 4,
Visits: 6
:D thanks for taking the time to put this together...very useful
Post #649524
bvandenbrink
bvandenbrink
Posted Wednesday, February 04, 2009 4:07 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, August 11, 2010 2:04 AM
Points: 1,
Visits: 16
"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.
Post #649649
blurpyblurp
blurpyblurp
Posted Wednesday, February 04, 2009 8:56 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, October 04, 2010 6:59 AM
Points: 113,
Visits: 119
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?
Post #649938
« Prev Topic
|
Next Topic »
33 posts, Page 2 of 4
««
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
