How to use READ_COMMITTED_SNAPSHOT

Question

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!

Answer 1

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.

Answer 2

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