Isolation Levels
Isolation levels come into play when you need to isolate a resource for a transaction and protect that resource from other transactions. The protection is done by obtaining locks. What locks need to be set and how it has to be established for the transaction is determined by SQL Server referring to the Isolation Level that has been set. Lower Isolation Levels allow multiple users to access the resource simultaneously (concurrency) but they may introduce concurrency related problems such as dirty-reads and data inaccuracy. Higher Isolation Levels eliminate concurrency related problems and increase the data accuracy but they may introduce blocking.
Note that first four Isolation Levels described below are ordered from lowest to highest. The two subsequent levels are new to SQL Server 2005, and are described separately. Read Uncommitted Isolation LevelThis is the lowest level and can be set, so that it provides higher concurrency but introduces all concurrency problems; dirty-reads, Lost updates, Nonrepeatable reads (Inconsistent analysis) and phantom reads. This Isolation Level can be simply tested.
Connection1 opens a transaction and starts updating Employees table.
USE Northwind
BEGIN TRAN
— update the HireDate from 5/1/1992 to 5/2/1992
UPDATE dbo.Employees
SET HireDate = ‘5/2/1992’
WHERE EmployeeID = 1
Connection2 tries to read same record.
USE Northwind
SELECT HireDate
FROM dbo.Employees
WHERE EmployeeID = 1
You will see that Connection2 cannot read data because an exclusive lock has been set for the resource by Connection1. The exclusive locks are not compatible with other locks. Though this reduces the concurrency, as you see, it eliminates the data inaccuracy by not allowing seeing uncommitted data for others. Now let’s set the Isolation Level of Connection2 to Read Uncommitted and see.
USE Northwind
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT HireDate
FROM dbo.Employees
WHERE EmployeeID = 1
— results HireDate as 5/2/1992
As you expected, Connection2 can see the record that is being modified by Connection1 which is an uncommitted record. This is called dirty-reading. You can expect higher level of concurrency by setting the Isolation Level to Read Uncommitted but you may face all concurrency related problems. Imagine the consequences when Connection1 rolls back the transaction but Connection2 makes a decision from the result before the roll back.
Read Committed Isolation Level
This is the default Isolation Level of SQL Server. This eliminates dirty-reads but all other concurrency related problems. You have already seen this. Look at the sample used above. Connection2 could not read data before the Isolation Level was set to Read Uncommitted. That is because it had been set to the default Isolation Level which is Read Committed which in turn disallowed reading uncommitted data. Though it stops dirty-reads, it may introduce others. Let’s take a simple example that shows Lost Updates.
Employee table contains data related to employee. New employee joins and record is made in the table.
USE Northwind
INSERT INTO dbo.Employees
(LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate)
VALUES
(‘Lewis’, ‘Jane’, ‘Sales Representative’, ‘Ms.’, ’03/04/1979′, ’06/23/2007′)
This table contains a column called Notes that describes the employee’s education background. Data entry operators fill this column by looking at her/his file. Assume that the update code has been written as below. Note that no Isolation Level has been set, means default is set.
IF OBJECT_ID(N’dbo.UpdateNotes’, N’P’) IS NOT NULL
BEGIN
DROP PROC dbo.UpdateNotes
END
GO
CREATE PROCEDURE dbo.UpdateNotes @EmployeeID int, @Notes ntext
AS
BEGIN
DECLARE @IsUpdated bit
BEGIN TRAN
SELECT @IsUpdated = CASE WHEN Notes IS NULL THEN 0 ELSE 1 END
FROM dbo.Employees
WHERE EmployeeID = @EmployeeID — new record
— The below statement added to hold the transaction for 5 seconds
— Consider it is as a different process that do something else.
WAITFOR DELAY ’00:00:5′
IF (@IsUpdated = 0)
BEGIN
UPDATE dbo.Employees
SET Notes = @Notes
WHERE EmployeeID = @EmployeeID
END
ELSE
BEGIN
ROLLBACK TRAN
RAISERROR (‘Note has been alreasy updated!’, 16, 1)
RETURN
END
COMMIT TRAN
END
Operator1 makes Connection1 and executes the following query.
EXEC dbo.UpdateNotes 15, ‘Jane has a BA degree in English from the University of Washington.’
Within few seconds (in this case, right after Operator1 started) Operator2 makes Connection2 and executes the same with a different note, before completing the Operator1’s process.
EXEC dbo.UpdateNotes 15, ‘Jane holds a BA degree in English.’
If you query the record after both processes, you will see that note that was entered by the Operator2 has been set for the record. Operator1 made the update and no error messages were returned to it, but it has lost its update. This could be avoided if the record was locked and held as soon as it was identified as a not updated record. But obtaining and holding a lock is not possible with Read Committed Isolation Level. Because of this, concurrency related problems such as Lost Updates, Nonrepeatable reads and Phantom reads can happen with this Isolation Level.