Different Types of Isolation Levels - Microsoft SQL Server 2005 – Part 2

In my previous article, I introduced different types of ISOLATION LEVEL available in Microsoft SQL Server 2005. Let me introduce you some details about following four isolations.

Read UnCommitted (no lock) Isolation Level

Read Committed Isolation Level

Repeatable Read Isolation Level

Serializable Isolation Level

All of the above isolation was available in SQL-Server 2000 and we have it in SQL-Server version 2005 as well.

Note: I will be using following table and its records in my series of Isolation Levels article.

--CREATING FIRST ORDER TABLE

USE [AdventureWorks]

GO

/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Orders](

[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[OrderDate] [datetime] NOT NULL,

[CompanyName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

[OrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

INSERT INTO Orders

SELECT 'A1000','03/13/2009','abc' UNION ALL

SELECT 'A1001','03/14/2009','xyz'

GO

Read Uncommitted Isolation Level: This isolation level also known as “No Lock” (a.k.a Lowest Level Isolation) as it allows data modified by other transactions can be read by the current transaction even before transaction gets committed. This isolation level does not issue shared locks. It does not prevent other transactions from modifying the data that is being modified by the current transaction. This may raise an issue of read data which is still not committed, once we read it and user may roll back it but we don’t know about that as we have already received record sets. This situation is often called as “Dirty Reads” along with dirty reads; this isolation also produces some concurrency problem like lost updates, nonrepeatable reads, phantom reads.

Let us see how we can practically see what could happen with Read Uncommitted Isolation Level.

Open two instances of SSMS. Let’s call first instance as I1 and second instance as I2.

In I1, run following update query without committing the transaction.

BEGIN TRAN a

-- update records withou commiting or roll back transaction

UPDATE Orders

SET OrderDate = '01/11/2009'

WHERE [OrderID] = 'a1000'

Now, try to run “Select” statement on Orders table in I2, you won’t get the results as Orders table is locked. Now, set Read Uncommitted Isolation level in I2 and run select statement.

use adventureworks

go

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

select * from Orders

You will get updated data but think, if user 1 from I1 has rolled back data and we have already taken our critical decision after seeing data we got before roll back, what would happen??? It may lead us to some critical disaster. So, beware of using this isolation level as it is lowest level and creates some concurrency problem.

Read Committed Isolation Level: Read Committed Isolation Level is the default isolation level in SQL-Server 2005. It keeps you free from Dirty Read problem like we had it in previous isolation level. You must have observed that when we had not set any isolation level, we had not get any results set in I2 as table was locked due to non-committed data. By making table lock, RCIL made you free from dirty reading but it has some concurrency issue and we may lose update some time.

Repeatable Read Isolation Level: Repeatable Read isolation level makes sure that the data is being read and update by current transaction couldn’t be update by any other transaction until the current transaction completes. Unlike, read committed isolation level, it doesn’t release shared lock once data is read but it do locked it till the transaction is completed. Because of this property, it prevents from the “Lost Update”.

Serializable Isolation Level: This isolation level is quite restrictive isolation level and it avoids all concurrency level issue. It applies HOLDLOCK on the table. It never allows data to be read or modified in the transaction which is not been committed by another transaction and at the other end; it will not let any other transaction to modified or read data which is being read or modified by the current transaction. Not only this but if new record comes in, it falls in the same lock.

All of the above isolation level was supported by MS-SQL Server 2000 and also comes with MS-SQL Server 2005. In my next article I will give detail introduction of two new isolation level comes with MS-SQL Server 2005. 1.) Snapshot Isolation Level 2.) Read Committed Snapshot Isolation Level.

Reference: Ritesh Shah