Snapshot Isolation Adds Flexibility in SQL Server 2005 : Page 3

'Snapshot' is a new isolation level that reduces blocking reads and allows transactions to read previously committed versions of data, instead of uncommitted data. Picture your data access in a whole new light.

by Ron Talmage

Aug 2, 2004

Page 3 of 3

Degrees of Isolation
Essentially, the various isolation levels in SQL Server allow you to adjust the degree of isolation for any given transaction. Remember, changing the isolation levels in a transaction just affects the degree to which the data you read in SELECT statements is protected from changes by other transactions. Any actual data changes that you make are guaranteed, at any isolation level, to be unaffected by other transactions until you finish.

If you enable snapshot isolation on a database that has a high volume of transactions, you can expect that tempdb will require considerable storage to keep up with the required versions of the data in addition to its usual tasks. That means that your operations personnel will need to closely monitor tempdb for snapshot activity.

At the lowest isolation level, READ UNCOMMITTED, your read activity is completely open to any changes by other transactions, whether they have committed or not, and your SELECT statements can read dirty data. At the highest level, SERIALIZABLE, your transaction will never see any selected data change due to other transactions.

Higher transaction isolation levels come at a cost: the higher levels take more locks, and therefore increase the likelihood that your transaction may block others, and that your transaction will have to wait while other transactions release their locks. Isolation increases but concurrency decreases, because transactions become more incompatible with each other as more shared locks are added into the mix. That's the reason the default isolation level for SQL Server 2000 and 2005 is READ COMMITTED; it's high enough to make sure your transaction never sees dirty reads, but also low enough that your transaction will release its shared locks as soon as each SELECT statement is finished. So READ COMMITTED allows more concurrency than REPEATABLE READ and SERIALIZABLE, but ensures more consistency than READ UNCOMMITTED.

Overcoming Blocking in READ COMMITTED: Enter SNAPSHOT Isolation

Even in the READ COMMITTED isolation level, if another transaction is changing some data and therefore has exclusive locks on it, your transaction will have to wait until those exclusive locks are released before it can gain its brief shared locks and read the data. SQL Server can block the read activity of a SELECT statement in the READ COMMITTED isolation level.

But suppose your SELECT statement could just read an older version of the data, one that was consistent with when your transaction began, so that your transaction didn't have to wait, but also wouldn't be reading dirty data? That is what SNAPSHOT isolation will accomplish.

Snapshot Isolation Basics
SQL Server 2005 introduces a new type of isolation level that is termed "non-blocking READ COMMITTED" in Books Online. In other words, SNAPSHOT isolation behaves like READ COMMITTED in that your transactions will not ever see dirty reads, but on the other hand, they do not take shared locks that could block other transactions.

Enabling Snapshot Isolation

You must first enable snapshot isolation at the database level. By default, SQL Server 2005 databases will not be enabled for snapshot isolation, so you must issue an ALTER DATABASE command if you desire it. For example, the following code will enable snapshot isolation in the AdventureWorks sample database:

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON

With SQL Server 2005 Beta 2, the Master and MSDB databases are enabled for snapshot isolation by default. In addition, the sample databases AdventureWorks and AdventureWorksDW have also been enabled for snapshot isolation.

You can detect whether a database has been enabled for snapshot isolation in SQL Server 2005 by issuing a SELECT command against the new sys.databases system view:

SELECT name
, snapshot_isolation_framework
FROM sys.databases

(There is also a new SnapshotIsolationFramework property in the DATABASEPROPERTYEX() function documented, but it does not work in SQL Server 2005 IDW3, and may not be in later builds.)

Levels of Snapshot Isolation

Once you have enabled a database for snapshot isolation, you have the option of establishing it at one of two possible levels. The first is as a special case within the default READ COMMITTED isolation level. It was originally called 'statement-level' snapshot isolation in Beta 1, and has been renamed READ_COMMITTED_SNAPSHOT for Beta 2. The second is the transaction-oriented SNAPSHOT isolation level.

With READ_COMMITTED_SNAPSHOT (known as statement-level isolation in Beta 1), you establish snapshot isolation for all your transactions that occur within the default READ COMMITTED isolation level. When enabled, you do not need to specify the snapshot isolation level before starting a transaction. For Beta 2, you can set this using ALTER DATABASE, whereas with Yukon Beta 1, you have to use the trace flag 3970 when starting up SQL Server. Because READ_COMMITTED_SNAPSHOT, as of IDW3, is still a work in progress and not fully documented, in this article I'll focus exclusively on the snapshot isolation level.

