SQL Server: What is REPEATABLE READ Isolation Level?

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 same row twice in a transaction, you will get the same results both times.

If someone are modifying the data, you can not 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 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 user is going to insert a new record with ID 394 and you are selecting data from multiple source 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 this REPEATABLE READ isolation level:

First, create a table with sample data:

Open a new query window or session and executing this script:

During this 15 second delay, Open a new query window or session and try to UPDATE this table:

The result is, You can not UPDATE the data because it used by other session.

Anvesh Patel

Leave a Reply

1 Comment on "SQL Server: What is REPEATABLE READ Isolation Level?"

Notify of
Sort by:   newest | oldest | most voted

Greate pieces. Keep posting such kind of information on your page.
Im really impressed by your site.
Hi there, You have done an excellent job. I will certainly digg it and individually suggest
to my friends. I am sure they’ll be benefited from
this site.