Repeatable Read - am I understanding this right?

Question

Hello

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

Correct.

A full description from MSDN:

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.

Answer 2

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