Transaction-level snapshot isolation requires you to explicitly change the isolation level to SNAPSHOT for each transaction that you want to execute at the snapshot isolation level. The following code snippet shows how you can set the isolation level in a transaction to SNAPSHOT:

The advantage of statement-level snapshot isolation is that all transactions automatically support it, and you don't have to specifically set it before you begin a transaction. The disadvantage is that you have to set it using a trace flag. The real difference between the two levels is whether SNAPSHOT or READ COMMITTED is the default isolation level. In this article, we will assume that SQL Server's default isolation level is READ COMMITTTED, so whenever you see example transactions using the snapshot isolation level, you'll also see the explicit SET command just before the transaction begins.

How Snapshot Isolation Works

Just as soon as you enable snapshot isolation for a database, SQL Server 2005 stores copies of all database data changes in the tempdb database. This allows SQL Server 2005 to version the updates that occur in a database.

When a change occurs to a row, it is marked with a timestamp of the change and linked to a copy of the previously committed row in tempdb. Successive prior versions are stored in tempdb using a linked list, whereas the newest committed value is always stored in a page in the database where the change originated.

When a user in a snapshot isolation transaction attempts to read some data that has been changed, SQL Server 2005 retrieves the version of the row consistent with the time the users' transaction started. If the row has been changed since the transaction started, SQL Server will then traverse the linked list chain of row pointers in tempdb and retrieve the correct version of the data row.

Consider the code snippet of the prior section. Any change to the HumanResources.Employee row that occurs after your transaction has begun will cause the SELECT statement to read the older committed version of the then-current row.

The timestamps and links for versioned rows are maintained in an additional fourteen bytes that are added to each changed row in the database. The copied row stored in tempdb also contains an additional fourteen bytes for timestamp and linking to other row versions. If you set snapshot isolation off in the database, the space in tempdb is freed up, and as soon as the committed rows in the database that were versioned are updated again, SQL Server 2005 will free up the row's fourteen-byte snapshot overhead.

Snapshot Isolation and Blocking
Snapshot isolation can help reduce blocking by removing the shared locks that a transaction's SELECT statement normally takes, but without having to read uncommitted data. Table 1illustrates how two transactions running side by side will behave when the reading transaction is using the snapshot isolation level. (This example is based on an example in SQL Server 2005 Books Online, "Understanding Snapshot Isolation.")

Table 1: Two transactions running side by side illustrate how snapshot isolation can help reduce blocking.

Listing 1contains the code steps for User A, and Listing 2contains the code steps for User B. You can step along with the following analysis by loading each listing into its own SQL Server 2005 Management Studio (formerly known as SQL Workbench) window, and executing step by step.

To start, notice that at time T1 both users begin a transaction, though only User A sets the isolation level to SNAPSHOT. At time T2, User A SELECTs a value from the HumanResources.Employee table, and sees the current committed value of 39.6635. The key step now occurs at time T3 when User B changes the value to 40.0:

At time T4, User A re-issues the original SELECT, and still sees the currently committed value, not the new but as yet uncommitted value of 40.0.

There are a couple of important observations to make here. First, in the default READ COMMITTED isolation level, User A's second SELECT statement at time T4 would have been blocked. That's because the SELECT statement would have attempted a shared lock on the row being updated by User B, whereas User B has not yet released its exclusive lock on the row. Since shared locks are not compatible with exclusive locks, User A would simply have to wait until User B commits the transaction. However, since User A is running its transaction at the snapshot isolation level, no shared locks are requested and instead, the current committed version of the row is read from tempdb.

In the READ COMMITTED isolation level, if User A's SELECT statement at time T4 had used the NOLOCK hint, the statement would not have been blocked. But then User A would read dirty data. Similarly, if User A's SELECT statement had used the READPAST hint, the statement would not have been blocked, but the SELECT statement would not have read any data at all. Under READ COMMITTED, there is no way to read committed data without blocking; only the SNAPSHOT isolation level makes that possible.

Second, notice that the initial SELECT statement that User A makes at time T2 is not really necessary. A user transaction in the snapshot isolation level reads data that was committed as of the time the user's transaction began. The initial SELECT statement made no difference, as you can see for yourself if you vary the steps of the transactions accordingly.

The elegance of snapshot isolation consists in the fact that User A reads data that was correct, consistent, and committed at the time its transaction began. The underlying data did change during User A's transaction, but as long as the data remained uncommitted, it did not affect User A's transaction.

(Note that in the example, at time T6, after User B has committed its change to the row, User A still reads the older version of the data. This behavior may change in future builds. The original example in SQL Server 2005 Books Online suggests that User A should read the new value as soon as it is committed by User B. However, SQL Server 2005 IDW3 requires User A to commit the transaction before the newly committed value is read.)

