Using Row Versioning-based Isolation Levels
The row versioning framework is always enabled in Microsoft SQL Server 2005, and is used by multiple features. Besides providing row versioning-based isolation levels, it is used to support modifications made in triggers and multiple active result sets (MARS) sessions, and to support data reads for ONLINE index operations.
Row versioning-based isolation levels are enabled at the database level. Any application accessing objects from enabled databases can run queries using the following isolation levels:
Read-committed that uses row versioning by setting the READ_COMMITTED_SNAPSHOT database option to ON as shown in the following code example:
ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON;
Snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option to ON as shown in the following code example:
ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN SELECT t1.col5, t2.col5 FROM Table1 as t1 INNER JOIN SecondDB.dbo.Table2 as t2 ON t1.col1 = t2.col2;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN SELECT t1.col5, t2.col5 FROM Table1 as t1 WITH (READCOMMITTED) INNER JOIN SecondDB.dbo.Table2 as t2 ON t1.col1 = t2.col2;
For more information about setting the isolation level within an application, see Adjusting Transaction Isolation Levels.
Consider the following limitations when working with row versioning-based isolation levels:
READ_COMMITTED_SNAPSHOT cannot be enabled in tempdb, msdb, or master.
Global temp tables are stored in tempdb. When accessing global temp tables inside a snapshot transaction, one of the following must happen:
Set the ALLOW_SNAPSHOT_ISOLATION database option ON in tempdb.
Use an isolation hint to change the isolation level for the statement.
- Set the ALLOW_SNAPSHOT_ISOLATION database option ON in tempdb.
Snapshot transactions fail when:
A database is made read-only after the snapshot transaction starts, but before the snapshot transaction accesses the database.
If accessing objects from multiple databases, a database state was changed in such a way that database recovery occurred after a snapshot transaction starts, but before the snapshot transaction accesses the database. For example: the database was set to OFFLINE and then to ONLINE, database autoclose and open, or database detach and attach.
- A database is made read-only after the snapshot transaction starts, but before the snapshot transaction accesses the database.
Distributed transactions, including queries in distributed partitioned databases, are not supported under snapshot isolation.
SQL Server does not keep multiple versions of system metadata. Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata. If a DDL statement modifies an object, any concurrent reference to the object under snapshot isolation causes the snapshot transaction to fail. Read-committed transactions do not have this limitation when the READ_COMMITTED_SNAPSHOT database option is ON.
For example, a database administrator executes the following ALTER INDEX statement.
USE AdventureWorks; GO ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD; GO
HumanResources.Employeetable after the ALTER INDEX statement is executed. Read-committed transactions using row versioning are not affected.
Note: BULK INSERT operations may cause changes to target table metadata (for example, when disabling constraint checks). When this happens, concurrent snapshot isolation transactions accessing bulk inserted tables fail.
ConceptsIsolation Levels in the Database Engine
Understanding Row Versioning-Based Isolation Levels
Choosing Row Versioning-based Isolation Levels
Enabling Row Versioning-Based Isolation Levels
Row Versioning Resource Usage
Other ResourcesALTER DATABASE (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)