Wat are the disadvantages of SELECT ... WITH (NOLOCK)?
-
Hello everybody,
Recently one of our our applications experiences some deadlocks and locks, resulting in slower performance. Some of these locks were eliminated by better transaction management. Some remain due to complex timeconsuming queries and lots of insert, update and delete statements.
My colleagues now want to add WITH (NOLOCK) to every SELECT statement. Is this wise?
Greetings,
Gerry S.
Dutch Anti-RBAR League -
'It depends'
If.... it is ok by the business rules that the SELECT statment may show data that are in transit - ie 'logically' doesn't yet exist, and as such may not never have existed, or be there the next time you look in the same place... then yes, it may be wise.. or perhaps 'necessary' is a better word.
The 'best' way is of course to design and build a system that can handle the required concurrency load, but... sure, there are situations when it's perfectly legal to do 'dirty reads' on purpose.
If this is the case here, I don't know. Only you who know the system and it's purpose can tell. For your colleagues, make sure that they understand what they are proposing as well - ie what are the consequences of (NOLOCK) - not just the technical aspect, but more important, the business aspect. Is it possible that something 'bad' may come out of the system if you use the option?
Think about that, and the answer will guide you to if it's a wise choice or not
/Kenneth
-
From Books Online
NOLOCK Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement. ...
READUNCOMMITTED Equivalent to NOLOCK.
-
Here's another option (with obvious drawbacks).
READPAST Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
Edit: Better to have no data than inacccurate data!
The snapshot isolation coming in sql 2005 may provide a better solution.
-
>My colleagues now want to add WITH (NOLOCK) to every SELECT statement. Is this wise?
Why does your colleagues want to use an RDBMS, or even a transaction-management system, at all? The main concern for an RDBMS is to maintain the integrity of the data stored in it. Users reading uncommitted transactions and possibly basing actions on that information will inevitably lead to data corruption and lost integrity.
-
Thank you all for your answers.
I am aware of some of the mentioned issues involved with using WITH (NOLOCK). And I do warn my colleagues not to use this option as a Golden Hammer.
Summarizing: do NOT use unless the developer is certain that reading uncommited transactions will have no drawbacks. And I gather from Chris' words that this will NOT happen often.
In fact, I know only 1 example: A report which shows total items ordered, items picked, items shipped and items invoiced per day. It was meant as a king of progress meter.
OK, now I will try to convince my colleagues
Thanks again!
Dutch Anti-RBAR League -
Scenario: The company owners are facing a tough decision. If the company has shipped more than X items today they will sell the company, otherwise not. They look at the report and see that X+1 items has been shipped, so they sell. However, as it turns out the report was generated while a transaction that added 2 rows to the items shipped table was executing. This transaction was then rolled back, so not the number of items shipped is actually X-1, so the decision to sell was based on incorrect information.
The effects here are of course a bit unrealistic, but when using information that does not exist (which is really the case for outside viewers until a transaction has committed) data corruption and loss of integrity is bound to happen. What these lead to depends on when the problems occur, but if the non-existent information is used for basing decisions then we are really in trouble.
-
I disagree with the notion that dirty reads is inherently evil - it's not. Also, doing it cannot corrupt data in any way - since it's a read op, not write.
However, what dictates if dirty reads could be used, is the business, the nature of the data viewed and the purpose for viewing it. If it serves as grounds for critical desicions, then no, it's NOT a good idea. On the other hand, if the purpose is to view trends, overviews or any other purpose that does not require exact data, but an 'average' will do, then it may be perfectly legal.
Again, it depends.
It is however wise to be aware when it's done and why. Also, never take for granted that the end-user (ie the consumer of a 'dirty-read dataset') knows that it is not-necessarily-accurate at all times. That is another problem.
A Golden Hammer it's not! With wisdom it can be a practical tool.
/Kenneth
-
Having a WITH (NOLOCK) to every SELECT statement is not a good idea and that to for every select statement.
I have billing related applications where every transaction depends upon the other transactions. So if the data is dirty, my other transactions will have wrong data b'coz it is based on the earlier reads.
In reports, management makes decisions on what report we present them. And if there is dirty data, decision would be wrong. Rather than having susch solutions, its better to have alternates like indexing some columns so that data can be fetched faster.
--Kishore
-
... its better to have alternates like indexing some columns so that data can be fetched faster.
I think Kishore has a good point. Maybe the better solution is retry to solve the problem by addressing the performance issues. I understand that some of that has already been done but perhaps putting more resources on the performance problem is the best solution.
-- ron, who still believes: Better to have no data than inaccurate data.
-
I totally agree with ron... it's better to have less data that is accurate than all the data that might be...
-
This is a tricky question in which there are no Absolutes. The right anwser is "it depends on the application". I bet none of us would like to see the wrong balace on our bank account (when is less of course
). Nor would like to get a pay stub with less amount in it On the other hand I coudn't care less if I am reading the total meter reading that are happening on the day alittle bit more or a little bit less on a data logger system. Nor couldn't care less if the total number of transactions on my server are skewed by +2% or by -2% difference from the real values
Just my $0.02
* Noel -
Gentlemen,
Of course we won't forget improving the performance. But as the customers add wishes and need more details, the number of tables in frequently used queries is growing. And growing still... And thus the performance decreases, resulting in the mentioned locks and deadlocks.
Question: quite a few tables in those queries are lookup tables, rarely modified. Would it help to use WITH (NOLOCK) for these tables.
?
Dutch Anti-RBAR League -
If they are not modified then there will not be any locks on them, so I see no reason.
-
...Question: quite a few tables in those queries are lookup tables, rarely modified. Would it help to use WITH (NOLOCK) for these tables.
I'm not sure but my guess is it would not help since (except for the occasional update) these tables only have shared locks and are not the cause of your deadlocks.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply