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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm having a database which is used heavily. A lot of writing/reading is done simultanosly. Therefore I've decided to use snapshots to reduce/avoid deadlocks.

I'v turned on READ_COMMITTED_SNAPSHOT, but do I need to specify what transaction level to use in each SP aswell?

Thanks a lot!

share|improve this question

migrated from Feb 12 '12 at 7:50

This question came from our site for professional and enthusiast programmers.

up vote 1 down vote accepted

Did you set this on the database-level? If so, then unless otherwise overridden in a query then no you don't need to specify READ_COMMITTED_SNAPSHOT again.

Setting transaction Isolation Level:


I'm guessing you did the above?

Also, I hope you understand what exactly this Transaction Isolation Level entails. It has to do with row versioning and is quite the hit on tempdb. It's not always the answer to heavy "writing/reading". Not to mention it utilizes row versioning, so you may not be getting what you think you should be getting.

share|improve this answer
Btw, do you know how long it should take to put it on? – johan Feb 8 '12 at 18:05
@johan Put what on? – Thomas Stringer Feb 8 '12 at 18:06
running the alter database script can only be done in single user mode. Otherwise the query never finnish – johan Feb 9 '12 at 7:58

Be careful: when we turn on READ_COMMITTED_SNAPSHOT, we can break existing code. I wrote a few examples here: When Snapshot Isolation Helps and When It Hurts

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.