Post reply

Locking, Blocking and Deadlocking

  • November 18, 2013 at 12:01 am

    #280214

    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

  • November 18, 2013 at 3:31 am

    #1667065

    Thanks Wayne. Good introductory article.

  • November 18, 2013 at 5:56 am

    #1667112

    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

  • November 18, 2013 at 9:47 am

    #1667190

    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

  • November 18, 2013 at 9:52 am

    #1667191

    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

  • November 18, 2013 at 5:04 pm

    #1667269

    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

  • November 18, 2013 at 7:35 pm

    #1667289

    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?

  • November 19, 2013 at 3:57 pm

    #1667619

    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

  • November 19, 2013 at 3:59 pm

    #1667620

    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

  • November 20, 2013 at 7:45 am

    #1667817

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

  • February 17, 2014 at 5:38 am

    #1689456

    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.

  • February 17, 2014 at 5:39 am

    #1689458

    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.

  • February 17, 2014 at 8:32 am

    #1689556

    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

  • February 18, 2014 at 3:00 am

    #1689784

    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 13 (of 13 total)

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