SQL Server Isolation Levels - Repeatable Read

Question

Hello

I'm having problems getting my head round why this is happening. Pretty sure I understand the theory, but something else must be going on that I don't see.

Table A has the following schema:

ID [Primary Key]
Name
Type [Foreign Key]

SprocA sets Isolation Level to Repeatable Read, and Selects rows from Table A that have Type=1. It also updates these rows.

SprocB selects rows from Table A that have Type=2.

Now given that these are completely different rowsets, if I execute both at the same time (and put WAITFOR calls to slow it down), SprocB doesn't complete until SprocA.

I know it's to do with the query on Type, as if I select based on the Primary ID then it allows concurrent access to the table.

Anyone shed any light?

Cheers

Answer 1

SQL Server uses indexes to do range locks (which is what repeatable reads often use) so if you don't have index on Type perhaps it locks entire table...

Answer 2

With Repeatable Read set for the isolation level, you will hold a shared lock on all data you read until the transaction completes. That is until you COMMIT or ROLLBACK.

This will lower the concurrency of your application's access to this data. So if your first procedure SELECTS from table then calls a WAITFOR then SELECTS again etc within a transaction you will hold the shared lock the entire time until you commit the transaction or the process completes.

If this is a test procedure you are working with try added a COMMIT after each select and see if that helps the second procedure to run concurrently.

Good luck!

Kevin

Answer 3

The thing to remember is that the locked rows are black boxes to the other process.

You know that SprocA is just reading for type = 1 and that SprocbB is just reading for type = 2.

However, SprocB does not know what SprocA is going to do to those records. Before the transaction is completed, SprocA may update all of the records to type = 2. In that case, SprocB would be working incorrectly if it did not wait for SprocA to complete.

Maintaining concurrency when performing range locks / bulk changes is tough.