x

Which row-versioning transaction isolation level do you use?

SQL Server 2005 and 2008 support 2 types of row-versioning transaction isolation levels.

  1. Snapshot Isolation
  2. Read Committed isolation with Row-Versioning

For information on the differences between the two, read this previous Q&A:
http://ask.sqlservercentral.com/questions/864/what-is-snapshot-isolation

If you have used one of these types, which one did you use and why did you choose that option over the other one?

I realize this is more of a forum question (no single right answer), but I think it will be informative and is a good extension to some of the other questions asked previously. Thanks.

more ▼

asked Nov 17, 2009 at 04:03 PM in Default

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

We're using READ COMMITTED SNAPSHOT because it provides just about everything we want without requiring our developers to make a single bit of change to their code. That's a pretty major win for us. Also, because it's a database setting as opposed to a connection setting we don't have to sweat the loss of remote transactions.

more ▼

answered Nov 17, 2009 at 05:19 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(comments are locked)
10|1200 characters needed characters left

We used read committed isolation for 2 reasons:

  1. We didn't have to change existing queries that already used read committed isolation.
  2. It is recommended by Microsoft for most situations.

Once we switched this option on, we saw a dramatic reduction in ASP.NET page timeouts that had been occurring due to blocking.

more ▼

answered Nov 17, 2009 at 04:07 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

In my last job we used SNAPSHOT, purely for the transactional consistency. We also saw a massive reduction in locks when moving from READ COMMITTED (without row versioning) to SNAPSHOT.

tempdb usage and bandwidth was not an issue for us, however, if it was, we would have considered READ COMMITTED with row versioning in order to reduce that load slightly.

more ▼

answered Nov 17, 2009 at 05:13 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

We used READ COMMITTED with row versioning enabled in our large billing system (with a couple of hundreds of millions of records) and successfully reduced the locks and increased the concurrency. It was a quick and easy way of solving the issues, without any code changes.

more ▼

answered Nov 18, 2009 at 04:49 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(comments are locked)
10|1200 characters needed characters left

Yep, read committed with row-level versioning. No application changes necessary and it works like a charm. I was concerned about the impact on tempdb but it hasn't got that big and doesn't seem to be under stress. (And that's on virtualised RAID 5 on a virtual machine - damn, I'm looking forward to our new hardware!)

more ▼

answered Nov 18, 2009 at 07:27 AM

David Wimbush gravatar image

David Wimbush
4.9k 28 30 34

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1950
x1842
x60
x13
x1

asked: Nov 17, 2009 at 04:03 PM

Seen: 3794 times

Last Updated: Nov 19, 2009 at 07:03 AM