Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

READ UNCOMMITTED

Read Uncommitted Isolation in SQL Server

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

        1.  Does not issue shared locks to prevent other transactions from modifying data read by the current transaction.
  2.  Also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set called as dirty reads.

 

This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

 

Protecting transactions from dirty reads:

 

a. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
b.  The SNAPSHOT isolation level. 


Example:

---Query1
BEGIN TRAN 
UPDATE Employee SET CreatedDate = getdate()
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:05' 
ROLLBACK  
 
---Query 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Employee 

 

 Result:

Actual data after transaction Completion by Query2:



Retrieved data by query 2 before Query 1 transaction completes (Dirty Read):


Employee table updated all records with CreatedDate,Actually query 1 will roll-back all changes after wait time completion. read uncommitted isolation level  allows user to read uncommitted (wrong) data updated by query 2


 

Invalid entry,please enter valid data.

Loading