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

Different Types of Isolation Levels – Microsoft SQL Server 2005 – Part 1

When I was preparing article of ROW level versioning, I have used two types of Isolation level in that Article. 1.) Read Committed Snapshot Isolation Level (RCSI) 2.) Snapshot Isolation level (SI). After finishing that article, I felt to write something about Isolation level available in Microsoft SQL Server 2005.

First of all, question might arise in your mind that what is isolation level and why should we use it? The answer is as simple as question is. I must say that isolation level is two sided blade, if you will not use it in proper manner, it will harm you. Let me put it in this way, if you understand the meaning of ISOLATE then you will know the purpose of that as well. When you wish to isolate the resource for transaction and protect that resource from other transactions. Simply understand that more isolation more locks in your data.

Actually we had four types of isolation in Microsoft SQL Server 2000 and we have those four isolation level in Microsoft SQL Server 2005 as well along with two new isolation level.

All four ISOLATION level given below were available in both MS-SQL 2000 and MS-SQL 2005. List displayed four isolation levels from lower to highest level.

Read UnCommitted Isolation Level
Read Committed Isolation Level
Repeatable Read Isolation Level
Serializable Isolation Level

Apart from above four isolation levels, there is two more isolation level in Ms-SQL Server 2005.

Read Committed Snapshot Isolation Level
Snapshot Isolation Level

Since, this is big topic; I want to cover isolation in parts. This first part is just an introduction of Isolation. Second part will contain detail about four types of isolation level which was available in SQL Server 2000 and 2005 both and finally third part will contain last two types of isolation levels which are introduced in MS SQL Server 2005 only.

Reference: Ritesh Shah