Category Archives: Understanding SQL Server Isolation Levels

Understanding SQL Server Isolation Levels


ISOLATION LEVELS in SQL Server :

  

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.

  

********************************************************************************************************

  

Repeatable Read Isolation Level:

 
This Isolation Level addresses all concurrency related problems except Phantom reads. Unlike Read Committed,
it does not release the shared lock once the record is read. It obtains the shared lock for reading and
keeps till the transaction is over. This stops other transactions accessing the resource, avoiding Lost
Updates and Nonrepeatable reads. Change the Isolation Level of the stored procedure we used for
read Committed sample.

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

        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

        BEGIN TRAN

                SELECT @IsUpdated = CASE WHEN Notes IS NULL THEN 0 ELSE 1 END

                FROM dbo.Employees

                WHERE EmployeeID = @EmployeeID — new record

  

  
Now make two connections and execute below queries just as you did with Read Committed sample.
Make sure you set the Note column value back to NULL before executing them.

With Connection1;

EXEC dbo.UpdateNotes 15, ‘Jane has a BA degree in English from the University of Washington.’

With Connection2;

EXEC dbo.UpdateNotes 15, ‘Jane holds a BA degree in English.’

  

Once you execute the code with Connection2, SQL Server will throw 1205 error and Connection2 will be a
deadlock victim. This is because, Connection1 obtain and hold the lock on the resource until the
transaction completes, stopping accessing the resource by others, avoiding Lost Updates.
Note that setting DEADLOCK_PRIORITY to HIGH, you can choose the deadlock victim.

Since the lock is held until the transaction completes, it avoids Nonrepeatable Reads too. See the code below.

___________________________________________________________________________________________

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

_________________________________________________________________________________

  

BEGIN TRAN

       

        SELECT Notes

        FROM dbo.Employees

        WHERE EmployeeID = 10

  

  

It reads a record from the Employees table. The set Isolation Level guarantees the same result for the query
anywhere in the transaction because it holds the lock without releasing, avoiding modification from others.
It guarantees consistency of the information and no Nonrepeatable reads.
Now let’s take another simple example. In this case, we add one new table called Allowances and one new
column to Employees table called IsBirthdayAllowanceGiven.

The code for changes are as below;
USE Northwind

GO

— table holds allowances

CREATE TABLE Allowances (EmployeeID int, MonthAndYear datetime, Allowance money)

GO

— additional column that tells whether the birthday allowance is given or not

ALTER TABLE dbo.Employees

        ADD IsBirthdayAllowanceGiven bit DEFAULT(0) NOT NULL

GO

  

  
Assume that company pays an additional allowance for employees whose birth date fall on current month.
The below stored procedure inserts allowances for employees whose birth date fall on current month and
update employees record. Note that WAITFOR DELAY has been added hold the transaction for few seconds in
order to see the problem related to it. And no Isolation Level has been set, default applies.

IF OBJECT_ID(N’dbo.AddBirthdayAllowance’, N’P’) IS NOT NULL

BEGIN

        DROP PROC dbo.AddBirthdayAllowance

END

GO

CREATE PROC dbo.AddBirthdayAllowance

AS

BEGIN

        BEGIN TRAN

                — inserts records to allowances table

                INSERT INTO Allowances

                        (EmployeeID, MonthAndYear, Allowance)

                SELECT EmployeeID, getdate(), 100.00

                FROM dbo.Employees

                WHERE IsBirthdayAllowanceGiven = 0

                        AND MONTH(BirthDate) = MONTH(getdate())

       

                — hold the transaction for 5 seconds

                — Consider this is as some other process that takes 5 seconds

                WAITFOR DELAY ’00:00:05′

       

                — update IsBirthdayAllowanceGiven column in Employees table

                UPDATE dbo.Employees

                        SET IsBirthdayAllowanceGiven = 1

                WHERE IsBirthdayAllowanceGiven = 0

                        AND MONTH(BirthDate) = MONTH(getdate())

       

        COMMIT TRAN

END

  

  
Before running any queries, make sure at least one employee’s birth date falls on current month.
Now open a new connection (let’s name it as Connection1) and run the stored procedure.
In my Northwind database, I have one record that stratifies the criteria; EmployeeId 6: Michael Suyama.

  

 

USE Northwind

GO

EXEC dbo.AddBirthdayAllowance

 

