Is it possible that T2 reads,updates and commits the row in question
BEFORE T1 gets to read it ?
Yes, as noted by Paul White here:
The repeatable read isolation level provides a guarantee that data
will not change for the life of the transaction once it has been read
for the first time.
Also due to lock escalation the query can start with row locks and then escalate to a full table lock. If the update runs while row locks are being taken and the row has not been accessed yet, then the update will not be blocked.
If that is so,then RR would not have an effect,or as a matter in this scenario no isolation level would have an effect becasue
isolation concerns only rows that they've been ALREADY read,not those
not yet read.True?
you could use full table locking with a
TABLOCK hint to make sure that the entire table is locked in RR but that reduces concurrency.
SELECT * FROM dbo.mytable WITH(TABLOCK) WHERE city='London';
Of course, if you are selecting a lot of data anyway the table lock escalation might happen nonetheless.
This is commonly around the 5000 row mark but can be higher/lower.
Is however SERIALIZIBLE different? Does it take key range locks which means it locks the rows BEFORE it has read them? So would
SERIALIZIBLE in the code example above take any key ranges locks that
would prevent the scenario of T2 getting to the row and updating
before it is read?
Yes, it takes range locks and the considerations for equality range locks are noted by Sunil Agarwal here:
- If the key value exists, then the range lock is only taken if the index is non-unique. In the non-unique index case, the ‘range’ lock
is taken on the requested key and on the ‘next’ key. If the ‘next’
key does not exist, then a range lock is taken on the ‘infinity’
value. If the index is unique then a regular S lock on the key.
- If the key does not exist, then the ‘range’ lock is taken on the ‘next’ key both for unique and non-unique index. If the ‘next’ key
does not exist, then a range lock is taken on the ‘infinity’ value.
In your case the key exists, and the index is probably non-unique so you would have a range lock up to the next value or the infinity value.
However I would be wary of using the serializable isolation level, it can introduces deadlocks and other concurrency issues.