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 |
