Take Five with SQL Server 2005

New Snapshot Isolation Level
Kevin S. Goff, Microsoft MVP 2007

Application developers who work in an environment where users retrieve data while others save/update data are probably familiar with the challenges of concurrency and isolation levels. This month I'll cover the standard SQL Server isolation levels in SQL Server 2000, and also talk about the new SNAPSHOT ISOLATION LEVEL in SQL Server 2005.

In this article, I'll cover the following:

What are Isolation Levels in general?
An overview of the Isolation Levels in SQL Server prior to SQL Server 2005
The new Snapshot Isolation Level and how it improves on the existing Isolation Levels

1 - What are Isolation Levels?

Isolation levels in SQL Server are the specific mechanisms that allow transactions to run in isolation of each other, with rules for each specific level. Each specific isolation level carries rules: these rules include locking of rows and accepting (nor not accepting) inconsistent data across transactions. Each level takes and holds shared locks on data during SELECT read statements.

2 - Isolation Levels in SQL Server 2000

SQL Server 2000 provides four isolation levels. Each of the four isolation levels implements a progressively stricter set of rules.

READ UNCOMMITTED (Dirty read)
READ COMMITTED (SQL Server default)
REPEATABLE READ
SERIALIZABLE

READ UNCOMMITTED is the lowest and least restrictive isolation level. When using this level, I can query the value of a row/column that is in the middle of a transaction but not yet completed. For instance, take a look at the following tasks:

User 1	User 2
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID = 316 -- was previously 'Blade', now 'Super Blade'
	USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN SELECT Name from Production.Product WHERE ProductID = 316 -- WILL RETURN 'Super Blade', even before committing transaction,
COMMIT TRANSACTION

In both tasks, I set the transaction isolation level to READ UNCOMMITTED. In task 1, I update the value of a row from 'Blade' to 'Super Blade'. But before I commit the transaction, I query the table in another task, and I return the new updated value, even though I have not yet committed the transaction in task 1. This is because the isolation level allows me to read uncommitted data. This is why some refer to this isolation level as a "Dirty Read". You should only use this isolation level (which, in effect, provides no real isolation with other transactions) if you need to get the most immediate feedback possibly from a query, and when accuracy is not absolutely critical.

If I modify the code above to use the READ COMMITTED isolation level (which is the default SQL Server isolation level), the code for user 2 will result in a deadlock on the SELECT statement until the COMMIT TRANSACTION for User 1 executes. This is because the READ COMMITTED isolation level can only read COMMITTED data. Since the Transaction and UPDATE statement for User 1 perform an exclusive LOCK on the row, the SELECT statement for User 2 will be in a "deadlock" condition until the COMMIT occurs for User 1.

User 1	User 2
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID = 316 -- was previously 'Blade', now 'Super Blade'
	USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT Name from Production.Product WHERE ProductID = 316 -- Deadlock until the COMMIT finishes for User 1
COMMIT TRANSACTION
	-- Lock is released, and now will finally return 'Super Blade'

Many developers are satisfied with the behavior of the READ COMMITTED level. However, consider the following scenario: User 2 needs to query the same row twice in a stored procedure. In between the two queries, User 1 updates the specific row. User 2 will receive a different result the second time, because the READ COMMITTED level does not isolate User 2 from the update performed by User 1. This situation is referred to as a non-repeatable read. If you need for User 2 to read the same piece of data both times and return the same result, and not allow User 1's UPDATE to complete until User 2's queries complete, you can use the Isolation level called REPEATABLE READ.

User 1	User 2
	USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT Name from Production.Product WHERE ProductID = 316 -- returns 'Blade'
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID = 316 -- Deadlock, because User 2 still has the row
	SELECT Name fromProduction.Product WHERE ProductID = 316 -- Will still return 'Blade'
-- Still in deadlock until User 2 commits the transaction
	COMMIT TRANSACTION
-- UPDATE completes we can COMMIT COMMIT TRANSACTION

As you can probably imagine, the REPEATABLE READ isolation level holds locks on data until the transaction completes. This can affect operations that need to do multiple updates, which many impact performance. So this level should be used carefully.

The REPEATABLE READ level protects User 2 from any outside changes to data. However, suppose User 2 wanted to query on a range of products twice within a stored procedure, while User 1 inserted a new row (that matches the range) in between the two queries. This is a PHANTOM record, which User 2 may not wish to see if it changes the results from the first query to the second. This brings us to the last (and highest) isolation level, the SERIALIZABLE isolation level. A SERIALIZABLE isolation level will lock out any insertions that would match the range, until the other transaction is committed:

User 1	User 2
	USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT *COUNT() FROM Production.Product WHERE SafetyStockLevel = 500 -- returns 167**
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN INSERT INTO Production.Product (ProductName, ProductNumber, SafetyStockLevel....) VALUES ('111','TEST111',500....) -- Deadlock, because this would affect the rowcount for User 2
	SELECT *COUNT() FROM Production.Product WHERE SafetyStockLevel = 500 -- STILL returns 167**
-- Still in deadlock until User 2 commits the transaction
	COMMIT TRANSACTION
-- INSERT completes we can COMMIT COMMIT TRANSACTION
	SELECT *COUNT() FROM Production.Product WHERE SafetyStockLevel = 500 -- NOW returns 168**

As you can see from the code above, the SERIALIZABLE isolation level locks out any attempts to insert new data that matches the query of a transaction that would include the new insertion. It does so through the use of key-range locks. Once again, this level should be used carefully, as it also uses locks and resources.

3 - The new SNAPSHOT ISOLATION LEVEL in SQL Server 2005

SQL Server 2005 adds a fifth isolation level, called the SNAPSHOT Isolation Level. Books Online describes this new level as "non-blocking READ COMMITTED". The level prevents queries from reading "dirty data", but also allows you to query data without using shared locks. The level does this by taking a "snapshot" (thus the name of the isolation level) into the tempDB database, This allows a developer to query against a specific version of the data throughout the entire transaction, without needing to wait for any other locks to clear, and without reading from any "dirty data".

Before using this new isolation level, you must first configure the specific database, as follows:

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

After you configure the database, you can use the SNAPSHOT ISOLATION level as follows:

User 1	User 2
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID = 316 -- was previously 'Blade', now 'Super Blade'
	USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT Name from Production.Product WHERE ProductID = 316 -- Still 'Blade'
COMMIT TRANSACTION
	SELECT Name from Production.Product WHERE ProductID = 316 -- Still 'Blade' COMMIT TRANSACTION SELECT Name from Production.Product WHERE ProductID = 316 -- NOW it's 'Super Blade'

In the code above, User 2 retains the original value ('version') of 'Blade', until we commit the transaction BOTH for User 1 and then User 2.

Final Thoughts:

For every piece of information that an article covers, there are always multiple items that aren’t covered. Remember…GOOGLE is your friend. When you have time, Google some of the bolded keywords in this article - it will lead to more valuable information than you ever thought possible. For example, the SNAPSHOT isolation level actually carries two options: one called READ_COMMITTED_SNAPSHOT for statement-level snapshot isolation. The second option is transaction-oriented snapshot isolation level.

About the author:

Kevin S. Goff has been a Microsoft C# .NET MVP for the last three years, from 2005 through 2007. He is a regular columnist for CoDe Magazine, for the Baker’s Dozen Productivity Tips. He is also the author of Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. He is the principal consultant for Common Ground Solutions, and specializes in solutions in .NET, SQL Server, and Crystal Reports. His main site is www.commongroundsolutions.net, and his blog is www.TheBakersDozen.net.

Email this Author!

Related Links