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