«
»

How to check for and enable read committed snapshot isolation

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.

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: The first transaction committing will “win” and if the second tries to commit, it will abort and error will be raised.

In effect this is optimistic concurrency control without coding anything. Read more about read committed snapshot isolation and isolation levels in the Isolation Levels in SQL Server 2005 article.

 Anders Lybecker is an chief architect at Kring Development A/S, a consultancy firm in Copenhagen, Denmark. He holds a degree in software engineering specializing in software development. His primary expertises are the Microsoft .Net framework and SQL Server which he has been working with since the start of this century! He enjoys discussing technical topics, teaching and speaking at conferences.

Related posts:

  1. Disable and enable all database constraints
  2. New blog – NoTech
  3. Check for breaking changes in APIs

Tags: ,

This entry was posted on Wednesday, March 18th, 2009 at 12:51 pm and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “How to check for and enable read committed snapshot isolation”

  1. TomNo Gravatar says:
    May 14, 2009 at 4:48 pm

    I believe the scenario you’re describing for simultaneous writes on read committed snapshot is incorrect. It seems like you’re applying the behavior of snapshot isolation level to read committed snapshot. It’s nice that the names are so very similar–it helps confuse things.

    Anyway, the way I think of read committed snapshot is that writers don’t block readers, but writers still block writers. You’re saying one of the writers will throw an error. I just tried this scenario on a DB where I have is_read_committed_snapshot_on set to on, and the second update blocked until I committed, as I expected.

    I think you’re thinking of the conflict detection bit in snapshot isolation level. The article you reference says this: “Unlike Snapshot, it always returns latest consistence version and no conflict detection.”

Leave a Reply