The Paradox of NOLOCK: How Dirty Reads Can Sometimes Be Cleaner

Question

The Paradox of NOLOCK: How Dirty Reads Can Sometimes Be Cleaner

Viewing 3 posts - 31 through 32 (of 32 total)

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

will 58232 SSC-Addicted Points: 471 More actions · Answer 1

Hi Rick,

The MVCC thing is fairly incidental to the book which is great because it is such a clear exposition of the math behind the relational model.

will 58232 SSC-Addicted Points: 471 More actions · Answer 2

My point was how you solve serialisable with adequate performance in principle independent of platform, I am well aware of the drawbacks in current methods.

My other point is that concurrency is a difficult subject and one which ideally those involved with applications should not need to know about - the onus is on the software industry to address this problem.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 3

MMartin1 wrote:

Jeff. I'll take a crack at your question:
In your example, are you assuming the date column is indexed and would be used? What if that is not the case, the rows for the past month share records with current month , and updates occur on other rows that cause page splits ? I believe you might get something called a ghost read in these cases.
And all subsequent replies were oblivious to what I mentioned earlier that nolock is not even acted on if you have a AG environment.
Also if the business case is that items can be returned, your counts for previous month(s) can change. If you have a program where you get discount from the manufacturer of a product if you sell over (i dont know) 2000 items in the month, then accuracy starts to matter before you get to the point wher you close the books and no longer accept refunds for some month.

Let's ask the question... you gather the data without using WITH (NOLOCK). As soon as you're done, someone makes a return like what you were talking about. Your report is now inaccurate for the current time. As for the return itself, "It Depends". Are you handling your inventory like a checkbook where you never modify or erase a transaction so that you can actually track the time between the sale and the return? In other words, in such a situation (reporting for previous month), you wouldn't be using any rows that are updated or newly inserted because the rows are frozen at the time of entry.

As for when a page split occurs, I'm pretty sure those pages are locked tighter than a drum with a system lock. I can't say for sure if you'd be able to do a read of either page using WITH (NOLOCK) or not. And then, I'm not sure that the "ghost record" you speak of would be there or not unless it were a Heap, in which case, it probably would be. But, again... it would depend on whether or not you're modifying the rows or treating them as immutable upon entry (which I recommend because compression works much better on such things, as well).

If the table is a Heap, though, there's probably bigger problems to worry about all the way around.

And, just to say it out loud again, I'm NOT advocating for use of WITH (NOLOCK) or setting the transaction isolation level to READ UNCOMMITTED everywhere. We've seen where it doesn't work and must not be used. The bottom line is, "It Depends" on what the situation is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)