SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Roy Ernest / Row Level Versioning / Latest Posts

RE: Row Level Versioning

Chandhini — Tue, 18 Aug 2009 22:19:56 GMT

Thanks Roy. Thanks a lot....Regards,Ramani

RE: Row Level Versioning

Roy Ernest — Tue, 18 Aug 2009 06:55:49 GMT

Thank you. The old data will be cleared automatically. You do not have to worry about that. Just keep in mind that the more transactions you have (update, insert and delete) the more tempdb will be used. Along with the normal tempDB usage.

RE: Row Level Versioning

Chandhini — Tue, 18 Aug 2009 03:54:36 GMT

Very nice article. it is very clear..Tempdb will go like anything because of row versioning also.. so is there anyway to remove old row versioning data from tempdb or sql server itself will remove old row versioning data automatically or it can be done manually. if so, how it should be implemented?please clarify this....Regards,Ramani

RE: Row Level Versioning

rajakumar.neelam — Sun, 15 Mar 2009 23:47:57 GMT

I am using SQL server 2005. I just tried executing your example given here. Though i don't commit the transaction 'a' i am getting the updated value '2' when i run select query. what could be the reason?And also you are saying that the tempdb database is used for row level versioning stuff. But i could not see the 'test' table (which i created) in tempdb database. I am thinking that the 'test' table will be created in tempdb too. what do you say? Or else where does it stores versioning data?

RE: Row Level Versioning

Andrew Peterson-472853 — Mon, 09 Mar 2009 07:49:17 GMT

Good intro. MVCC (multi-version concurrency) is a major addition to SQL Server.I encourage everyone to fully understanding the differences between Read Committed Snapshot, and SnapShot Isolation. Miss-use of SnapShot Isolation can lead to excessive, and usually unexpected tempdb usage. Here are some references:[url]http://www.fotia.co.uk/fotia/Blog/2006/05/snapshot-isolation-part-i.html[/url][url]http://www.fotia.co.uk/fotia/Blog/labels/transactions.html#PreviousPosts[/url][url]http://www.databasejournal.com/features/mssql/article.php/3560451/Controlling-Transactions-and-Locks-Part-4---SQL-2005-Snapshot.htm[/url][url]http://www.sqlteam.com/article/transaction-isolation-and-the-new-snapshot-isolation-level[/url]Andrew

RE: Row Level Versioning

ryberg_b — Mon, 09 Mar 2009 05:58:33 GMT

Hi, as one other poster commented, you have a typo that would make this whole thing not work as described :) When you ask of the reader to commit the first tran a, you in fact commit the second, tran b .But other than that - it's a well written article !" Let us commit the first update. Just execute the commented statement. Commit tran b. "- which would probably not work at all , since that one is waiting for things to end in "tran a" ?

RE: Row Level Versioning

Roy Ernest — Fri, 06 Mar 2009 14:41:49 GMT

[quote][b]Amit Lohia (3/6/2009)[/b][hr]Good Article but you need a followup article to cover the issue raised in discussion. As how this is different from nolock and other isolation level. A real time example or a scenario where this will be useful and will make huge difference.[/quote]Thanks.. I will try to do that. And Thanks for taking time to read the article.:)

RE: Row Level Versioning

Roy Ernest — Fri, 06 Mar 2009 14:40:35 GMT

[quote][b]Huonglien Nguyen (3/6/2009)[/b][hr]I followed through the steps but eventhough after I executed "Commit tran a" the select * on dbo.test still showed me the old data: 1212121 and if I executed "Commit tran b" the select statement showed value 5. So I actually never saw the value 2 at all. Do you have any idea? Thanks.[/quote]To be honest I am lost. I just did all the steps and I could not reproduce the value that you saw.

RE: Row Level Versioning

Mark D Powell — Fri, 06 Mar 2009 13:55:20 GMT