Immediately, open Connection2 and insert a new employee whose birth date falls into current month.
USE Northwind

GO

INSERT INTO dbo.Employees

        (LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate)

VALUES

        (‘Creg’, ‘Alan’, ‘Sales Representative’, ‘Ms.’, ’07/13/1980′, ’07/20/2007′)

 

Go back to Connection2. Once the transaction completed, query the Allowances table and see.
You will see a one record that is generated for Michael. Then open the Employees table and see
that how many records have been updated. It has updated two, not only Michael but Alan.
Note that no record has been inserted to the Allowances table for Alan. In this case, the new
record is considered as a Phantom record and read of the new record called as Phantom Read.
This cannot be avoided with default Isolation Level that is Read Committed.
Change the stored procedure and set the Isolation Level as Repeatable Read.

 

IF OBJECT_ID(N’dbo.AddBirthdayAllowance’, N’P’) IS NOT NULL

BEGIN

        DROP PROC dbo.AddBirthdayAllowance

END

GO

CREATE PROC dbo.AddBirthdayAllowance

AS

BEGIN
        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

        BEGIN TRAN

                — inserts records to allowances table

                INSERT INTO Allowances

                        (EmployeeID, MonthAndYear, Allowance)

                SELECT EmployeeID, getdate(), 100.00

                FROM dbo.Employees

                WHERE IsBirthdayAllowanceGiven = 0

                        AND MONTH(BirthDate) = MONTH(getdate())

 

 
Now bring the Employees table to original state.
UPDATE dbo.Employees

        SET IsBirthdayAllowanceGiven = 0

DELETE dbo.Employees

WHERE FirstName = ‘Alan’     

DELETE dbo.Allowances

 

 

Open two connections again and try the same. Check the result. Still the Phantom Reads problem exists.
In order to avoid this problem, you need to use highest Isolation Level that is Serializable.

 

********************************************************************************************************

Serializable Isolation Level:
This is the highest Isolation Level and it avoids all the concurrency related problems.
The behavior of this level is just like the Repeatable Read with one additional feature.
It obtains key range locks based on the filters that have been used. It locks not only current
records that stratify the filter but new records fall into same filter. Change the stored procedure
we used for above sample and set the Isolation Level as Serializable.

IF OBJECT_ID(N’dbo.AddBirthdayAllowance’, N’P’) IS NOT NULL

BEGIN

        DROP PROC dbo.AddBirthdayAllowance

END

GO

CREATE PROC dbo.AddBirthdayAllowance

AS

BEGIN

        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

        BEGIN TRAN
                — inserts records to allowances table

                INSERT INTO Allowances

                        (EmployeeID, MonthAndYear, Allowance)

                SELECT EmployeeID, getdate(), 100.00

                FROM dbo.Employees

                WHERE IsBirthdayAllowanceGiven = 0

                        AND MONTH(BirthDate) = MONTH(getdate())


Run the clean up code again to bring the Employees table to the original state.

Now test the stored procedure and INSERT statement with two connections.
You will notice that INSERT operation is blocked until Connection1 completes the transaction,
avoiding Phantom Reads.

Run the clean up code again and drop the new table Allowances and added column IsBirthdayAllowanceGiven
in the Employees table.
Whenever we set the Isolation Level to a transaction, SQL Server makes sure that the transaction
is not disturbed by other transactions. This is called concurrency control.
All the Isolation Levels we discussed so far come under a control called Pessimistic Control.
The Pessimistic control, SQL Server locks the resource until user performs the action she/he needs and
then release for others. The other concurrency control is Optimistic Control. Under Optimistic Control,
SQL Server does not hold locks but once read, check for inconsistency for next read.
The two newly introduced Isolation Levels with SQL Server 2005 are Snapshot and Read Committed Snapshot.
These two Isolation Levels provide Optimistic Control and they use Row Versioning.

********************************************************************************************************

Comparison of Isolation Levels

The isolation levels in the following table are arranged from the least degree of locking to the highest degree of locking. The default, ReadCommitted, is a good compromise for most transactions.

Isolation Level Dirty Read Non Repeatable Phantom Data Concurrency
ReadUncommitted Yes Yes Yes Best
ReadCommitted No Yes Yes Good
RepeatableRead No No Yes Poor
Serializable No No No Very Poor
Advertisements
%d bloggers like this: