InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 22:06:56 GMT20http://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspx[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]I got the same results. The article seems very good and I have a much better understanding of SQL Server's row level versioning capabilities, but I got the same results here. Will continue reading for any insight into why this happens.RThu, 31 May 2012 13:37:51 GMTrmizrahihttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxI also got incorrect results. I could never get the other machine (or the local machine, for that matter) to show me the 2 once I had run transaction b.Thu, 31 May 2012 13:31:20 GMTrmizrahihttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxYes, you will need to remove those hints that you have given. It will defeat the purpose.Tue, 31 Jan 2012 10:55:43 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThanks for article Roy.i am considering turning on these features on an application we have at my current company, but had a question.If i alter my database with;ALTER DATABASE MyDatabaseSET ALLOW_SNAPSHOT_ISOLATION ONALTER DATABASE MyDatabaseSET READ_COMMITTED_SNAPSHOT ONDo I need to alter all my existing stored procedures that make use of the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDstatement?also, the hint with (NOLOCK), should those also be removed?Thanks.Tue, 31 Jan 2012 10:45:08 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThe select should be run on a different SID. If you run it on the same SID, you will see the updated non committed value.Fri, 27 Aug 2010 07:15:22 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxEven i get the result reflecting with new value before the commit tran is issued. I had ran all the select & update with same SID. Also, if th above doesnt make any sense. DO i need to recycle my sql sever instance to take effect. ?????Fri, 27 Aug 2010 03:42:16 GMTmanoj.kshttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxI just ran the sample script provided in the article. But i am not seeing the effect of row-versioning. I have enabled LLOW_SNAPSHOT_ISOLATION & READ_COMMITTED_SNAPSHOT also. Do i need to restart my instance to take effect.Wed, 25 Aug 2010 03:46:41 GMTmanoj.kshttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxDid you run the select statement on the same query window or on a different SID?Tue, 27 Jul 2010 07:09:03 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxI just did the same way as roy suggested. I turned ON row-level versioning. But it was not working the way he said. Do i need to restart my instance to take the effect of database option change(row level versioning)?Tue, 27 Jul 2010 06:40:22 GMTmanoj.kshttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThanks Roy. Thanks a lot....Regards,RamaniTue, 18 Aug 2009 22:19:56 GMTChandhinihttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThank 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.Tue, 18 Aug 2009 06:55:49 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxVery 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,RamaniTue, 18 Aug 2009 03:54:36 GMTChandhinihttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxI 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?Sun, 15 Mar 2009 23:47:57 GMTrajakumar.neelamhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxGood 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]AndrewMon, 09 Mar 2009 07:49:17 GMTAndrew Peterson-472853http://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxHi, 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" ? Mon, 09 Mar 2009 05:58:33 GMTryberg_bhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspx[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.:)Fri, 06 Mar 2009 14:41:49 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspx[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.Fri, 06 Mar 2009 14:40:35 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxLogically 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 --Fri, 06 Mar 2009 13:55:20 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxGood 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.Fri, 06 Mar 2009 12:34:25 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspx[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. :DFri, 06 Mar 2009 07:50:32 GMTDavid Benoithttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxI 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.Fri, 06 Mar 2009 07:44:59 GMTHuonglien Nguyenhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxNice 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.Fri, 06 Mar 2009 07:12:59 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThank 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.Fri, 06 Mar 2009 06:05:05 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThis is a really good article. Top Job :DFri, 06 Mar 2009 04:19:44 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspx...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.htmlFri, 06 Mar 2009 02:38:55 GMTleifahhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxNice 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:Fri, 06 Mar 2009 02:04:32 GMTDrikus Rouxhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThank 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?Fri, 06 Mar 2009 01:09:02 GMTKarsten-159275http://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxPaul,Executing the select in a different query window does the trick(In my case...)Regards,Bart de VriesFri, 06 Mar 2009 01:07:09 GMTBart-328753http://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxPaul,Executing the select in a different query window does the trick(In my case...)Regards,Bart de VriesFri, 06 Mar 2009 01:03:52 GMTBart-328753http://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxHi 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. RoyThu, 24 Jul 2008 06:47:16 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThanks 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?Wed, 23 Jul 2008 18:42:06 GMTsharon.benderhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxPaul, I tried the set up three times and the three times it worked just fine.Fri, 25 Apr 2008 13:06:08 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxDid 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.Fri, 25 Apr 2008 13:02:28 GMTpaul.lewishttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxThanks 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.Wed, 09 Apr 2008 06:08:51 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxGood article. The basics are well written. :)Tue, 08 Apr 2008 23:59:58 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxHi 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.Mon, 07 Apr 2008 10:42:29 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxI 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.Mon, 07 Apr 2008 10:39:13 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxOn 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...LOLMon, 07 Apr 2008 08:34:41 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxYes 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?Mon, 07 Apr 2008 08:23:55 GMTDrew Salemhttp://www.sqlservercentral.com/Forums/Topic480439-1188-1.aspxActually 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. Mon, 07 Apr 2008 07:56:10 GMTRoy Ernest