Isolation Levels in SQL Server

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 Level

This 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.

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

One Response to “Isolation Levels in SQL Server”

  1. As per your article,dirty reads can happen in “Read uncommitted” but not in “Read Committed Snapshot”.

    But after reading this article , if you see the example given in “Read Committed Snapshot”.
    begin tran — in connection 1
    UPDATE dbo.Employees
    SET HireDate = ’5/2/1992?
    WHERE EmployeeID = 1

    In connection 1 hiredate will be changed to ’5/2/1992?but in connection 2 when I select from the table it still shows the old hiredate.Is this not the same as Read Uncommitted? then why in the above table, it is mentioned that all the concurrency related problems except dirty reads can happen in “Read Committed Snapshot”.Please clarify this.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |