Repeatable Read - am I understanding this right?

Question

Trying to completely understand SQL Server Isolation Levels - notably REPEATABLE READ.

I have a sproc that starts a transaction and puts a cursor around some data (boo hiss). This can be a fair chunk of data, so can take a while to do.

It will then COMMIT or ROLLBACK.

During this time, before the transaction has been closed, if someone calls a method which causes some of those affected rows to be READ, my understanding is that this method will stall until the first method is complete. They will then be served up the data (as long as a time-out doesn't occur first)

I think I'm right, but question is - am I?!

Answer 1

REPEATABLE READ prevents SELECTs from lifting shared locks they placed until the end of the transaction.

With transaction 1 as READ COMMITTED, you can update a row in transaction 2 after you selected it in transaction 1.

With transaction 1 as REPEATABLE READ, you cannot update a row in transaction 2 after you selected it in transaction 1.

The scenarios:

READ COMMITTED

1 SELECT -- places a shared lock and immediately lifts it.
2 UPDATE -- places an exclusive lock. Succeeds.
1 SELECT -- tries to place a shared lock but it conflicts with the exclusive lock placed by 2. Locks.

REPEATABLE READ

1 SELECT -- places a shared lock and keeps it
2 UPDATE -- tries to places an exclusive lock but it's not compatible with the shared lock. Locks
1 SELECT -- the lock is already placed. Succeeds.

Update:

As for you question: in SQL Server, SELECTs will not lock each other even with REPEATABLE READ, since shared locks they place are compatible with each other:

CREATE TABLE t_lock (id INT NOT NULL PRIMARY KEY, value INT NOT NULL)
INSERT
INTO    t_lock
VALUES (1, 1)

-- Session 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
DECLARE @id INT
DECLARE cr_lock CURSOR DYNAMIC
FOR
SELECT  id
FROM    t_lock
OPEN    cr_lock
FETCH   cr_lock

id
--
1

-- Session 2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
DECLARE @id INT
DECLARE cr_lock CURSOR DYNAMIC
FOR
SELECT  id
FROM    t_lock
OPEN    cr_lock
FETCH   cr_lock

id
--
1

-- Session 1

DEALLOCATE cr_lock
COMMIT

-- Session 2

DEALLOCATE cr_lock
COMMIT