Read committed Isolation in SQL Server
is the default isolation level and means selects will only return committed
data. This prevents dirty reads. Data can be changed by other transactions
between individual statements within the current transaction, resulting in no
repeatable reads or phantom data.
UPDATE Employee SET CreatedDate = getdate()
having some intensive processing here with a wait
WAITFOR DELAY '00:00:05'
Isolation level is Read committed
SET TRANSACTION ISOLATION
LEVEL READ COMMITTED
SELECT * FROM Employee
query 2 display only committed data ,only committed data allowed to read
If you want to check what isolation level you
are running under you can run “DBCC user options”. Remember isolation levels
are Connection/Transaction specific so different queries on the same database
are often run under different isolation levels.