patternsqlMajor
ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT
Viewed 0 times
allow_snapshot_isolationandread_committed_snapshot
Problem
Most of the forum and example online always suggest to have both
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
The
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)?
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)?
Solution
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 (direct MP4 download link (544MB)). Fast forward to 41:45 in the video to get to the part where she answers your question.
If you use
If you set
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.
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 (direct MP4 download link (544MB)). Fast forward 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.
Context
StackExchange Database Administrators Q#40738, answer score: 32
Revisions (0)
No revisions yet.