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.

So, I have some deadlocking issues.

I have seen two options to resolve it. Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot.

After doing some research into both, it seems to me that read uncommitted can allow reads of data that could possibly never get committed to the database.

On the flip side read committed snapshot will give only the latest (it is the latest right?) committed version of data (even if the data that may be in the middle of a change/transaction.)

Is this right?

If so, I am leaning towards Snapshot Isolation (I don't really want to return data that is in the middle of a change.)

However, my DBA told me that last he heard there were "problems" with Snapshot Isolation. (He did not expand on the the problems were, just that it was not a Enterprise ready feature.)

So, here are my questions on Snapshot Isolation:

Is Snapshot Isolation robust? Does it just work?

Or are there "gotchas" that I need to look out for?

share|improve this question
add comment

3 Answers 3

up vote 4 down vote accepted

On the flip side read committed snapshot will give only the latest committed version of data

Incorrect. Snapshot will give you the data that was committed at the moment the snapshot was taken. This moment means either when you issued BEGIN TRAN if you use true SNAPSHOT isolation level, or the moment your statement started if you use read_committed_snapshot RCSI. That means that if multiple updates commit, on the same record, you will not see the latest, but instead the one that was committed when your 'took the snapshot'. Think of it a a photo: you take a picture of the data and from then on you can only see the picture, no matter how the 'real' scenery changes behind.

Snapshot is absolutely robust. If your DBA has objections, then he/she should be able to articulate them so that they can be addresses or dismissed, as is the case. Everything has trade offs and gotchas. Indexes have trade offs and gotchas, does that mean your DBA does not use indexes?

share|improve this answer
    
Turns out my DBA's objections are performance based. He argues that snapshot isolation has to save to the cache, then to Temp DB. A "normal" write would just save to the cache and that is it. He says that a write in snapshot isolation would take a performance hit. (It would be nice if that is not true... but the data has to get to tempdb somehow, and I am guessing he is right. –  Vaccano Apr 3 '12 at 14:38
    
See Row Versioning Resource Usage. I've seen snapshot deployed in countless occasions and the impact was either absorbed easily by existing hardware or mitigated with ease (eg. tempdb on SSD). Have you measured the impact with your workload? Remember that INSERTs (the most common form of a write) do not need to write into the versionstore... –  Remus Rusanu Apr 3 '12 at 15:07
    
Thank you for the response. We are not allowed to use SSDs at my work (Our Sys Admin Group's policy is like this: "If it is important it goes on the SAN, if it is not important why are we storing it?") I think you are right about trying to measure the impact. Though it is kind of hard to measure the impact in a non-production setting, I think we need to try.) –  Vaccano Apr 3 '12 at 15:14
add comment

Snapshot isolation is robust. It does work. I have been using it in my system for several years, and it does reduce the amount of deadlocks.

However, snapshot isolation adds more workload to your tempdb, so you need a skilled DBA who can ensure that your system withstands the load. Before you start using snashot isolation in production, make sure you have a DBA who understands how it works.

share|improve this answer
add comment

When you are doing read committed snapshot, you are going to work your tempdb out since you are making use of the row versioning functionality (previous row state is kept available while other transactions are in process updating your row(s)). Read uncommitted is a different paradigm, and it shouldn't be thought of as being the same as read committed snapshot.

Start with Kendra Little's Iso levels poster. It's a fun way to start comparing ISO levels.

As for which one to select for your DBs, it's important to understand the differences in the concurrency models. There are tradeoffs. Ultimately though, with a properly configured tempdb & hardware that can handle the work, I agree w Kendra the read committed snapshot is "awesome" :)

Here's another link with a quick synopsis of concurrency effects.

share|improve this answer
add comment

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.