Read Uncommitted Isolation Level

and in last blog we saw the behavior of  default isolations (Read Committed) for each read phenomenon.

Read Uncommitted Isolation level : This is the Lowest restrictive isolation level and as the name Suggests, The transactions running under Read Uncommitted Isolation Level does not prevent reading of data that has been modified by other transaction which are not committed.

In my blog about Read Phenomenon against each isolation level .

We saw Dirty, Repeatable & Phantom Reads occurrence. And these phenomenon exists in Read Uncommitted Isolation.

---Script To Create Test Table

If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel 
Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go

Id	Name	EncodedDate
1	Pankaj	2011-07-28 20:50:53.247
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247

 

CASE -1

CASE – 1 ( Does READ UNCOMMITTED ISOLATION Prevents Dirty Read ?)

Query Window 1

Begin Transaction
    Update CheckIsolationLevel Set Name='MSSQLGUIDE' Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued
--Also Note That We Have Not Changed Isolation Level For Above Transaction So Update Is Taking Place Under Default Isolation Level (Read Committed).

Lets Open Another Connection

Query Window 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Select * From CheckIsolationLevel Where Id=1

Output :

Id	Name	EncodedDate
1	MSSQLGUIDE	2011-07-28 20:50:53.247

 

As you see above the pending update transaction at Query Window 1 doesn’t lock records since user at Query Window 2 can easily read those records (Which is Dirty Read).

Here Data can be inconsistent but certainly give performance over read since there is no locking and wait.

The Above Query At Query Window 2 Can be Written In Following Ways Also.

Syntax 1 : Using “NOLOCK” Hint

Select * From CheckIsolationLevel (NOLOCK)  Where Id=1

 

Syntax 2 : Using READUNCOMMITTED HINT

Select * From CheckIsolationLevel (READUNCOMMITTED)  Where Id=1

 

Conclusion of CASE-1 (“NO”, Read UnCommitted Isolatiton Does Not Prevent Dirty Reads.)

 

CASE –2

 

CASE – 2 (Does Read UnCommitted Isolation Prevents Non-Repeatable Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Begin Transaction

Select * From CheckIsolationLevel Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued

 

Let’s  Go To Query Window 2

Query Window 2

Begin Transaction

Update CheckIsolationLevel Set Name=’MSSQLGUIDE’ Where Id=1

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go Back to Query Window 1

Query Window 1

--Let’s Fire The Same Query Again

Select * From CheckIsolationLevel Where Id=1

--As Expected Data is Changed even executing identical query within a transaction ( This is forming a Non-Repeatable Read).

Conclusion Of CASE –2 ( “NO” , Read UnCommitted Isolation Doesn’t Prevent Non-Repeatable Read)

 

CASE – 3

CASE – 3 (Does Read UnCommitted Isolation Prevents Phantom Read ?)

Drop And Recreate Table and Data

Query Window 1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Begin Transaction

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Note the transaction is still pending since no commit and rollback has been issued and Following Is Your Result

 

Id	Name	EncodedDate
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247

 

Let Go To Next Query Window 2

Query Window 2

Begin Transaction

Insert Into CheckIsolationLevel 
Values(‘Sandeep’,GetDate()),(‘Naren’,Getdate())

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go To Query Window 1

Query Window 1

--Run the Same Query and See the Result

Select * From CheckIsolationLevel Where Id Between 2 and 5

--Following result is Retrieved

Id	Name	EncodedDate
2	Santosh	2011-07-28 20:50:53.247
3	Hitesh	2011-07-28 20:50:53.247
4	Sandeep	2011-07-28 21:17:25.820
5	Naren	2011-07-28 21:17:25.820

 

As You Can See The Above result with 2 New Rows which were not there in First Result (Causing Phantom Read)

Conclusion Of CASE – 3 (“NO”,Read UnCommitted Isolation Does Not Prevents Phantom Read)