5

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?

Vaccano
  • 2,550
  • 5
  • 31
  • 56

3 Answers3

5

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?

Remus Rusanu
  • 52,054
  • 4
  • 96
  • 172
3

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.

A-K
  • 7,444
  • 3
  • 35
  • 52
1

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.

Eric Higgins
  • 2,689
  • 1
  • 19
  • 25