WEBINAR:
On-Demand
Building the Right Environment to Support AI, Machine Learning and Deep Learning
art I of this article (see "
Snapshot Isolation in SQL Server 2005," July/August, Volume 5, Issue 4) described the transaction-based
SNAPSHOT isolation level. At the time Part I was written, just prior to the release of Beta 2, only the
SNAPSHOT isolation level was fully documented. With the release of SQL Server 2005 Beta 2, a fuller explanation of a second type of
SNAPSHOT isolation behavior has come to light.
The
READ_COMMITTED_SNAPSHOT option is a supplement to, or an alternative way of using, the default isolation level of
READ COMMITTED. When a database has the
READ_COMMITTED_SNAPSHOT option set to
ON, and a
SELECT statement is issued, if any of the
SELECT's underlying data is changed during its execution, the
SELECT statement reads unchanged versions of changed rows from versioned data stored in the
tempdb database. The result is that the
SELECT statement returns a consistent snapshot of the data as of the starting time of the query. Because the
SELECT statements do not issue any shared locks on tables, they do not block other statements, nor are they blocked.
The alternative approach to row-versioned, or snapshot, data is called READ_COMMITTED_SNAPSHOT. It is a database-wide option, not an isolation level. Once the option is set, it provides a way for SELECT queries to read data that is consistent as of the time of the start of a single-statement query, no matter how long the query takes, and without causing any blocking.
| Author's Note: The information in this article is based on the Community Technology Preview of SQL Server 2005 Beta 2, which is build 9.00.951. Expect that some of the details about the operation and behavior of snapshot isolation will change in the final release of SQL Server 2005.) |
Two Forms of SNAPSHOT Isolation Behavior in SQL Server 2005
It's important to distinguish the
READ_COMMITTED_SNAPSHOT option from the
SNAPSHOT isolation level. As discussed in Part I of this article, the
SNAPSHOT isolation level is a new and distinct isolation level, on a par with the legacy SQL Server isolation levels (
READ UNCOMMITTED,
READ COMMITTED,
REPEATABLE READ, and
SERIALIZABLE). All the isolation levels other than the default
READ COMMITTED level are usually set at the session level in transactions, although locking hints can sometimes produce the same effect within a single query. SQL Server 2005 gives us a new isolation level,
SNAPSHOT, resulting in a total of five isolation levels available for transactions.
READ_COMMITTED_SNAPSHOT, on the other hand, is not a new transaction isolation level. Rather, READ_COMMITTED_SNAPSHOT is a database option that changes the behavior of the default READ COMMITTED isolation level. It is not set for a given session or transaction, but applies database-wide as soon as it is set.
