Update nolock

Login to reply. February 22, at am February 22, at pm February 24, at pm And for future reference-- looking at the execution plan should show they are identical. Viewing 5 posts - 1 through 4 of 4 total.

This depends on your definition of "okay" and, more importantly, how future-proof you want your code to be. So code that uses this hint is implying some sort of non-blocking behavior that doesn't actually happen and, frankly, isn't possible, when you think about it. I ran some tests on SQL Server and confirmed that the locking behavior at least according to sys. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

Nobody wants to carry forward code that has the potential to break through no other change than an upgrade. So if you are in a shop that waits for full service packs, you may be vulnerable to this issue until SQL Server Service Pack 2 is released and I am fairly confident that will happen long before a new service pack is released for or R2, which hits end of mainstream support later this year. As a broad first attempt to identify this pattern in your code, I'll offer this query, with the disclaimer that it is very likely to produce false positives, won't necessarily identify UPDATE statements that are built with dynamic SQL, and obviously won't find any offending statements in external applications.

I find nolock useful occassionally in some situations dealing witha busy table for simple selects. If you notice below the Suffix column now has "B" for all records. If the UPDATE is rolled back the data will revert back to what it looked like before, so this is considered a Dirty Read because this data may or may not exist depending on the final outcome in query window 1. So the issue with using the NOLOCK hint is that there is the possibility of reading data that has been changed, but not yet committed to the database.

If you are running reports and do not care if the data might be off then this is not an issue, but if you are creating transactions where the data needs to be in a consistent state you can see how the NOLOCK hint could return false data.

Here is an example. These are also other terms you may encounter for this hint. This way you can set a whole batch of statements instead of modifying each query. Thanks for the input. As you mentioned, Microsoft lists this as a hint in their list of hints. The goal of this tip was to show people the danger of using nolock in production and the issue with dirty reads. I think there is a place for using this, but I agree people need to know that the drawbacks can outweigh the benefits.

I don't think everyone understands how this works and the downsides. It is a command. Nothing provides the optimizer with the choice of ignoring the hint and moving along on its happy way, totally oblivious to the help the developer so thoughtfully provided.

MS documentation might regard it as a hint but in working software it is anything but. For those of us with some multiple decades of experience the coding choice is more obvious. Newer developers need to understand the odds against them arguing with the optimizer and winning.

Portraying NOLOCK as anything less than a command gives the casual reader comfort in a place where they should instead exercise extreme caution. I tried the examples with SQL Server and they still work the same. Or just do an internet search. You can find this for different SQL Server versions in various places. Nice article, indeed! I wonder if there is a way to do select which returns the data before the other session started the transaction without beeing locked by that session.

Thanks and regards. Nice one Greg Robidoux!!! There is a case for me calling web service from SQL server Maybe there are ways known to a select few that allow implementing the hint without any adverse consequences.

You can use Profiler or a server side trace to look for long running queries. I usually start with things that run over ms. From there review the query plan and see if you can make these run faster either by reworking the query or adding indexes.

Also, check the system wait stats to make sure the issue is not something SQL related. How to improve my database performance speed? Ramesh - a nonreaptable read is when you need to read the same data more than once and the data changes during the process. I don't have R2 installed. If I run the repro-script on , then I do not get to see the bug.

I suspect that the bug submitter got lost in his own repro. Also, the problem is that the phrase [1]. I did purposely changed the repro.

As I said, in my original post, I think the submitter got lost in his repros. And the statement that blocked was:. Just click the link you need. If it is ignored then why. This may be caused by a conflicting hint specified for a view"? It is not clear which behavior is really indended to be correct one the actual one, the described one or the third one. I guess the checks for conflicting locks happens at an earlier state, when the query is compiled.

BOL describe a feature which is inconsistently realized. Obviously someone messed up. The correct behaviour would have been that using NOLOCK for the target table anywhere in the query would have yielded an error. But they failed to check in the compilation module, and then the error was ignored silently ignored at run-time.