Enabling Row Versioning-Based Isolation Levels
Database administrators control the database-level settings for row versioning by using the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options in the ALTER DATABASE statement.
When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode.
The following Transact-SQL statement enables READ_COMMITTED_SNAPSHOT:
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON;
When the ALLOW_SNAPSHOT_ISOLATION database option is set ON, the instance of the Microsoft SQL Server Database Engine does not generate row versions for modified data until all active transactions that have modified data in the database complete. If there are active modification transactions, SQL Server sets the state of the option to PENDING_ON. After all of the modification transactions complete, the state of the option is changed to ON. Users cannot start a snapshot transaction in that database until the option is fully ON. The database passes through a PENDING_OFF state when the database administrator sets the ALLOW_SNAPSHOT_ISOLATION option to OFF.
The following Transact-SQL statement will enable ALLOW_SNAPSHOT_ISOLATION:
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
The following table lists and describes the states of the ALLOW_SNAPSHOT_ISOLATION option. Using ALTER DATABASE with the ALLOW_SNAPSHOT_ISOLATION option does not block users who are currently accessing the database data.
State of snapshot isolation framework for current database |
Description |
|---|---|
OFF |
The support for snapshot isolation transactions is not activated. No snapshot isolation transactions are allowed. |
PENDING_ON |
The support for snapshot isolation transactions is in transition state (from OFF to ON). Open transactions must complete. No snapshot isolation transactions are allowed. |
ON |
The support for snapshot isolation transactions is activated. Snapshot transactions are allowed. |
PENDING_OFF |
The support for snapshot isolation transactions is in transition state (from ON to OFF). Snapshot transactions started after this time cannot access this database. Update transactions still pay the cost of versioning in this database. Existing snapshot transactions can still access this database without a problem. The state PENDING_OFF does not become OFF until all snapshot transactions that were active when the database snapshot isolation state was ON finish. |
Use the sys.databases catalog view to determine the state of both row versioning database options.
All updates to user tables and some system tables stored in master and msdb generate row versions.
The ALLOW_SNAPSHOT_ISOLATION option is automatically set ON in the master and msdb databases, and cannot be disabled.
Users cannot set the READ_COMMITTED_SNAPSHOT option ON in master, tempdb, or msdb.