...

Anchor
_under _under

Understanding Snapshot Isolation Levels

SQL Server 2005 added two new isolation levels,  Read Committed Snapshot and Snapshot, to enhance concurrency for OLTP applications. These isolation levels determine what locks SQL Server takes when accessing data and, therefore, by extension they determine the level of concurrency and consistency that statements and transactions experience.  In earlier versions of SQL Server, concurrency was based solely on locking, which caused blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios. All of these isolation levels are described in the following Microsoft Development Network article:

...

Read Committed Snapshot is a modification to the Read Committed Isolation level that uses row versioning to read the previous value. Turn on Read Committed Snapshot, and a lot of your blocking will be a thing of the past. Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction. 

...

Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb). You must enable snapshot isolation in each database that uses it with the Transact-SQL ALTER DATABASE statement. In this respect, snapshot isolation differs from the traditional isolation levels of READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and READ UNCOMMITTED, which require no configuration.  

...

1. Log into SQL server management studio with an sa-level user account.
2. Open a new query.

3. Run the following query to enable the READ_COMMITTED_SNAPSHOT option:
   
   

   No Format
   nopanel true
   /** Run this statement to change the isolation level of the database. 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. **/ ALTER DATABASE Aptify SET READ_COMMITTED_SNAPSHOT ON;

4. Run the following query to enable the ALLOW_SNAPSHOT_ISOLATION option:
   
   

   No Format
   nopanel true
   /** Statement below can be used to turn on the snapshot isolation, so that transactions can set the snapshot isolation level. Per our analysis, there is no Aptify code that currently does this, therefore this is not required.**/ ALTER DATABASE Aptify SET ALLOW_SNAPSHOT_ISOLATION ON;

5. Run the following query to verify the values of these options:
   
   

   No Format
   nopanel true
   /** Query to determine snapshot states **/ SELECT Name , snapshot_isolation_state , snapshot_isolation_state_desc , is_read_committed_snapshot_on FROM sys.databases WHERE name = 'APTIFY';