UPDATE dbo.tb SET name = name WHERE id <= 2;
so I changed the command with
UPDATE dbo.tb SET name = 'a' WHERE id <= 2;
this time, the select command was blocked!!!! here is the profiler trace when session 2 was being blocked.
then I rollback the session 1 transaction, get the new traceso generally speaking,
1. when "SET name = name ", the select command doesn't request S lock on Key, only need IS lock on page.
2. when "SET name = 'a' ", the select command request S lock on key(or page), so it was blocked.
but why? for first condition("name=name"), actually, the data is not changed, but for second condition("name='a'), the data is changed.
I did more testings:
rollback session 1 (SET name = 'a' ), run session 1 with "SET name = name " again, but this time, the session 2 was still blocked, even if we use "set name=name".
so I think if the data page is not touched(changed), SQL Server will use a smart way to get data without S lock, but once the data page is changed(even if we rollback the change), in order to avoid dirty data read, SQL Server will use S lock when selecting data, that make sense.
To prove my thought, after rollback session 1(SET name='a'), I run "checkpoint" to write the dirty page to disk, then do the testing with condition (SET name=name), this time the select query return quickly and no blocking issue.
later, I searched on Internet, and found Paul White had shoot this issue before in a wonderful article:
The Locking OptimizationSQL Server contains an optimization that allows it to avoid taking row-level shared (S) locks in some circumstances. Specifically, it can skip shared locks if there is no risk of reading uncommitted data without them.
but sometimes the optimization is not smart, when I changed select query to
SELECT * FROM dbo.tb WHERE id <= 600
then blocking occurs no matter what update command is................