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 12345»»»

Row Level Versioning Expand / Collapse
Author
Message
Posted Saturday, April 5, 2008 5:18 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 2,361, Visits: 6,746
Comments posted to this topic are about the item Row Level Versioning

-Roy
Post #480439
Posted Monday, April 7, 2008 4:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Good article. I was getting a bit lost following your examples (committed selects?) but I think the basic ideas were well covered. If people wish to know more they should look up transaction isolation levels in general to see how locking has always been done and they can then gain an appreciation of the potential problems solved (and introduced) by row level versioning.

I'm hoping to turn on read committed snapshot on in one of our installations at a customer's site because they have a reasonably (once a minute) continuous data import that needs to scan our tables (about 10 of them) each having 20-60k rows whilst other users are performing occasional transactions against the system. Even though the import is broken up into many small transactions it can still cause a pause of 5 or so seconds in our client application...

I wasn't aware that row level versions were used for the deleted table in triggers but now that I think about it that certainly makes sense :)



Post #480680
Posted Monday, April 7, 2008 4:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 9:44 AM
Points: 11, Visits: 24
I believe that there is a typo when talking about committing the transactions. The line that reads:

Just execute the commented statement. Commit tran b.


Should be:
Just execute the commented statement. Commit tran a.
Post #480682
Posted Monday, April 7, 2008 7:49 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 12, 2014 9:26 PM
Points: 68, Visits: 172
Let me get this straight.
If a field has a value of, say $0 and it is in the process of being commited to $1000000000, then using row versioning will not display older inaccurate value until it is commited.

So are we saying then that a feature like this would only be used in situations like reporting, where it does not matter if the data is to-the-point accurate?
Post #480810
Posted Monday, April 7, 2008 7:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 2,361, Visits: 6,746
Actually if the committed value was 0 and it is going to be updated to 10000000, then when you select the row, you will get 0.
You will get the most latest Committed value. That is the true value. This will reduce Wait for resource and lessens the load on the server.


-Roy
Post #480827
Posted Monday, April 7, 2008 8:23 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 12, 2014 9:26 PM
Points: 68, Visits: 172
Yes sorry, a typo. I meant to say "then using row versioning WILL display older...".

I understand how it works (great article), but I thought the whole idea of locks etc was for atomicity, isolation and general data integrity. Could this then not be a dangerous feature, otherise it would be on by default?
Post #480868
Posted Monday, April 7, 2008 8:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 2,361, Visits: 6,746
On the contrary it helps the Acid property. As long as you get the right Data, you are doing good. You are actually getting the last committed data. From the example I am providing below we can argue both ways.
Let us look at it this way. The last updated data was 0 for a row and it is in the process of being updated to 1000000. At this point, the most accurate data is 0. Why? Because it is the last committed data. What if the update is being Rolled back. Even after the transaction was completed, the correct value is 0.
It all depends on how you are using the data or how your operation is in your company. Even though it is a cool feature, I have reservation of this. Now I am going to counter argue my own posting...:D
If I am trying to retrieve data for a report that is not the latest, I would go actually go Read Uncommitted. The main reason is I dont want to wait for Resources. I will not be doing any locking either. Not even a shared lock.
Now I have defended the article and countered it by myself...LOL


-Roy
Post #480882
Posted Monday, April 7, 2008 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 266, Visits: 2,598
I think this is a great article. I like that you explain the concept and then explain the pros and cons.

I understand the difference between the subject of the article and using NO LOCK hints. (At least I think I do.) But I wonder if it might have made the article a bit stronger to add a paragraph on how this is a different thing entirely than NO LOCKS and why. But maybe that would be considered "beyond the scope of the article". I'm just throwing out a thought.

Thanks for the article.
Post #480992
Posted Monday, April 7, 2008 10:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 2,361, Visits: 6,746
Hi JJ, Thanks for the compliments. Maybe I could write it as a supplement of this article. I really did not think about it. Thanks for pointing it out.

-Roy
Post #480993
Posted Tuesday, April 8, 2008 11:59 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,375, Visits: 1,391
Good article. The basics are well written. :)


Post #482062
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse