How to reduce or stop deadlocks temporarily

Question

How to reduce or stop deadlocks temporarily

gana79950

Old Hand

Points: 371
More actions
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.

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

gana79950 Old Hand Points: 371 More actions · Answer 2

gana79950

Old Hand

Points: 371

September 3, 2019 at 3:55 pm

#3676922

Thanks Sue,

Any more thoughts on this?

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

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)