This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The REPEATABLE READ allows you to read the same data repeatedly and it makes sure that any transaction cannot update this data until you complete your reading.
If you are selecting the same row twice in a transaction, you will get the same results both the times.
If someone is modifying the data, you cannot even read those data until they complete the update.
This is very similar to READ COMMITTED Isolation level.
The REPEATABLE READ issues the shared locks on all statements which are in the transaction.
The shared locks do not release lock at each statement level, but it applies at the transaction level.
The REPEATABLE READ does not stop insertion of newer records so when we are reading data with this isolation level, there is a chance to get Phantom or Dirty Reads.
For example,
You are selecting range data between ID 200 and ID 500, and in this range, we have one free ID which is 394.Now the user is going to insert a new record with ID 394, and you are selecting data from multiple sources, so there are chances to get different results for your both SELECT statements.
Like one data set with ID 394 and other data set may be without ID 394.
Now, test the REPEATABLE READ isolation level:
First, create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE tbl_Employee ( EmpID INTEGER ,EmpName VARCHAR(50) ) GO INSERT INTO tbl_Employee VALUES (1,'Anvesh'),(2,'Neevan') ,(3,'Roy'),(4,'Martin') GO |
Open a new query window or session and executing this script:
|
1 2 3 4 5 6 7 8 9 |
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT *FROM tbl_Employee WAITFOR DELAY '00:00:15' SELECT *FROM tbl_Employee ROLLBACK |
During the delay of 15 seconds, Open a new query window or session and try to UPDATE this table:
|
1 |
UPDATE tbl_Employee SET EmpName ='Loother' WHERE EmpID=4 |
Now, you can not UPDATE the data because it used by another session.
