Deadlocks Solution

Question

Post reply

Deadlocks Solution

ayemya

Hall of Fame

Points: 3495
More actions
June 14, 2011 at 1:27 pm

#252829

Hi we are constantly getting deadlocks on some part of database.Can we apply Snapshot Isolation level to prevent the deadlocks? If I turn on Snapshot isolation, will it break the code?
Is there any way to resolve the Deadlock Issue in DATABASE. Is Snapshot Isolation Level is the Good Way to minimize Deadlocks Occurrence?

Viewing 15 posts - 1 through 15 (of 21 total)

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

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 1

As a general rule i would advise against using such things with out a full and proper understanding of exactly why.

Have you been able to identify the code that is causing the deadlock ?

Have you ensured that all the queries are not locking to much data, ie you have a good indexing strategy on the tables involved. ?

Here's a good article on the subject http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

Clear Sky SQL
My Blog[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Thre is a good chance that setting the database to READ_COMMITTED_SNAPSHOT will resolve most or all deadlocks.

However, deadlocks will still be possible, and if you still get them you will need to look at just what resources are being deadlocked.

Detecting and Ending Deadlocks

http://msdn.microsoft.com/en-us/library/ms178104.aspx

Deadlock Troubleshooting

http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

Using Row Versioning-based Isolation Levels

http://msdn.microsoft.com/en-us/library/ms179599.aspx

ayemya Hall of Fame Points: 3495 More actions · Answer 3

We have application that is causing deadlocks. The developers can't do anything to prevent deadlocks. I don't have permission to see the code. I can only run a trace to see the deadlock graph. I am thinking what I can do to reduce deadlocks from Database side.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

mya (6/14/2011)
The developers can't do anything to prevent deadlocks.

Translation, they're too lazy to try...

I can only run a trace to see the deadlock graph. I am thinking what I can do to reduce deadlocks from Database side.

Post the deadlock graph.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

The thing is, it's likely to reduce deadlocks, but it's unlikely to eliminate them and it certainly won't prevent them in the future. Best way to avoid or fix deadlocks is to adjust the code (and possibly the structure) so that you don't get them in the first place.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

mya (6/14/2011)
We have application that is causing deadlocks. The developers can't do anything to prevent deadlocks. I don't have permission to see the code. I can only run a trace to see the deadlock graph. I am thinking what I can do to reduce deadlocks from Database side.

Stating the obvious, you cannot fix what you cannot see. Sure, there's lots of tricks (a good number already mentioned on this thead) to reduce them but the real problem is that someone wrote some code that causes deadlocks and that code needs to be fixed. The deadlock graph is certainly a start but if they want you to fix the deadlocks, they're going to have to let you see the code.

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

ayemya Hall of Fame Points: 3495 More actions · Answer 7

Thank you everyone. I will put deadlock graph soon.

ayemya Hall of Fame Points: 3495 More actions · Answer 8

I attached 3 deadlock graphs generated today.

dl2 is the frequent deadlock graph in our production server.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Please zip and post the deadlock graph files themselves.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ayemya Hall of Fame Points: 3495 More actions · Answer 10

ayemya

Hall of Fame

Points: 3495

June 15, 2011 at 12:54 pm

#1339236

see attached.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

mya (6/15/2011)
see attached.

I'm pretty sure that's not what she meant. There's a ton of stuff in the txt versions of loggings in sql server and I'd bet she needs access to that.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

No. Not the pictures of the deadlock graph. There's a hell of a lot of info that's lost in a picture.

The deadlock graphs are files. XML files. I want to see those, the xml, so that I can open then in SSMS and look at all the properties and tool tips that pictures don't have.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

GilaMonster (6/15/2011)
No. Not the pictures of the deadlock graph. There's a hell of a lot of info that's lost in a picture.
The deadlock graphs are files. XML files. I want to see those, the xml, so that I can open then in SSMS and look at all the properties and tool tips that pictures don't have.

Looks like I have your foresight gift today... how nice ;-).

ayemya Hall of Fame Points: 3495 More actions · Answer 14

I am running a trace and post xml files shortly. Thank you.