Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Isolation levels - Database Engine Expand / Collapse
Author
Message
Posted Thursday, March 5, 2009 12:25 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:30 AM
Points: 3,871, Visits: 3,621
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
Posted Thursday, March 5, 2009 12:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 18, 2013 8:39 AM
Points: 1,219, Visits: 226
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
Posted Thursday, March 5, 2009 12:39 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:30 AM
Points: 3,871, Visits: 3,621
Wouldn't the last select hang waiting for the pending commit or rollback?
Post #669546
Posted Thursday, March 5, 2009 12:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 18, 2013 8:39 AM
Points: 1,219, Visits: 226
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
Posted Thursday, March 5, 2009 12:56 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:30 AM
Points: 3,871, Visits: 3,621
Does it work this way because the original select and final select are within a transaction? Or does that matter?
Post #669558
Posted Thursday, March 5, 2009 1:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 18, 2013 8:39 AM
Points: 1,219, Visits: 226
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
Posted Thursday, March 5, 2009 1:05 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:30 AM
Points: 3,871, Visits: 3,621
Got it. Thanks for the clarification.
Post #669569
Posted Friday, March 6, 2009 6:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Posted Friday, March 6, 2009 8:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:42 PM
Points: 451, Visits: 364
I agree with BitBucket. The results described at what you would expect in "normal" operations (assuming the typo is corrected)
Post #670262
Posted Friday, March 6, 2009 8:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse