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