Warning: Use of undefined constant user_level - assumed 'user_level' (this will throw an Error in a future version of PHP) in /var/www/lybecker.com/public_html/blog/wp-content/plugins/ultimate-google-analytics/ultimate_ga.php on line 524

Run the below query to see which databases that uses read committed snapshot isolation:

SELECT name, is_read_committed_snapshot_on FROM sys.databases

To enable it on a specific database run the below query:

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

As a developer I am in love with read committed snapshot isolation feature for SQL Server 2005+. Transactions can read and write simultaneously the same piece of data – no read/write contention due to row versioning. Writers will block other writers though.

Normal conflicting scenarios:

Simultaneously write and read of the same data.

• Read committed isolation:  The reader will wait for the writer to release the exclusive lock.
• Read committed snapshot isolation:  The reader will not block, but receive the latest committed data (latest row version).

Simultaneously writes of the same data.

• Read committed isolation: The second writer will wait until the first transaction completes because of an exclusive lock.
• Read committed snapshot isolation: Same as read committed isolation.

Read more about read committed snapshot isolation and isolation levels in the Isolation Levels in SQL Server 2005 article.

Update 02FEB2011: Amended the article due to Tom’s comments.