Then doing this simultanously in two sessions:
create table test (a integer, r rowversion); insert into test values (1, null); commit;
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.
set transaction isolation level repeatable read set implicit_transactions off set nocount on declare @r rowversion declare @a int begin tran 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' commit select * from test
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.
From novice to tech pro — start learning today.