Thursday, March 28, 2019

Read Dirty Records in SQL Server

Sometimes we need to read Dirty / Uncommitted records to confirm the changes made by other users.

To read uncommitted records we have SET the Isolation level to read uncommitted. check below example.

Formate to write the SQL
set transaction isolation level read uncommitted

select * from dbo.code
where  id ='123'

look at the "isolation level read uncommitted" part, which makes you SQL to read dirty records/data.

Now go for a full example:

Let there are two users,
USER-1 will make the changes and rollback at the end
USER-2 will read the uncommitted data from other sessions

Before start making changes, data looks like 
ID
Name
123
Saiful

USER-1: started the transaction...
begin transaction
  
  update dbo.code
   set name = 'azam'
  where id='123'

USER-2: Read this Dirty/ Uncommitted/ Unrollbacked record
set transaction isolation level read uncommitted

select * from dbo.code
where  id ='123'

S/he will get below data BUT the transaction has not been committed OR Rollbacked

USER-1: now rollback this transaction 
rollback

USER-2: if s/he runs the SQL then s/he will get the actual data!
select * from dbo.code
where  id ='123'
will get below data

ID
Name
123
Saiful

No comments:

Post a Comment