Snapshot Isolation and Deadlocking
Once you've stepped through an example of how snapshot isolation can reduce blocking, it's easy to understand how snapshot isolation can reduce deadlocking. In a nutshell, deadlocking is just mutual blocking. When two transactions block each other, but neither can finish because each is requesting locks on the other's resources, neither can finish and SQL Server will detect the deadly embrace and cancel one of the queries. (For more information about deadlocking in SQL Server 2000, see "Resolving Deadlocks in SQL Server 2000," CoDe Magazine, Sep/Oct 2003.)

Table 2 shows two transactions operating side by side in a way that would cause a deadlock if they were both under the default READ COMMITTED isolation level. Both User A and User B finish their updates at times T2 and T3, respectively. In the default READ COMMITTED or higher isolation level, both transactions would be blocked when they attempt to read data that the other user is currently changing. The mutual blocking at time T5 would result in a deadlock, and SQL Server would choose a deadlock victim.

Table 2: Two transactions that would normally deadlock due to shared locks will not when in the snapshot isolation level.

Time

Tran 1

Tran 2

T1

SET TRANSACTION ISOLATION LEVEL SNAPSHOTBegin Tran

SET TRANSACTION ISOLATION LEVEL SNAPSHOTBegin Tran

T2

GRANT

Update Person.AddressSet AddressLine2 = 'None'Where AddressID = 1

T3

GRANT

Update Person.ContactSet middlename = 'J.'Where ContactID = 1

T4

Not Blocked

Select *From Person.ContactWhere ContactID = 1

T5

Not Blocked

Select *From Person.AddressWhere AddressID = 1

With snapshot isolation, though, each User's transaction now reads a prior committed version of the data, and no blocking occurs. Each transaction is free to continue. The upshot is that snapshot isolation can help reduce deadlocks that are caused by blocking reads.

Costs and Benefits of Snapshot Isolation
The major benefit of snapshot isolation is that you can now have consistent reads of data in a transaction that does not block other transactions and does not read uncommitted data. Instead, your transactions can read versions of data appropriate for when each transaction started.

Costs of Snapshot Isolation

The costs of snapshot isolation are significant and require some planning. Because snapshot isolation must store copies of all versioned data in the tempdb database, you can expect some processing overhead due to copying and maintaining versions and some I/O overhead due to storing and retrieving versions in tempdb.

Even more important is the amount of space that tempdb could use. If you enable snapshot isolation on a database that has a high volume of transactions, you can expect that tempdb will require considerable storage to keep up with the required versions of the data in addition to its usual tasks. That means that your operations personnel will need to closely monitor tempdb for snapshot activity.

Measuring Cost versus Benefit

When should you use snapshot isolation? Because its purpose is to eliminate blocking reads, the most natural use of snapshot isolation is in contexts where it is important that your transactions not read uncommitted data, but where reads of one transaction often tend to block or deadlock with other transactions. For this context, snapshot isolation is a real winner.

However, if your transactions hardly ever roll back, and/or reading uncommitted data does not cause problems in the application, the overhead of snapshot isolation may outweigh the benefit. In this type of context, you may find it better to just add NOLOCK or READPAST hints to sensitive queries to stop them from blocking.

In addition, there may be contexts where your transactions must be assured complete serializability, and in that context, snapshot isolation cannot replace the stronger REPEATABLE READ and SERIALIZABLE isolation levels.

Snapshot isolation is a new isolation level provided by SQL Server 2005. The snapshot isolation level is designed to help read operations no longer block, or be blocked by, write operations. Snapshot isolation fits in between the READ UNCOMMITTED and READ COMMITTED isolation levels. Like the READ UNCOMMITTED isolation level, SNAPSHOT does not take shared locks on data, and like READ COMMITTED, SNAPSHOT does not read uncommitted data.

Snapshot isolation can help reduce blocking and deadlocking due to blocking reads. If you have transactions that must read data that other transactions might be changing, and you do not want the transactions to read uncommitted data, then the new snapshot isolation level can fill that need. Because snapshot isolation stores versions of data in tempdb, however, it is important that you plan for increased temdb activity and increased space requirements if you want to use snapshot isolation.

Ron Talmage is a principal mentor with Solid Quality Learning, and also heads Prospice LLC, a database consulting firm based in Seattle. He is a SQL Server MVP, PASS newsletter co-editor, and current president of the Pacific Northwest SQL Server Users Group. He also writes for SQL Server Professional and SQL Server Magazine.