create table test (a integer, r rowversion);
insert into test values (1, null);
Then doing this simultanously in two sessions:
set transaction isolation level repeatable read
set implicit_transactions off
set nocount on
declare @r rowversion
declare @a int
select @r=r, @a=a from test
print convert(varchar(20), getdate(), 114)+' Select ok - '+cast(@a as varchar(5))
waitfor delay '0:0:5'
print convert(varchar(20), getdate(), 114)+' wait ok'
update test set a=@a+1 where r = @r
print convert(varchar(20), getdate(), 114)+' update ok'
select * from test
When using isolation levels "repeatable read" or higher, it should not be possible to read a row someone else already has selected in the same IL (but other transactions) - this is the behaviour we need.
But I can. Why? The BOL states for RR IL:
This prevents other transactions from modifying any rows that have been read by the current transaction.
which I understand that the SELECTs should pass, but the first
UPDATE error out.
What I get is a deadlock in the second session. I would have expected the SELECT of the second session having to wait until the first transaction is ended, but that isn't the case.
Worse, if the second transaction waits longer (say 30 seconds), the UPDATE (!) of the first also waits until the UPDATE attempt is made by the 2nd, and by then triggers a deadlock for the 2nd.
The issues now are:
the BOL and what I see do not fit together
the only way to get a SELECT waiting if another transaction selected the same row is by using a SELECT ... WITH (XLOCK), but that would require me to modify ALL SQLs going thru my DB access layer (an unmanaged C++ DLL) in RR IL. Or to manually locate the places we wish this behaviour, and add the lock hint.
SQLs should not have to be modified (much), as the same SQLs are running against different DBMS (like Oracle).
I've tested this on 2008r2 (with DB in 2000 mode), but think it is the same for any MSSQL release.