June 14, 2011 at 1:27 pm
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?
June 14, 2011 at 1:54 pm
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
June 14, 2011 at 2:01 pm
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
June 14, 2011 at 2:14 pm
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.
June 14, 2011 at 3:17 pm
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
June 14, 2011 at 4:07 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2011 at 10:48 pm
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
Change is inevitable... Change for the better is not.
June 15, 2011 at 8:44 am
Thank you everyone. I will put deadlock graph soon.
June 15, 2011 at 12:36 pm
I attached 3 deadlock graphs generated today.
dl2 is the frequent deadlock graph in our production server.
June 15, 2011 at 12:40 pm
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
June 15, 2011 at 12:54 pm
see attached.
June 15, 2011 at 12:58 pm
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.
June 15, 2011 at 1:00 pm
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
June 15, 2011 at 1:03 pm
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 ;-).
June 15, 2011 at 1:21 pm
I am running a trace and post xml files shortly. Thank you.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply