Read Committed
With Read Committed, transactions issue exclusive locks at the time of data modification, thus not allowing other transactions to read the modified data that is not yet committed. The Read Committed isolation level prevents the Dirty Read issue.
Execute below scripts to create table and insert some records to use in examples for each isolation.
begin
DROP TABLE Product
end
create table Product
(ProdID int,
ProdName Varchar(50),
ProdPrice int
)
insert into Product(ProdID,ProdName,ProdPrice)
values
( 1,'Black Shirt',1000),
( 2,'White Suits',2000),
( 3,'Red Kurta',700),
( 4,'Half Shirt',1700)
Run both the sessions side by side
Example 1:
Session 1:
begin tran
update Product set ProdPrice=999 where ProdID=1
waitfor delay '00:00:15'
commit
Session 2:
set transaction isolation level read committed
select ProdPrice from Product where ProdID=1
Output:
999
Session 2 output can be seen only after session 1 is completed execution
Example 2: (Carry forward from example 1)
Session 1:
begin tran
select ProdPrice from Product where ProdID=1
waitfor delay '00:00:15'
commit
Session 2:
set transaction isolation level read committed
select ProdPrice from Product where ProdID=1
Output:
999
Output will come immediately as in session 1 there is no DML operation.
Example 3:
Session 1:
begin tran
select ProdPrice from Product where ProdID=1
waitfor delay '00:00:15'
update Product set ProdPrice=1000 where ProdID=1
commit
Session 2:
set transaction isolation level read committed
select ProdPrice from Product where ProdID=1
Output:
999
Output will come immediately as in session 1 there is no DML operation before wait. Later value will change to 1000.
Click on Below links to see the Different types of isolation levels with example
