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 12»»

Locking, Blocking and Deadlocking Expand / Collapse
Author
Message
Posted Monday, November 18, 2013 12:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
Comments posted to this topic are about the item Locking, Blocking and Deadlocking

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1515093
Posted Monday, November 18, 2013 3:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:33 PM
Points: 8, Visits: 204
Thanks Wayne. Good introductory article.
Post #1515148
Posted Monday, November 18, 2013 5:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 67, Visits: 117
Good article for understanding deadlocks, I have some doubts regarding deadlock and blocking ,from your article I can understand that deadlock have circular dependency. But in our production server we have a lot of blocks not particularly deadlocks. Recently we had a situation when index reorganization and rebuild job kicked in the same time and caused a lot of blocks.

so my question is how will you resolve blocking in sql server ,when I queried sys.process to get a glimpse of the blocking It returned around 50 rows.
So how will I find the [color=#FF0000]root blocker, whether is it a chained blocking[/color] etc...

Any good articles or a layman's explanation is expected.

Thank you
Post #1515201
Posted Monday, November 18, 2013 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
gand 29575 (11/18/2013)
Thanks Wayne. Good introductory article.

Thanks gand. I enjoyed putting it all together, and I hope many will get some use out of it.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1515291
Posted Monday, November 18, 2013 9:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
desperadomar (11/18/2013)
So how will I find the root blocker, whether is it a chained blocking etc...
Thank you


I like to use sp_who2 and/or Adam Machanic's WhoIsActive to determine the root blocker.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1515292
Posted Monday, November 18, 2013 5:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 3,618, Visits: 5,254
A very concise and clear article Wayne.

I've read a number of others on this topic and none came across quite as clear.

Too bad I didn't have this a couple of years ago when I was trying to explain "locking is your friend" to someone. He just didn't get it. He still might not have even after reading your article, but it might have given me hope.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1515385
Posted Monday, November 18, 2013 7:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 67, Visits: 117
Thanks for the info ,I will give it a try to sp_whoisactive, but when you use sp_who2 I can only see some spid's in the
blocked by
column so all that spids in that column are head blockers?
Post #1515411
Posted Tuesday, November 19, 2013 3:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
desperadomar (11/18/2013)
Thanks for the info ,I will give it a try to sp_whoisactive, but when you use sp_who2 I can only see some spid's in the
blocked by
column so all that spids in that column are head blockers?


Ummm.... No.
The blocked by column is just that... the spid that is blocking this spid. Use a recursive CTE to keep going up this chain, until you find a blocked by that is empty. That one is the head blocker (for this chain of blocking).


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1515824
Posted Tuesday, November 19, 2013 3:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
Thanks Dwain.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1515826
Posted Wednesday, November 20, 2013 7:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:32 AM
Points: 43, Visits: 322
Hi. Great article. Thanks. Can i ask if changing the isolation level of the DB TO READ_COMMITTED_SNAPSHOT would have prevented the deadlock?
Post #1516068
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse