6

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?

1 Answer 1

13

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.

4
  • I tried something similar. It says 'OFF'. Oct 7, 2017 at 14:26
  • @IanWarburton, what is the isolation level returned when you execute DBCC USEROPTIONS; in the second query window?
    – Dan Guzman
    Oct 7, 2017 at 14:27
  • Aha.. there it says 'read committed snapshot'. But I didn't turn it on. Is it the default now? Oct 7, 2017 at 14:28
  • @IanWarburton, the default hasn't changed. I can't say how the option may have been changed from the default.
    – Dan Guzman
    Oct 7, 2017 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.