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 «««12345

Using SQL Profiler to Resolve Deadlocks in SQL Server Expand / Collapse
Author
Message
Posted Monday, June 7, 2010 11:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 935, Visits: 2,683
I remember where I've seen mention of nHibernate now...Grant Fritchey (aka ScaryDBA) talks about it on his blog:
http://scarydba.wordpress.com/2010/04/05/nhibernate-first-look-at-tsql/

Which I believe his post is exactly what you maybe seeing.


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #933619
Posted Monday, June 7, 2010 9:30 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:19 AM
Points: 490, Visits: 1,345
@kevin77:

it seems to me that the database should be smart enough to analyze the SELECT query and recognize that it cannot acquire all the necessary data using the IX_Trip_startDate_userID index alone and should know and therefore acquire a lock on PK_Trip at the same time.

I think the solution of creating a covering index in this scenario isn't a very good one since you'd be duplicating all that data, particularly the 'descriptionText' field.

Does anyone have any suggestions or comments? Either on my example scenario or why the database isn't smart enough to acquire the locks that it needs all at the same time. I understand that in order to have a highly performant, highly concurrent system, you need to minimize the use of locks, but your article just surprised me on how seemingly simple it is to generate deadlock in SQL Server by doing something so common and what the database is supposed to be designed to handle, that is, concurrent INSERTs and SELECTs.


The query still takes row level locks. The scenario shown by Johnathon
applies when one attempts to insert and attempts to update the same row ( or group of rows ) and the timing for two conflicting operations is perfect up to some milliseconds. ie the update should occur exactly between query engine applies its locks at NC Index and before it obtains lock on clustered Index. This is very unlikely in reality especially if your indexes are correct. It takes a indefinite loop to simulate the example. So, I wont categorize it as a 'Simple/common' scenario.

Btw, at isolation level serializable, the same example it doesn't get into a deadlock as serialzable is lot more pessimistic when it comes to taking Range locks on indexes.


Regards,
Raj

Strictlysql.blogspot.com
Post #933770
Posted Tuesday, June 8, 2010 11:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 1,734, Visits: 1,072
Thanks for the additional clarification, Raj.


Post #934158
Posted Monday, December 20, 2010 1:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:35 AM
Points: 6, Visits: 123
Hi,
I captured a deadlock graph in an attempt to resolve a deadlocking issue, but I don't see the resources on the graph, I only see the two oval shapes representing the two SPIDS, with the victim having a big "X" on it. Why is that?
Thanks
Post #1037366
Posted Monday, December 20, 2010 1:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
Without the graph its really hard to say. Save the deadlock xml as a text file and attach it to your response and it will be easier to help you out.

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 #1037374
Posted Tuesday, December 21, 2010 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:35 AM
Points: 6, Visits: 123
Thanks Jonathan. I was able to see the resources after I saved the xdl file as a txt.
The deadlocks happen when executing an SSIS sequence of packages. What is strange is that the identical packages run fine on sql2005 server, but they frequently deadlock in sql2008. The deadlock most often invloves an alter index rebuild all statement and an update.
Are there differences between SQL 2005 and 2008 that could potentially cause the deadlocks?

Thanks
Sameera
Post #1037724
Posted Tuesday, December 21, 2010 8:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
Not that I know of, but its possible that there might be. Without the deadlock graph its hard to know what is occurring behind the scenes leading to the deadlock. I would look first at the two schema's and the list of indexes to see if there is a difference there. Is there a filtered index in 2008? Was an index added?

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 #1037747
Posted Tuesday, September 10, 2013 8:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, Visits: 547
Thanks Jonathan!!
This really made things a lot clearer for me.
Don't know if there are better techniques now days, but this one's a keeper!!




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1493232
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse