Let’s replicate the problem, for which I am creating a table
StudentMarks and insert three records
create table studentMarks (
id
int
,name varchar(50)
,total_marks int
)
insert into studentMarks(id,name,total_marks) values( 1,'Prayansh',430);
insert into studentMarks(id,name,total_marks) values( 2,'Rishika',345);
insert into studentMarks(id,name,total_marks) values( 3,'Aayansh',390);
Now lets open a transaction in a new session and try to read this table twice,
and in between two read keep a delay of 18 Sec
Open Session1, and execute below transaction
begin tran
select * from studentMarks where ID in(1,2)
waitfor delay '00:00:18'
select * from studentMarks where ID in (1,2)
rollback
Parallely open another session and execute below session
update studentMarks
set total_marks=499 where id=1
Now the output from the first session you will find
id name total_marks
----------- ----------------------
1 Prayansh 430
2 Rishika 345
(2 row(s) affected)
id name total_marks
----------- -------------------------
1
Prayansh 499
2 Rishika 345
(2 row(s) affected)
Here if you look at session1 output, a same select statement gives two
different outputs, that is because , while session one is waiting for 18 second,
session two updated the marks of id=1 to 499.
This is a problem with repeatable read if any outside transaction
updates the data in between two reads.
How to avoid it
It can be avoided by setting transaction isolation level to repeatable
read, let’s see how
set transaction isolation level repeatable read
begin tran
select * from studentMarks where ID in(1,2)
waitfor delay '00:00:15'
select * from studentMarks where ID in (1,2)
rollback
Now here what will happen, if there is any outside transaction tries to
modify the data which affect the Transaction one query then, the outside
transaction will go on wait for getting update lock until transaction1 complete
its operation.
