SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SQL Profiler to Resolve Deadlocks in SQL Server


Using SQL Profiler to Resolve Deadlocks in SQL Server

Author
Message
Shawn Melton
Shawn Melton
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2287 Visits: 3513
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
arr.nagaraj
arr.nagaraj
SSC Eights!
SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)SSC Eights! (862 reputation)

Group: General Forum Members
Points: 862 Visits: 1590
@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

http://Strictlysql.blogspot.com
kevin77
kevin77
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1823 Visits: 1099
Thanks for the additional clarification, Raj.



sameera33
sameera33
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 146
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
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2916 Visits: 1807
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
sameera33
sameera33
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 146
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
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2916 Visits: 1807
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
Dennis Post
Dennis Post
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search