Post reply

How to reduce or stop deadlocks temporarily

  • August 29, 2019 at 5:04 pm

    #3675518

    Hi,

    Our application is facing with numerous deadlocks and dev team knows that they have to fix the code. But they are asking if there is way to reduce or stop deadlocks temporarily while they are working to fix the code. Please advise.

  • August 29, 2019 at 10:32 pm

    #3675568

    You could possibly try a row versioning isolation level but it can come with plenty of other issues. Read through the following posts which explains some of the issues and has some additional links that you really want to read before going down this route:

    What risks are there if we enable read committed snapshot in sql-server?

    Sue

  • September 3, 2019 at 3:55 pm

    #3676922

    Thanks Sue,

    Any more thoughts on this?

     

  • September 4, 2019 at 9:04 am

    #3677116

    No magic here. Version the rows, which creates a different resource load, or fix code.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • September 4, 2019 at 3:51 pm

    #3677275

    Be very aware that versioning the rows can cause a massive amount of page splits, which causes massive fragmentation (when you first apply it because a version column will be added to the table), which causes queries that read more than 1 page to slow down and can cause a huge amount of wasted memory.  Paul Randal over at SQLSkills has an article on it that I don't have the URL for handy.

    At the very least, you should rebuild all the indexes (especially the Clustered Index) on any tables you apply versioning to.

    Also be aware that when you use versioning to prevent deadlocking, you're also removing one of the primary reasons for developers to 1) write better new code and 2) fix existing code.  You might also run into some unexpected problems because your also changing the optimism of updates.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

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