Logically all rows returned by a query should be at the same poiint in time. Without row level versioning this is not possible if concurrent DML is supported. Basically SQL Server's row level versioning is equilivent to Oracle's use of rollback segments, now usually called undo. In Oracle readers do not block readers and neither do writers block readers because the reader gets the time consistent version of the data.There is a cost and there are situations where this cost can get high, but then there is also cost savings on the locking end.This is definitely an advancement in the basic database feature set.-- Mark D Powell --

RE: Row Level Versioning

Amit Lohia — Fri, 06 Mar 2009 12:34:25 GMT

Good Article but you need a followup article to cover the issue raised in discussion. As how this is different from nolock and other isolation level. A real time example or a scenario where this will be useful and will make huge difference.

RE: Row Level Versioning

David Benoit — Fri, 06 Mar 2009 07:50:32 GMT

[quote][b]Karsten (3/6/2009)[/b][hr]A thought crossed my mind, whether this is similar to the default implementation of READ COMMITTED isolation level in Oracle databases, isn't it?[/quote]Uh Huh. :D

RE: Row Level Versioning

Huonglien Nguyen — Fri, 06 Mar 2009 07:44:59 GMT

I followed through the steps but eventhough after I executed "Commit tran a" the select * on dbo.test still showed me the old data: 1212121 and if I executed "Commit tran b" the select statement showed value 5. So I actually never saw the value 2 at all. Do you have any idea? Thanks.

RE: Row Level Versioning

Chris Harshman — Fri, 06 Mar 2009 07:12:59 GMT

Nice article on this topic. Glad to see others spreading the word! :DWhen I first started using SQL Server 2000 (after previously using Oracle and Interbase) I was suprised that it didn't have any kind of multiversion concurrency. It was also a little scary to see that so many people would try to get arround all the shared locks that SELECT queries created by using READ UNCOMMITTED transaction isolation level or the NOLOCK hint, which opens up the possibility for seeing inconsistant or "dirty" data. Row Versioning in 2005 offers a clean data solution that doesn't require all those shared locks bogging down the system so your end users get better response times.Yes it uses TempDB more, but from my experience 2005 manages TempDB much better than 2000 ever did, and as others stated, keeping TempDB on a separate drive from your data files, and separate drive from your transaction log files, creates an efficient system that is very responsive.

RE: Row Level Versioning

Roy Ernest — Fri, 06 Mar 2009 06:05:05 GMT

Thank you all for taking time and reading the article :). Anything that is done in production environment should be done only after thinking it out and seeing if it actually does what you need. It all depends on your environment.

RE: Row Level Versioning

Matt Whitfield — Fri, 06 Mar 2009 04:19:44 GMT

This is a really good article. Top Job :D

RE: Row Level Versioning

leifah — Fri, 06 Mar 2009 02:38:55 GMT

...or the same functionality INTERBASE introduced a decade ago?Sorry for being slightly off topic, but however much I like SQL Server, Reporting Services & Integrations services, I think it's really sad that Interbase didn't get more appreciation. Great developers and technology, lousy marketing.Their version explained here: http://www.dbginc.com/tech_pprs/IB.html

RE: Row Level Versioning

Drikus Roux — Fri, 06 Mar 2009 02:04:32 GMT

