LearnNowOnline

Blog

SQL Server 2012: Using Snapshot Isolation

Excerpt by Don Kiely | May 17, 2013

As discussed in our previous article, concurrency that is based solely on locking can cause blocking and deadlocking problems. However, reading data with READ UNCOMMITTED is not a solution for applications that require row-level accuracy when retrieving data; dirty data is generally not a good thing. To solve this problem, SQL Server includes a row versioning mechanism called snapshot isolation, which is intended to improve performance by avoiding the reader-writer blocking scenarios in the previous example.

Read operations do not request shared locks on the data, so transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level.

The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits transactions to execute without being blocked by a prior incomplete transaction.

NOTE: Two write operations do block each other even while running under row versioning-based isolation levels, because two write operations cannot modify the same data at the same time.

A unique transaction sequence number identifies each transaction. Whenever a transaction modifies a row, an image of the row before modification is copied into a page in tempdb. If multiple transactions modify a row, multiple versions of the row are linked in a version chain.

When a user or application retrieves data, it automatically gets the last saved version of each row.

Snapshot Isolation Caveats

Snapshot isolation is not recommended for every application. Here are several reasons why READ COMMITTED might be a better choice for your application:

• READ COMMITTED consumes less tempdb space than snapshot isolation.
• Snapshot isolation does not work with distributed transactions that span servers.
• Snapshot isolation is vulnerable to update conflicts that do not apply to READ COMMITTED. When a transaction that's running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back. This is not an issue with READ COMMITTED.

Enabling Snapshot Isolation

You enable snapshot isolation per database by turning on the ALLOW_SNAPSHOT_ISOLATION database option.

ALTER DATABASE Northwin

SET ALLOW_SNAPSHOT_ISOLATION ON;

This allows snapshot isolation when it is explicitly invoked, but the default READ COMMITTED transaction isolation level remains in effect for implicit transactions that do not specify snapshot isolation.

To replace READ COMMITTED entirely, turn on the READ_COMMITTED_SNAPSHOT option in addition to the ALLOW_SNAPSHOT_ISOLATION option. Snapshot isolation will then be used for all transactions.

ALTER DATABASE Northwind

SET READ_COMMITTED_SNAPSHOT ON;

This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely