Isolation Levels in SQL Server

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.

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 |