What risks are there if we enable read committed snapshot in sql-server?

Question

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there?

eg. Will this affect recovery of the database? Is there anything else we need to do to take advantage of this?

I plan to execute these commands:

ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON

I believe this will give us something closer to oracle where if one transaction is updating other transactions can still read the old data. Is this correct?

I am looking into this because I am sick of locking problems in SQL Server 2005. I am hoping this might reduce the the occasional deadlocks our users see, help overall performance of our application and encourage our developers to do more than one operation per transaction without fear.

Answer 1

Summary

1. If you have locking problems then you have a problem with your code: it isn't the database engine
2. It isn't a magic bullet
3. You may add more problems

Load

It will also increase load on your tempdb and CPU. Also see:

• "Performance Impact: The Potential Cost of Read_Committed_Snapshot" (Linchi Shea)

Safety

Most important, snapshot isolations are not safe in many cases by default. Read "Snapshot isolation" (Wikipedia) for more on write-skew anomalies. The next section is "Making Snapshot Isolation Serializable" to get around this.

In general, therefore, snapshot isolation puts some of the problem of maintaining non-trivial constraints onto the user, who may not appreciate either the potential pitfalls or the possible solutions. The upside to this transfer is better performance.

Also see:

• "The Potential Dangers of the Read Committed Snapshot Isolation Level" (JimMcLeod, disputed in comments by Alex Kuznetsov)
• Deadlocked!: "read committed snapshot" Explained (Nick Berardi)
• Serializable vs. Snapshot Isolation Level, the Marble problem (Craig Freedman)
• Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent (Alex Kuznetsov)

Answer 2

I know this is an old thread but I would say to a large degree snapshot isolation is a magic bullet. It will eliminate all of your blocking between readers and writers. It will however not prevent writers from blocking other writers. There is no way around that.

In my experience, the additional load on the TEMPDB is negligible and the benefits of row versioning in reducing blocked readers is huge.

For reference, row versioning (snapshot isolation) is the method Oracle has used for decades to achieve isolation without blocking readers and the Oracle DBs I've worked on for nearly 20 years experience far less blocking issues than SQL Server does. Most SQL developers are hesitant to use snapshot isolation though because they've only tested their code against databases that use the default setting.

Answer 3

Couple of additional points to add to the other answers:

SET ALLOW_SNAPSHOT_ISOLATION ON only enables snapshot isolation in a database. To take advantage of it you have to recode and SET TRANSACTION ISOLATION LEVEL SNAPSHOT for the transactions you want it to apply to. The calling code will need to be changed to handle update conflict errors.

After SET READ_COMMITTED_SNAPSHOT ON, statements at read committed use row-versioning. Note, this is statement level row-versioning for reads only. For updates, the "real" row is retrieved and update locks applied. See the Summary of Behaviour section in Understanding Row-Versioning based Isolation Levels

Either route, without exhaustive testing you're likely to introduce a completely new set of problems to the system.

Answer 4

I believe this will give us something closer to oracle where if one transaction is updating other transactions can still read the old data. Is this correct?

Yes, this is correct.

Well worth reading the links in gbn's answer and I believe the same applies to Oracle's default MVCC as to SQL Server in Snapshot Isolation mode. I would add that if you understand the potential pitfalls, IMO the benefits far outweigh the added difficulties (speaking from an Oracle perspective) - and of course some locking problems legitimately go away, that is the point of MVCC (there is also a class of locking problems that will not go away due to code issues, but I am assuming you understand this).

Answer 5

We are using SNAPSHOT ISOLATION in all of our projects that use SQL Server DB. No more 1205 SQL Errors, that are caused not because of wrong application code, but from default page locking and row locking behaviour.

Performance impact is minimal and so far 7 years have passed, hundreds of millions operations have been processed in different systems, with no problems regarding SNAPSHOT ISOLATION.

Situations where several different threads are updating business critical information in single row in parallel are extremely exceptional, and the chances that SNAPSHOT ISOLATION will be the cause of any inconsistency problem are very much near zero.

If you have an OLTP system, that by design updates single row based on current row data in many threads, of course SNAPSHOTS are not acceptable in such cases.

• influent: Same here, completely agree.