What is Repeatable Read problem and how to solve it?

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.

Post Reference: Vikram Aristocratic Elfin Share