Placement of WITH(NOLOCK) in nested queries

Question

In the Following query where would I place WITH(NOLOCK)?

SELECT *
FROM   (SELECT *
        FROM   (SELECT *
                FROM   (SELECT *
                        FROM   (SELECT *
                                FROM   dbo.VBsplit(@mnemonicList, ',')) a) b
                       JOIN dct
                         ON dct.concept = b.concept
                WHERE  b.geo = dct.geo) c
               JOIN dct_rel z
                 ON c.db_int = z.db_int) d
       JOIN rel_d y
         ON y.rel_id = d.rel_id
WHERE  y.update_status = 0
GROUP  BY y.rel_id,
          d.concept,
          d.geo_rfa

Nowhere. Why do you want to use `NOLOCK`? It is not equivalent to `WITH (TURBO)` it is equivalent to `WITH (I DO NOT CARE ABOUT ACCURACY)`. — Aaron Bertrand, Aug 16 '13 at 13:46
provide with the table description and definition also. Question not clear. — Veer Shrivastav, Aug 16 '13 at 13:46
@AaronBertrand Isn't with(NOLOCK) only dangerous if there is a chance of rollbacks? — , Aug 16 '13 at 13:49
why would you need to do such a thing? there are few cases in which you'd need to use `NOLOCK` and almost all the reasons not to use `NOLOCK`. first I would improve the query, by stop using so many `SELECT *`, second I would completle avoid using `FROM UDF()` it will save you lots, lots, of time — Luis LL, Aug 16 '13 at 13:51
@Timigen no, where did you hear that? With `NOLOCK` you can read the same row twice and you can skip rows altogether. Nothing to do with rollbacks at all. — Aaron Bertrand, Aug 16 '13 at 13:52
@Timigen there's a good question with a lot of points on this very site: http://stackoverflow.com/questions/1452996/is-the-nolock-sql-server-hint-bad-practice — Meff, Aug 16 '13 at 13:53
@AaronBertrand Wow I have seen it used a lot where I work all the time. I was actually told I should start using it in my queries. — , Aug 16 '13 at 13:53
@Timigen There is a widespread misconception that `NOLOCK` is essentially a turbo button that will remove all locking with no consequences, so it is widely (mis)used — JNK, Aug 16 '13 at 14:01
But notwithstanding that still an interesting question. I presume that if applied to a table expression containing multiple table references it gets applied to all of them but I'm not absolutely sure without testing. — Martin Smith, Aug 16 '13 at 14:04
Queue a very detailed @MartinSmith answer showing lock behavior on tables references in a table-valued function in 3... 2... (though I doubt in this case the function accesses any tables) — Aaron Bertrand, Aug 16 '13 at 14:28
@AaronBertrand - Unfortunately too busy at work at the moment to oblige! — Martin Smith, Aug 16 '13 at 14:51
I don't agree that (nolock) is a always bad. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. If you are not rolling back any transactions and need maximum concurrency nolock can be used effectively. — paparazzo, Aug 16 '13 at 15:59
@Blam I think the argument here is not that NOLOCK is always bad - I use READ UNCOMMITTED in some scenarios too. But I understand the scenarios. A user who doesn't know where NOLOCK goes is unlikely to comprehend the consequences of putting it anywhere. — Aaron Bertrand, Aug 16 '13 at 16:27
@LuisLL In my real query I dont use * in any of my select statements. What do you mean by FROM UDF()? — , Aug 16 '13 at 18:34
It really depends on the environment. I regularly use NOLOCK. The reason is because I am querying from a data warehouse that only gets modified nightly. I would not use it in a transactional environment. — Neil, Aug 16 '13 at 18:57

score 10 · Accepted Answer · edited May 23 '17 at 10:30

You should not put NOLOCK anywhere in that query. If you are trying to prevent readers from blocking writers, a much better alternative is READ COMMITTED SNAPSHOT. Of course, you should read about this, just like you should read about NOLOCK before blindly throwing it into your queries:

Also, since you're using SQL Server 2008, you should probably replace your VBSplit() function with a table-valued parameter - this will be much more efficient than splitting up a string, even if the function is baked in CLR as implied.

First, create a table type that can hold appropriate strings. I'm going to assume the list is guaranteed to be unique and no individual mnemonic word can be > 900 characters.

CREATE TYPE dbo.Strings AS TABLE(Word NVARCHAR(900) PRIMARY KEY);

Now, you can create a procedure that takes a parameter of this type, and which sets the isolation level of your choosing in one location:

CREATE PROCEDURE dbo.Whatever
  @Strings dbo.Strings READONLY
AS 
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL --<choose wisely>;

  SELECT -- please list your columns here instead of *
    FROM @Strings AS s
    INNER JOIN dbo.dct -- please always use proper schema prefix
    ON dct.concept = s.Word
    ...
END
GO

Now you can simply pass a collection (such as a DataTable) in from your app, be it C# or whatever, and not have to assemble or deconstruct a messy comma-separated list at all.

In addition to this, specifying "Set Transaction Isolation Level Read Committed/UnCommitted" is set for the entire CONNECTION, whereas setting nolock is only for that specific transaction. — ganders, Aug 16 '13 at 13:58
@ganders actually `NOLOCK` only applies to a specific **object** in the single transaction - You can `NOLOCK` one table in a `JOIN` but not the other(s) — JNK, Aug 16 '13 at 14:02
@ganders I believe that MSDN is actually wrong in this regard, and [`SET TRANSACTION ISOLATION LEVEL` is based on scope, not on connection.](http://stackoverflow.com/a/7507829/643591) — Michael Fredrickson, Aug 16 '13 at 17:35

SQLMason · Answer 2 · 2013-08-19T12:38:48.770

3

Since the question really is, "where should I put NOLOCK". I am not going do debate the use of OR reformat the query with better joins. I will just answer the question.

In no way am I intending to say this is the better way or to say that the other answers are bad. The other answer solve the actual problem. I'm just intending to show where exactly to place the lock hints as the question asks

SELECT *
FROM   (SELECT *
        FROM   (SELECT *
                FROM   (SELECT *
                        FROM   (SELECT *
                                FROM   dbo.VBsplit(@mnemonicList, ',')) a) b
                       JOIN dct WITH (NOLOCK) --        <---
                         ON dct.concept = b.concept
                WHERE  b.geo = dct.geo) c
               JOIN dct_rel z WITH (NOLOCK) --        <---
                 ON c.db_int = z.db_int) d
       JOIN rel_d y WITH (NOLOCK) --        <---
         ON y.rel_id = d.rel_id
WHERE  y.update_status = 0
GROUP  BY y.rel_id,
          d.concept,
          d.geo_rfa

edited Aug 19 '13 at 12:38

answered Aug 16 '13 at 14:48

SQLMason

3,275
1
30
40

2

Well, this can be pretty subjective. If the question were "how should I poison my neighbor's cat such that it isn't detected?" would you just answer the question or would you provide more useful advice (like "don't poison your neighbor's cat")? Anyway sticking to your strict adherence to the question, wouldn't it be better to enforce read uncommitted in one location (using `SET`) instead of repeating it for every table? And what if `dbo.VBSplit()` references tables, don't you want to apply `NOLOCK` there too? – Aaron Bertrand Aug 16 '13 at 14:51
3

@AaronBertrand that's not what the question is. Typically I would append a warning and reasoning why you shouldn't do this - however that has been well done by the other answers. The other answers didn't actually answer the question - which is, "where should I put..." – SQLMason Aug 16 '13 at 16:08
3

@AaronBertrand if the question was, "How do you start a car?" would you answer it with, "well... you shouldn't really be driving, you should take a bus. It's better for the economy, environment, and you'll spend less on insurance and maintenance." You never really answered the question. – SQLMason Aug 16 '13 at 16:11
1

I don't think your analogy is any more valid than mine. Starting a car isn't dangerous (unless there is a bomb). With NOLOCK, we know of the dangers, and can advise otherwise. To match your analogy we would have had to be suggesting "stop querying databases and go buy some Lego." – Aaron Bertrand Aug 16 '13 at 16:26
I don't think that using NOLOCK is as dangerous as poisoning your neighbor's cat. However if using NOLOCK would stop my neighbors dog from barking at night, I'd use it religiously. I've never heard of animals dying for using NOLOCK... and the question is, "where should I place NOLOCK..." not, "should I use NOLOCK". – SQLMason Aug 16 '13 at 17:06
1

Seems the community doesn't share your enthusiasm about just telling the user how to shoot their own foot off. And you failed to address my other points: not only have you implied that it is ok to go ahead and shoot their foot off, you've shown the most painful way to do so, and also completely ignored any table access that might happen inside the function. So no, you haven't convinced me that just answering the exact question is the way to go here. – Aaron Bertrand Aug 16 '13 at 17:17
Also as to "you never really answered the question" did you miss this part: "and which *sets the isolation level of your choosing in one location*"? The whole point of that was to avoid peppering `NOLOCK` throughout all of the individual tables referenced in the queries, and also to still affect any potential table access inside the function. – Aaron Bertrand Aug 16 '13 at 17:27
@DanAndrews can you suggest better joins? – Aug 16 '13 at 18:45
What columns do you really need from the select? – SQLMason Aug 16 '13 at 19:53
@AaronBertrand I state in the first line of the post that I'm just going to answer the question and not debate the usage. I'm sorry that you think I shouldn't show someone where to put query hints in their query. I'm confident that the sun will rise tomorrow. – SQLMason Aug 16 '13 at 19:56
@Dan I never said you shouldn't show them. My original comment was precisely about your opening line, which seems to be a big middle finger to those of us who chose to guide the user a little better than just answering the question, period. And all I said about that was that it was subjective. You still have completely ignored the other points I've made about the technical merit of the approach you've suggested, so *shrug*. – Aaron Bertrand Aug 17 '13 at 01:13
@AaronBertrand - ah, there's the problem. I didn't intend to do that or mean it that way. I was just being a matter of fact. I was trying to credit the other answers for explaining it well. I was only trying to show where exactly to put the hints. No offense intended. – SQLMason Aug 19 '13 at 12:23

Eric Hauenstein · Answer 3 · 2013-08-16T14:31:29.423

Like this, to use the tidiest method.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM (SELECT * FROM 
(SELECT * FROM (SELECT * FROM 
(SELECT * FROM dbo.VBsplit(@mnemonicList,',')) a ) b 
JOIN dct ON dct.concept = b.concept WHERE b.geo = dct_variable.geo_rfa) c
JOIN dct_rel z ON c.db_int = z.db_int) d
JOIN rel_d y ON y.rel_id = d.rel_id
WHERE y.update_status = 0
GROUP BY y.rel_id,d.concept,d.geo_rfa
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

However, unless you are using this for reporting purposes on an active database, enabling dirty reads may not be the best way to go.

Edited as (NOLOCK) itself is not deprecated except as described here: http://technet.microsoft.com/en-us/library/ms143729.aspx.

Placement of WITH(NOLOCK) in nested queries

3 Answers3