SQL Server transaction isolation level

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. 

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 READUNCOMMITTED 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 READCOMMITTED 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 calledREPEATABLE 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 REPEATABLEREAD 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, theSERIALIZABLE 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 LEVELSERIALIZABLE  BEGIN TRAN SELECT COUNT(*) FROM Production.Product WHERESafetyStockLevel = 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 WHERESafetyStockLevel = 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 WHERESafetyStockLevel = 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.