x

Use of SQL NOLOCK hint in the select query. Yes or NO.

Should "NOLOCK" hint be used with select statements? We have a table in our application which has a data getting modified frequently by different threads. So is it required to have "NOLOCK" hint on the select queries executed on this table?

Syntax: select subject from x_table WITH(NOLOCK)

more ▼

asked Mar 26, 2010 at 02:45 AM in Default

Snehal gravatar image

Snehal
81 1 1 1

Nice question. Gets a +1
Mar 26, 2010 at 03:03 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Feel free, just so long as you're fine about the idea of dirty reads.

For example - you want to count the rows in a table. The system uses the smallest index there is, but there are updates going on by your other threads. Using NOLOCK, you will read some rows twice, and some not at all... like trying to read through a phone book while someone's rearranging the names.

more ▼

answered Mar 26, 2010 at 03:59 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

(Great question though - I wish more people asked it, instead of just using NOLOCK all the time without thinking about the consequences)
Mar 26, 2010 at 03:59 AM Rob Farley
Excellent. Most people don't mention the fact that you can have multiple or missing rows due to NOLOCK.
Mar 26, 2010 at 09:07 AM Grant Fritchey ♦♦
I use it all the time. It can be a huge enhancement to effeciency, especially in an environment where lots of locks ar eheld for long periods of time. But as Rob said, remember the risk of dirty reads. If you can accept that in your case, then it can be a true blessing for performance, but you should not use it blindly without carefully thinking through if you can really accept the risk of dirty reads.
Mar 26, 2010 at 01:55 PM TimothyAWiseman
Most excellent reply.
Mar 26, 2010 at 03:03 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

You should also have a look at switching to READ_COMMITTED_SNAPSHOT with row-level versioning, which you can read about here: http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx. It means you get a consistent view for the duration of your SELECT without being blocked by in-flight updates and without locking the rows you're reading. No dirty reads but you can end up reading a row that is being deleted by another transaction.

It does add some tempdb overhead but in my experience this is minimal. Your mileage may vary as they say, so you should test the effects on your system. It's quick and easy to switch on or off though, so if it causes problems you can quickly disable it again. And it ignores any NOLOCK hints so you don't have to change those queries to try it.

more ▼

answered Mar 26, 2010 at 04:18 AM

David Wimbush gravatar image

David Wimbush
5k 29 30 34

(comments are locked)
10|1200 characters needed characters left

My answer would be, "It Depends but Usually no, No, NO, OH HELL NO!" ;-) for all the reasons that Rob mentioned. The reason why most people use it is to have queries run that aren't blocked by others and that's the real problem. Cruddy code causing blocking by others. The best thing to do would be to find the code that's doing the blocking and make it run faster, smarter, and with fewer resources. Yeah, yeah... I've heard all the excuses... "It would be too expensive"... "It would take too long"... "It would whatever".

And, no, it's not an enhancement to efficiency... it's a way to ignore horribly ineffecient code.

Just wait until some critical piece of data is either missing or doubled up that causes a contract to be lost, a critical report to a government agency being incorrect and the company gets fined for it, or someone gets fired because it looks like you actually have enough manpower on the manpower report. Now, THAT's going to be expensive. ;-)

It also saves on the costs of new servers which include a bunch of software licenses, cooling, maintenance, additional hard disk space, additional server room space (even if it's just a single blade), additional UPS/cabling, additional installation cost, additional etc....

Heh... stop putting bandaids on stab wounds... fix the code. ;-)

more ▼

answered May 14, 2010 at 10:05 PM

Jeff Moden gravatar image

Jeff Moden
1.8k 3 4 8

Fix the code :-) Well, in a DW scenario where all the data modifications are done during the night timeframe (for example 11pm to 4am) can very well have NOLOCK hint added to the queries due to concurrency situtations during the office hours.
May 15, 2010 at 06:39 AM Peso
Heh... what would actually cause a concurrency issue on well written code such that NOLOCK would be needed in that case, Peter?
May 15, 2010 at 10:22 AM Jeff Moden
Concurrency for a lot of JOINS. For a few years ago we could increase the number of concurrent users from about 200 to about 4000 concurrent users.
May 16, 2010 at 03:26 PM Peso
I guess I don't understand. SELECTs don't cause blocking. INSERTs only cause blocking on the rows being inserted and no one looks for those until they've been inserted. Only DELETEs and UPDATEs cause the type of blocking that would require NOLOCK and those types of issues are solved by (like I said) writing better code using "Divide'n'Conquer" methods so transactions are very short. Yes, NOLOCK solves those problem, as well, but can you really tolerate dirty reads especially when a split or a rollback occurs? I generally can't tolerate dirty reads... especially in a DW. – Jeff Moden 0 secs ago
May 23, 2010 at 11:28 PM Jeff Moden
Where can I see some examples of "divide and conquer" methods, and keeping transactions short? How does this apply to complex selects across many tables?
Sep 21, 2010 at 05:15 AM GPO
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x39

asked: Mar 26, 2010 at 02:45 AM

Seen: 4043 times

Last Updated: Mar 26, 2010 at 04:00 AM