Isolation levels - Database Engine

  • Yes, that is exactly why. As I said, the issue of the correct answer relates to the specific phrasing and setup of the scenario. If there were either commits or transaction ends between the queries and updates, the result would be very much influenced by the high order database setting of READ_COMMITTED_SNAPSHOT.

  • Got it. Thanks for the clarification.

  • Bitbucket,

    Irish Flyer has already stated the fact that was confusing me.

    From the way your scenario was stated, the second script did not commit its changes. Hence I wrote my tests using 'ROLLBACK TRAN' to cancel the commits.

    As a result, I can't see any way to distinguish between READ COMMITTED and SNAPSHOT (as my tests showed) in the given scenario.

    They are only distinguishable if something actually gets commited.

    If script 2 actually commited it's change before script 1's second select and script 1 got the same result then that would be SNAPSHOT, but since that's not how you stated the problem I don't see how you can choose between the two.


  • I agree with BitBucket. The results described at what you would expect in "normal" operations (assuming the typo is corrected)

  • Ray Herring (3/6/2009)

    I agree with BitBucket. The results described at what you would expect in "normal" operations (assuming the typo is corrected)

    I understand the sections BitBucket quoted, but can't see how the answer SNAPSHOT is correct and READ COMMITTED isn't when the scenario has no commits!

    As far I can see, since nothing has been committed, it doesn't matter which isolation level is in effect, transaction 1 won't see any change.

    Now if the scenario was changed so that transaction 2 committed it's change before 1's 2nd select then the described behaviour is SNAPSHOT isolation level. But the question explicitly stated

    The first transaction continues and again executes before the 2nd transaction committs

    In this situation, it's impossible to tell whether it's SNAPSHOT or READ COMMITTED, as my test scripts show!


  • Hi Derek, I agree with you and Irish Flyer hat this a plain read committed isolation level scenario.

    By the way, I like your scripts. Nicely contained, but detailed.

    Kinda like that Tom Kite stuff.:)


  • Another poorly worded and ill-thought out question!:angry:

  • Great question and very relevant to concurrency and data integrity issues.

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply