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 4th 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.