Why can SELECT see updated row before commit?

If I run this without a commit statement...

begin transaction

update dateranges set DateRangeTypeId = 1 where DateRangeId = 154

In another tab of Management Studio, I can select the range with Id 154 and it shows the old value for the type column.

Why doesn't the SELECT block on the updated row?

Assuming the second SSMS window is using the default READ_COMMITTED isolation level, you will see the before value instead of being blocked by the uncommitted transaction if the database has the READ_COMMITTED_SNAPSHOT setting turned on.

SELECT name, snapshot_isolation_state_desc
FROM sys.databases
ORDER BY name;

The READ_COMMITTED_SNAPSHOT database setting uses row versioning instead of locking for read consistency so writers don't block readers and visa-versa.

StackExchange Database Administrators Q#187928, answer score: 13