<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by bitbucket
»
Isolation levels - Database Engine
23 posts, Page 2 of 3
««
1
2
3
»»
Isolation levels - Database Engine
Rate Topic
Display Mode
Topic Options
Author
Message
Cliff Jones
Cliff Jones
Posted Thursday, March 05, 2009 12:25 PM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
I chose the correct answer because I felt it was most correct. But the way I read it was that if you had READ_COMMITTED_SNAPSHOT set to on, it would produce the same result? But that is not the case?
From BOL:
If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.
Post #669533
Irish Flyer
Irish Flyer
Posted Thursday, March 05, 2009 12:35 PM
Ten Centuries
Group: General Forum Members
Last Login: Friday, June 08, 2012 7:51 AM
Points: 1,219,
Visits: 225
Cliff,
You are right that READ_COMMITTED_SNAPSHOT being on or off changes outcomes, but just not in the case stated, because of the WAY it is stated. As long as the two overlapping transactions do not commit and also do not end, they will see the original data row values unless they change the values within themselves, a la Transaction 2. The READ_COMMITTED_SNAPSHOT only makes a difference if there is an intervening commit.
Post #669543
Cliff Jones
Cliff Jones
Posted Thursday, March 05, 2009 12:39 PM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
Wouldn't the last select hang waiting for the pending commit or rollback?
Post #669546
Irish Flyer
Irish Flyer
Posted Thursday, March 05, 2009 12:45 PM
Ten Centuries
Group: General Forum Members
Last Login: Friday, June 08, 2012 7:51 AM
Points: 1,219,
Visits: 225
No. Only if there was a commit and then it go locked by another transaction. Within a transaction, changes made but not committed are immediately available. The changed row is not locked by the transaction that made the change when it is referencing within itself. The uncommitted row is only unavailable and invisible to other transactions.
Post #669550
Cliff Jones
Cliff Jones
Posted Thursday, March 05, 2009 12:56 PM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
Does it work this way because the original select and final select are within a transaction? Or does that matter?
Post #669558
Irish Flyer
Irish Flyer
Posted Thursday, March 05, 2009 1:01 PM
Ten Centuries
Group: General Forum Members
Last Login: Friday, June 08, 2012 7:51 AM
Points: 1,219,
Visits: 225
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.
Post #669567
Cliff Jones
Cliff Jones
Posted Thursday, March 05, 2009 1:05 PM
Hall of Fame
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:11 PM
Points: 3,390,
Visits: 3,403
Got it. Thanks for the clarification.
Post #669569
Derek Dongray
Derek Dongray
Posted Friday, March 06, 2009 6:07 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342,
Visits: 1,946
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
Post #670126
Ray Herring
Ray Herring
Posted Friday, March 06, 2009 8:18 AM
Old Hand
Group: General Forum Members
Last Login: 2 days ago @ 8:17 AM
Points: 334,
Visits: 280
I agree with BitBucket. The results described at what you would expect in "normal" operations (assuming the typo is corrected)
Post #670262
Derek Dongray
Derek Dongray
Posted Friday, March 06, 2009 8:45 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342,
Visits: 1,946
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
Post #670301
« Prev Topic
|
Next Topic »
23 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
