Transaction Isolation - Part 5 [Read UnCommitted]

The Read Uncommitted transaction Isolation level does not prevent read as well as the Write operation. This means that when a row is half updated and still in progress and read operation (Select statement) can still proceed there. We call this as Dirty Read or Uncommitted data. Look at the T-SQL statements below:

 

Query Window 1:

 

Set transaction Isolation level Read UnCommitted;

Begin Transaction

Update student set class = 7 where studid = 103;

commit;

 

Query Window 2:

 

Set transaction Isolation level Read UnCommitted;

Begin Transaction;

      Select * from student;

commit;

 

In both the SQL transaction above (One Select and One Update) we wet the transaction isolation level to UnCommitted using the Set transaction. This means we are requesting that we want to read data without any delays and we don't care about the dirty data. Now execute the statement till update in the query window 1, delaying the execution of commit (Do not execute that now). Now execute the all the statements in the Query window 2. What happens? No delay on retrieving the set of records right? And see, you got Hansika as 7^th STD girl. Even though the update in the query window 1 is not committed we are still seeing the updated result in the query window2. Now execute the commit statement in Query Window 1. We kept the Isolation level to uncommitted again before making the selection. Why because, the Read operation in Query window 2 otherwise defaults to Read Committed.