Archive for the ‘Isolation Level’ Category

Why should I use the SNAPSHOT isolation level? Advantage and disadvantage of Statement-Level Snapshot Isolation

December 12, 2011 Leave a comment

Why should I use the SNAPSHOT isolation level? Advantage and disadvantage of Statement-Level Snapshot Isolation


SQL Server 2005 introduced a new “snapshot” isolation level that is intended to enhance concurrency for online transaction processing (OLTP) applications.SNAPSHOT isolation level does not apply a lock while reading rows. SNAPSHOT  isolation level. As a true isolation level, SNAPSHOT is used within transactions and set at the session level with the SET TRANSACTION ISOLATION statement. During the course of a transaction, any changes to data that were read during the transaction are versioned: the original unchanged copy is stored in the versioning system in tempdb. During the remainder of the transaction, any SELECT statements read changed data from the version store and unchanged data directly from the database tables.

The great benefit of the new SNAPSHOT isolation level is that transactions no longer block each other because of SELECT statements. Transactions may still undergo blocking because of data change commands such as INSERT, UPDATE, and DELETE, because those commands still obtain locks. With SNAPSHOT isolation, you get the consistency of READ COMMITTED, and the concurrency of READ UNCOMMITTED. However, in order to use SNAPSHOT isolation, you must begin explicit transactions with the SET TRANSACTION ISOLATION LEVEL command. The SNAPSHOT isolation level has no application to SELECT statements outside explicit transactions.


Statement-Level Snapshot Isolation:
The READ_COMMITTED_SNAPSHOT option extends the benefits of SNAPSHOT isolation level to all SELECT statements in a database by changing the way the READ COMMITTED isolation level behaves. It is a database option, not a session level setting like the SNAPSHOT isolation level. However, you do have to enable it with ALTER DATABASE, as in the following command:

   ALTER DATABASE AdventureWorks



And that’s it: no other commands are required. (But note: there can be no other users in the database when you execute the ALTER DATABASE command with the READ_COMMITTED_SNAPSHOT option.) Automatically, the READ COMMITTED isolation level starts using row versioning, and SELECT statements no longer take shared locks.
Costs and Benefits of Statement-Level Snapshots
Like the SNAPSHOT isolation level, one of the major benefits of the database option READ_COMMITTED_SNAPSHOT is the elimination of blocking combined with reading only committed data.

A Key Benefit: Consistency
There is another subtle but arguably the most important benefit for the statement-level READ_COMMITTED_SNAPSHOT option: because the data returned from a SELECT statement is a consistent snapshot of the data as of the beginning of the statement, you are assured that the SELECT statement will return consistent aggregate values.

For example, suppose your SELECT statement sums up certain values from one or more tables. You are assured by READ_COMMITTED_SNAPSHOT that the summary values will be based on a snapshot of the data consistent at the starting time of the SELECT statement. Even though the underlying data may change during the execution of the SELECT statement, those changes are not read by the SELECT statement because only the version of the data current at the start time of the query is read.

Costs of Statement-Level Snapshots
Like the SNAPSHOT isolation level, the versioned rows for READ_COMMITTED_SNAPSHOT are kept in tempdb. Enabling this option database-wide implies that a considerable amount of activity in tempdb will be required to store and maintain those versions. This activity could potentially exceed that of the SNAPSHOT isolation level because the versioning is now applied database-wide. As a result, you must plan for increased space usage and increased I/O demands on the tempdb database. To ensure smooth running of a production system using this specific isolation level, the database administrator must allocate enough disk space for tempdb to ensure there is always approximately 10 percent free space. When free space falls below 10 percent, system throughput will degrade because the version cleanup process will spend more time trying to reclaim space in the version store. If I/O performance in tempdb becomes an issue, we recommend the database administrator create more than one file for tempdb on different disks to increase I/O bandwidth. In fact, on multiprocessor computers, increasing the number of files to match the number of processors can often yield even greater gains.

READ_COMMITTED_SNAPSHOT option can eliminate deadlocking without any change to your code. Because SELECT statements are no longer blocked by transactions that change data, deadlocks caused by the interaction of shared locks and exclusive locks are eliminated. Because the UPDATE statements apply to different rows, they do not conflict. As the SELECT statements do not require any shared locks, no blocking between the transactions occurs, and the deadlock does not occur.

For more information about how to set the isolation level within an application, see Adjusting Transaction Isolation Levels.


Limitations of Transactions Using Row Versioning-based 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.


  • 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.
  • 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 AdventureWorks2008R2;



    ON HumanResources.Employee REBUILD;


Any snapshot transaction that is active when the ALTER INDEX statement is executed receives an error if it attempts to reference the HumanResources.Employee table after the ALTER INDEX statement is executed. Read-committed transactions using row versioning are not affected.