Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Last few days I had major problems with my production site, turns out deadlock occurred, every half an hour maybe 50-60 of them. To do a quick fix in order to keep the site running I set : ALLOW_SNAPSHOT_ISOLATION ON

I believe my log file will grow faster than before now?

Are there different types of isolation and is it possible to set the isolation to just 2 tables which causes the deadlock?

::Update::

enter image description here

::2013-03-21 15:09:: ![xml graph image]http://i.imgur.com/KfXDf3k.png

Can I query the process ID's involved in the deadlock?select object_name() returns null always

share|improve this question
    
That snapshot isolation setting doesn't turn on snapshot isolation by default; it has to be specifically requested using SET TRANSACTION ISOLATION LEVEL SNAPSHOT... Please post the deadlock graph XML and then we can help you solve the root problem. –  Jon Seigel Mar 20 at 13:39
    
Monitor carefully your tempdb, as snapshot will use versionning which is stored in this system database. Have a careful read of brentozar.com/archive/2013/01/… if you didn't already. –  KookieMonster Mar 20 at 16:27
    
I couldn't get anything when setting sql profiler to filter "locks" so I enabled all sub sections ... Ina a whole list of Lock:Timeout events I found also Lock:Deadlock Chain...I will post the result in the question –  AArmin Mar 21 at 8:21
    
@JonSeigel Can I set the transaction level individually for certain tables ? –  AArmin Mar 21 at 8:36
    
No, the transaction isolation level is set in each session. Sorry, we need to see the XML behind the deadlock graph as it contains more information. –  Jon Seigel Mar 21 at 13:44

1 Answer 1

Enabling snapshot isolation on a production server without testing the application first is rather a high risk strategy, but if you seem to have got away with it, congratulations.

The side effects of snapshot isolation tend to be to do with tempdb rather than the transaction log, see for instance http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.110%29.aspx.

The best thing to do is to look at the code that is accessing those two tables, and see if you can correct the problem there.

share|improve this answer
    
I have a separate database and application for testing purposes and I checked on them first. Regarding the code, it's update with linq –  AArmin Mar 20 at 10:11
    
Doesn't seem like it did much difference. Site is still very very slow. No deadlocks are being logged now, what could it be this time? Database server RAM is at max (default) and CPU is 1% but site isn't responding. Same situation on web server regarding the CPU and RAM –  AArmin Mar 20 at 11:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.