Sql-server – ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

configurationisolation-levelsql server

Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question.

I guess the word SNAPSHOT in both setting get a little confusing. I thought that, in order for database engine to use row versioning instead of locks for READ_COMMITTED default behavior, the database READ_COMMITTED_SNAPSHOT is set to ON regardless of what ALLOW_SNAPSHOT_ISOLATION setting.

The ALLOW_SNAPSHOT_ISOLATION setting is set to ON only to allow snapshot isolation when starting a transaction (e.g. SET TRANSACTION ISOLATION LEVEL SNAPSHOT) regardless of READ_COMMITTED_SNAPSHOT setting.

The only reason to have these two settings set to ON is when it needs to have READ COMMITTED row versioning AND snapshot isolation.

My question is, is my understanding incorrect in some way? And that these two setting have to be always set to ON together (especially for READ COMMITTED row versioning)?

Best Answer

Your understanding is correct. It does get a little confusing.

Kim Tripp (one of the programmers of SQL Server and a integral part of SQLSkills) goes through exactly what you stated in the MCM videos on Snapshot Isolation. Fast fwd to 41:45 in the video to get to the part where she answers your question.

If you use ALLOW_SNAPSHOT_ISOLATION make sure you use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your code, otherwise you will not get any of the benefits.

If you set SET READ_COMMITTED_SNAPSHOT ON, then there is no need to modify any code. MS SQL Server automatically applies snapshot isolation for that table.

I haven't tested to see what happens if you ask for a different isolation level in your code, I suspect it will overwrite this option but test it first.

A quick look at performance overhead using Snapshot Isolation.

Good article on how snapshot isolation can change the expected behavior of your app. It shows examples of how a update statement and a select statement might return totally different and unexpected results.

Related Question