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?

12

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.

  • I tried something similar. It says 'OFF'. – Ian Warburton Oct 7 '17 at 14:26
  • @IanWarburton, what is the isolation level returned when you execute DBCC USEROPTIONS; in the second query window? – Dan Guzman Oct 7 '17 at 14:27
  • Aha.. there it says 'read committed snapshot'. But I didn't turn it on. Is it the default now? – Ian Warburton Oct 7 '17 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 '17 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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