Apply Now
|
Knowledge Base |
About Us |
Locations |
Contact Us
| About .NET |
| LINQ |
| WPF |
| About SQL Server 2005 |
| About Business Intelligence |
| Technical Articles |
| .NET Articles |
| SQL Server 2005 Articles |
| SharePoint 2007 Articles |
| Business Intelligence Articles |
| Online Resources |
 | |
| Live chat by LivePerson |
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 AdventureWorksGO 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 AdventureWorksGO 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 AdventureWorksGO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT Name from Production.Product WHERE ProductID = 316 -- returns 'Blade' |
|
USE AdventureWorksGO 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
|
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 AdventureWorksGO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT COUNT(*) FROM Production.Product WHERE SafetyStockLevel = 500 -- returns 167 |
|
USE AdventureWorksGO 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
|
|
|
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 AdventureWorksGO SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID = 316 -- was previously 'Blade', now 'Super Blade' |
|
|
USE AdventureWorksGO 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
Related Links
| Email this page | printer-friendly version |
|
Add the above content to: |
 Google  Y! MyWeb  Furl  Simpy  Spurl  Blink  Del.icio.us  Digg  Technorati
|
|