Too bad I wasn't aware of this post earlier but I feel like contributing. The whole point behind my post was basically to raise awareness to the guy in need about the NOLOCK option but maybe I should have stressed a little more to be really aware where to use it. I used to be the head developer of a "large" oltp system where we needed to do nasty calculations in real-time to present on the web. The data source changed so fast that using NOLOCK was the smallest of several cons when doing selects. In the post Kristen referred to I stated that 98% of all selects are on historic data that might as well have been cast in stone, and why on earth would I need to bother my server with locking these data when there is no chance whatsoever that they will be changed?
And the question was raised if I had done extensive testing on this or not, and belive me; I have (!) and the results made me a sworn beliver! The most important thing to consider is this: "Will anything ever get corrupted if I use NOLOCK in this select?" If the answer is no, I can't think of any reason why you shouldn't use it.
-- Lumbago "Real programmers don't document, if it was hard to write it should be hard to understand"
Sunil, Thank you for your response, I have follow ups: 1. regarding the balance between the two it appears that while the new feature is more consistent for a company using nolock because their performance is on the edge - the scaling requirements to ensure that changing to snapshot queries won't worsen their performance situation make it a difficult sell to the customer - expecially that 14 bytes of data on each row requirement...
2. Regarding guidance, I agree with your suggestions - in fact I'll go furhter and say the "Update <table ReportData> With Select <Table1Data> join <Table2Data>" style of query routinely causes problems and would be better implemented as a large query with a separate process to then insert the rows of the Report Data to maintain a scalable environment. But what I'm looking for is formal guidance that says that in a production environment of thousands or millions of rows this type of statement is bad because it creates a long running transaction.
Similarlly a statement that while a bulk upload to initialize a database makes sense a daily upload of data for an older file based interface is more scalable when the uploading process executes a larger number of small transacations instead of trying to do a bulk upload in the middle of traditional OLTP activities.
I think there are alot of people that recognize these patterns in database implementation, but unfortunately there are far more people who don't and who have to be convinced every time.
Consensus on NOLOCK
Posted - 06/22/2006 : 10:32:41
