Locking, Blocking and Deadlocking

  • WayneS

    SSC Guru

    Points: 95384

    Comments posted to this topic are about the item Locking, Blocking and Deadlocking

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • gand 29575

    Mr or Mrs. 500

    Points: 547

    Thanks Wayne. Good introductory article.

  • desperadomar

    Mr or Mrs. 500

    Points: 519

    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 root blocker, whether is it a chained blocking etc...

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

    Thank you

  • WayneS

    SSC Guru

    Points: 95384

    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
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS

    SSC Guru

    Points: 95384

    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
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Dwain Camps

    SSC Guru

    Points: 86893

    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![/I]

    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?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • desperadomar

    Mr or Mrs. 500

    Points: 519

    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?

  • WayneS

    SSC Guru

    Points: 95384

    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
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS

    SSC Guru

    Points: 95384

    Thanks Dwain.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Adtjob

    SSC-Addicted

    Points: 476

    Hi. Great article. Thanks. Can i ask if changing the isolation level of the DB TO READ_COMMITTED_SNAPSHOT would have prevented the deadlock?

  • prakashr.r7

    SSCrazy

    Points: 2828

    Hi Wayne,

    It's really a great and quite interesting article. Very clear cut explanation of understanding the dead lock with examples. I really enjoyed and clearly understood what a dead lock is. I think no one will expect more explanation of dead lock as simple as this article does. Thanks a lot.

  • prakashr.r7

    SSCrazy

    Points: 2828

    Hi Wayne,

    It's really a great and quite interesting article. Very clear cut explanation of understanding the dead lock with examples. I really enjoyed and clearly understood what a dead lock is. I think no one will expect more explanation of dead lock as simple as this article does. Thanks a lot.

  • WayneS

    SSC Guru

    Points: 95384

    prakashr.r7 - Your comment means that I met my objective - Thanks!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • prakashr.r7

    SSCrazy

    Points: 2828

    yes Of course, Wayne. We (beginners) must say thanks not you. As a beginner in learning SQL, it was very helpful to me. And I have been scheduled for an interview in the first week of next month. This article would really help me if they ask (definitely I hope 🙂 ) me about dead lock.

    Thanks a lot, Wayne....will be Keep looking for your next article. 🙂

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply