March 5, 2009 at 1:01 pm
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.
March 5, 2009 at 1:05 pm
Got it. Thanks for the clarification.
March 6, 2009 at 6:07 am
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.
Derek
March 6, 2009 at 8:18 am
I agree with BitBucket. The results described at what you would expect in "normal" operations (assuming the typo is corrected)
March 6, 2009 at 8:45 am
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!
Derek
March 6, 2009 at 10:41 am
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.:)
raymond
March 12, 2009 at 9:56 am
Another poorly worded and ill-thought out question!:angry:
October 11, 2010 at 9:24 am
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