Here is a longer elaboration to the question in the comment above. (Since I need more than 1000 characters, I post this as an Answer.)
In one window run this:
USE tempdb
go
DROP DATABASE IF EXISTS isolocktest
CREATE DATABASE isolocktest
go
USE isolocktest
go
CREATE TABLE sometable (id int NOT NULL,
name sysname NOT NULL,
CONSTRAINT pk_sometable PRIMARY KEY(id)
)
INSERT sometable(id, name)
SELECT object_id, name FROM sys.objects
BEGIN TRANSACTION
SELECT name FROM sometable WITH (REPEATABLEREAD) WHERE id = 6
SELECT name FROM sometable WHERE id = 7
The output is sysclones and sysallocunits.
Now open a second query window and run this:
USE isolocktest
go
UPDATE sometable SET name = 'Paris' WHERE id = 7
UPDATE sometable SET name = 'Madrid' WHERE id = 6
go
USE tempdb
This script will hang on the second UPDATE statement, as it is being blocked by the REPEATABLEREAD hint, which is implemented with a shared lock.
Now go back to the first window and run:
SELECT name FROM sometable WHERE id = 6
SELECT name FROM sometable WHERE id = 7
go
COMMIT TRANSACTION
The output is sysclones and Paris.
Thus, we can see that READ COMMITTED applies the row with id = 7, since we now see the new value. And, it seems that because we applied REPEATABLE READ, this isolation level applies to id = 6, as we still see the old value. But this is not really the case. This is only an artefact of the locking applied. Because of the lock, the other process was stalled, and could not perform its update until the other process had committed.
But imagine that REPEATABLE READ had been implemented by reading from the version store, just like READ COMMITTED might be. In this case, the UPDATE process would not have been blocked. And in this case the second read of id = 6 would have returned Madrid, since the second read of this row is performed under the isolation level READ COMMITTED.