Row Level Versioning

Question

Post reply

Row Level Versioning

Viewing 15 posts - 16 through 30 (of 44 total)

You must be logged in to reply to this topic. Login to reply

Bart-328753 Grasshopper Points: 12 More actions · Answer 1

Paul,

Executing the select in a different query window does the trick(In my case...)

Regards,

Bart de Vries

Bart-328753 Grasshopper Points: 12 More actions · Answer 2

Paul,

Executing the select in a different query window does the trick(In my case...)

Regards,

Bart de Vries

karsti71 Grasshopper Points: 11 More actions · Answer 3

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?

Drikus Roux Say Hey Kid Points: 708 More actions · Answer 4

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: Download now, that you can use to see locking behaviour on SQL Server.

MSDN article "SQL Server 2005 Row Versioning-based Transaction Isolation" 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:

leifah Right there with Babe Points: 724 More actions · Answer 5

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

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 6

This is a really good article. Top Job 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

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.

-Roy

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

Nice article on this topic. Glad to see others spreading the word! 😀

When 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.

Huonglien Nguyen SSC Eights! Points: 912 More actions · Answer 9

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.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 10

Karsten (3/6/2009)
A thought crossed my mind, whether this is similar to the default implementation of READ COMMITTED isolation level in Oracle databases, isn't it?

Uh Huh. 😀

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 11

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.

Kindest Regards,

Amit Lohia

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 12

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 13

Huonglien Nguyen (3/6/2009)
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.

To be honest I am lost. I just did all the steps and I could not reproduce the value that you saw.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 14

Amit Lohia (3/6/2009)
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.

Thanks.. I will try to do that. And Thanks for taking time to read the article.

🙂

-Roy

ryberg_b SSC Enthusiast Points: 182 More actions · Answer 15

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" ?