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'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
add comment

migrated from stackoverflow.com Feb 12 '12 at 7:50

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

2 Answers

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:

ALTER DATABASE YourDb
SET READ_COMMITTED_SNAPSHOT ON

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
add comment

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
add comment

Your Answer

 
discard

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