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
/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING ON
INSERT INTO Orders
SELECT ‘A1000’,’03/13/2009′,‘abc’ UNION ALL
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
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.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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