Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

A Deadlock Occurence and Resolution Expand / Collapse
Author
Message
Posted Tuesday, February 03, 2009 12:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
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
Posted Tuesday, February 03, 2009 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 1:58 PM
Points: 8, 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
Posted Tuesday, February 03, 2009 2:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:22 PM
Points: 24, Visits: 56
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
Posted Tuesday, February 03, 2009 2:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:22 PM
Points: 24, Visits: 56
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
Posted Tuesday, February 03, 2009 2:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:02 AM
Points: 147, Visits: 426
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
Posted Tuesday, February 03, 2009 4:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
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
Posted Tuesday, February 03, 2009 4:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
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
Posted Tuesday, February 03, 2009 9:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, February 04, 2009 4:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, February 04, 2009 8:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 6:30 AM
Points: 154, Visits: 188
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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse