Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row Level Versioning


Row Level Versioning

Author
Message
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3310 Visits: 6862
Comments posted to this topic are about the item Row Level Versioning

-Roy
Ian Yates
Ian Yates
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 445
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 Smile



Buzz-686799
Buzz-686799
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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.

Drew Salem
Drew Salem
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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?
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3310 Visits: 6862
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
Drew Salem
Drew Salem
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 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?
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3310 Visits: 6862
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...BigGrin
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
JJ B
JJ B
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 2858
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.
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3310 Visits: 6862
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
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6847 Visits: 1407
Good article. The basics are well written. Smile



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search