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.

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.

share|improve this question
add comment

3 Answers 3

up vote 20 down vote accepted

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:

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:

share|improve this answer
5  
The wikipedia page has changed to reflect the recent work by University of Sydney to make SI serializable, which has been incorporated into PostgreSQL 9.1. –  beldaz Oct 3 '12 at 3:37
    
@beldaz: thanks, good to know –  gbn Oct 3 '12 at 7:23
    
@gbn: Question on 1. Why do you say its an application problem? We have some massing apps that need to write to our logging table 4 example. Some can take over an hour to run. Lots of stuff read from the logging table which of course locks the readers out. If you want to keep everything in a one transaction how could you design your app around this? –  user127954 Sep 18 '13 at 13:55
add comment

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).

share|improve this answer
add comment

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.

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

Edit: Link requested by @gbn: Summary of Behaviour section in Understanding Row-Versioning based Isolation Levels

share|improve this answer
    
This matches something I've read and wanted to find: the snapshot modes "revert" to the pessimistic modes sometimes. Do you have a link please? –  gbn Aug 25 '11 at 11:49
    
Not sure what you mean @gbn. Are you looking for something on the difference between RCSI and Snapshot? –  Mark Storey-Smith Aug 25 '11 at 12:37
    
Some link or reference on the UPDATE lock thing sorry –  gbn Aug 25 '11 at 12:41
    
Gotcha, found a reference and added to answer. –  Mark Storey-Smith Aug 25 '11 at 12:47
    
ta. It was the bit "Update operations running under snapshot isolation internally execute under read committed isolation when the snapshot transaction accesses any of the following:" I'd seen elsewhere. I didn't even think about checking MSDN and BOL... –  gbn Aug 25 '11 at 12:49
show 2 more comments

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.