SQL Server do not block table/row when I update row to the same value

create table test (
id int identity,
id_int int default 1
)

insert test default values
go 1000

begin transaction

update test
set id_int = id_int
where id = 1000

waitfor delay '00:00:10'

commit


Another session without waiting get the result:
set transaction isolation level read committed

select * from test
where id =1000

id | id_int
---------------
1000 |1



Could someone explain why I get the result immediately?

However, when I use repeatable read isolation level I should wait:
set transaction isolation level repeatable read

select * from test
where id =1000


It is becauase SQL Server is doing a non updating update, look at the below locks it is aquiring in two cases,

Case 1: (Non updating update)

It is smart enough to allow IS lock while the IX is active on the obect.

Case 2: (Real update)

It waits for IX lock to be released before IS is granted to avoid dirty read.

Case 3: (Non updating update with serializable isolation)

If you want to explicity override this behavior you could use SERIALIZABLE isolation while doing the non udpating update which will get an X lock that blocks all other locks on it.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN transaction

    update test 
    set id_int = id_int
    where id = 1000

    waitfor delay  '00:00:10' 

COMMIT

StackExchange Database Administrators Q#335055, answer score: 4