Nice technology, however one will have to consider several things and specifically the impact on temdb carefully.For understanding SQL Server locking, Erland Sommarskog has a great script: [url=http://www.sommarskog.se/sqlutil/aba_lockinfo_sqlmm_sp3.sp]Download now[/url], that you can use to see locking behaviour on SQL Server.MSDN article [url=http://msdn.microsoft.com/en-us/library/ms345124.aspx]"SQL Server 2005 Row Versioning-based Transaction Isolation"[/url] by Kimberly Tripp and Neal Graves is a very comprehensive treatise :w00t: on the subject of row based versioning. In there the different cases under which certain isolation levels are to be considered are discussed with pros and cons.In my opinion, deciding on an isolation level in 2005 or 2008, you have to know your application's needs very well and also be very up to scratch on how you configure your SQL Server disk array, especially if you operate on a SAN, particularly where to place tempdb. Make a mistake with one of these and your Row versioning based transaction isolation will be an impediment rather than salvation.:hehe:

RE: Row Level Versioning

Karsten-159275 — Fri, 06 Mar 2009 01:09:02 GMT

Thank you for this great article!A thought crossed my mind, whether this is similar to the default implementation of READ COMMITTED isolation level in Oracle databases, isn't it?

RE: Row Level Versioning

Bart-328753 — Fri, 06 Mar 2009 01:07:09 GMT

Paul,Executing the select in a different query window does the trick(In my case...)Regards,Bart de Vries

RE: Row Level Versioning

Bart-328753 — Fri, 06 Mar 2009 01:03:52 GMT

Paul,Executing the select in a different query window does the trick(In my case...)Regards,Bart de Vries

RE: Row Level Versioning

Roy Ernest — Thu, 24 Jul 2008 06:47:16 GMT

Hi Sharon,What you have to do or make sure of is to confirm that the TempDB Database files are not on the same drive as that of your Main DB. Also make sure that the TempDB is not in the same drive as the Operating System. 6GB Memory usage is not that drastic. We use way more than that. Roy

RE: Row Level Versioning

sharon.bender — Wed, 23 Jul 2008 18:42:06 GMT

Thanks Roy, for explaining that.We have been running into some deadlocks lately, due to one main sort of Lookup form our app uses. The number of rows generally returned has doubled recently due to some data additions, and this is locking the select statement long enough to cause drama for users who are trying to update individual records, some of which appear in the Select.I recently changed the Select to use WITH (NOLOCK) much to the chagrin of several DBAs out there who are scared of "dirty reads".In the situations where we are using our data, switching the database settings to read-committed row-versioning would probably be a much better long-term solution.Although I am still rather concerned about the hit the TempDB would take as a result of this. At peak times, our SQL Server is already chewing through somewhere around 6Gb of memory, and I'm afraid this would push it over the edge.Has anyone done any particular load testing on how the TempDB is impacted by such a setting change?

RE: Row Level Versioning

Roy Ernest — Fri, 25 Apr 2008 13:06:08 GMT

Paul, I tried the set up three times and the three times it worked just fine.

RE: Row Level Versioning

paul.lewis — Fri, 25 Apr 2008 13:02:28 GMT

Did anyone else try this and not get the expected results? When I tried this, and ran tran a without committing, the value in the select statement was 2, not 1212121.

RE: Row Level Versioning

Roy Ernest — Wed, 09 Apr 2008 06:08:51 GMT

Thanks Anirban:). I tried. I did miss couple of things like others have pointed out. I have to keep in mind to cover all bases when I write my next article.

RE: Row Level Versioning

Anirban Paul — Tue, 08 Apr 2008 23:59:58 GMT

Good article. The basics are well written. :)

RE: Row Level Versioning

Roy Ernest — Mon, 07 Apr 2008 10:42:29 GMT

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.

RE: Row Level Versioning

JJ B — Mon, 07 Apr 2008 10:39:13 GMT

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.

RE: Row Level Versioning

Roy Ernest — Mon, 07 Apr 2008 08:34:41 GMT

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...:DIf 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

RE: Row Level Versioning

Drew Salem — Mon, 07 Apr 2008 08:23:55 GMT

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?

RE: Row Level Versioning

Roy Ernest — Mon, 07 Apr 2008 07:56:10 GMT

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.

RE: Row Level Versioning

Drew Salem — Mon, 07 Apr 2008 07:49:48 GMT

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?

RE: Row Level Versioning

Buzz-686799 — Mon, 07 Apr 2008 04:34:51 GMT

I believe that there is a typo when talking about committing the transactions. The line that reads:[quote]Just execute the commented statement. Commit tran b.[/quote]Should be:[quote]Just execute the commented statement. Commit tran a.[/quote]

RE: Row Level Versioning

Ian Yates — Mon, 07 Apr 2008 04:26:39 GMT

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 :)

Row Level Versioning

Roy Ernest — Sat, 05 Apr 2008 17:18:08 GMT

Comments posted to this topic are about the item [B]Row Level Versioning[/B]