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.