Transaction Isolation - Part 6 [Repeatable Read]

This isolation level will make sure to lock the Retrieved rows from any update and in the mean time it will allow inserts as well as read on the table. Here the lock is on all the retrieved rows marked in the transaction. This transaction isolation makes sure “inserts OK Proceed, Updates Please Wait” on the locked set of Rows. Now look at the below two Queries:

Query Window 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

Select * from student;

Commit Transaction;

Query Window 2:

Select * from student where Studid = 108;

Insert into student values(110, 'Ramesh', 4);

Insert into student values(111, 'Rakesh', 2);

update student set class = 4 where studid = 108;

As you did in the previous sections, Execute the query till the select statement in the query window 1. Then execute the SQL Statements one by one from query window 2. You will see that select and couple of inserts are executed. But, the update statement waits and once you execute the commit transaction on the Query Window 1, the update in query window 2 proceeds Promoting the Student 108 to 4^th standard of class.

Look at the name of the Isolation. Repeatable read, means that I will have multiple reads in the same table and going fetch two or three or more batches of records and I want to make sure nothing got changed in the retrieved rows until I mark my transaction as finished either by Commit transaction or Roll back transaction. Now look at below two statements:

Query Window 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

Select * from student where studid = 108;

Commit Transaction;

Query Window 2:

update student set class = 3 where studid = 101;

update student set class = 4 where studid = 108;

In the above transaction, the first update statement gets executed and next one gets blocked until the Transaction in the Query window 1 is committed. Because, we blocked only the rows with stuid = 108 in the Query Window 1 Transaction.