Where to place WITH (NOLOCK) ?

Question

Where to place WITH (NOLOCK) ?

BOR15K

SSCertifiable

Points: 5894
More actions
December 30, 2016 at 9:18 am

#339075

It might be obvious for all here, but I wonder where should I place NOLOCK, when several joins and outer joins are involved, please?
In my case I have something like SQL below and I wonder if having FROM myMainTable WITH (NOLOCK) will suffice or I should also have it next to other FROM's as well?
SELECT col1, col2,col3,col4,(SELECT col5 FROM Itinerary i WHERE i.col2 = myMainTable.col2)
FROM myMainTable WITH (NOLOCK)
JOIN (SELECT TOP 1 t.col1, t.col4
FROM Tickets t
JOIN Passengers p
ON t.ticket = p.ticket ) pax
ON myMainTable.col1 = pax.col1
Much appreciated

Viewing 15 posts - 1 through 15 (of 26 total)

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

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

Things like "WITH NOLOCK" are called "Table Hints". You'll need to put them next to each table you want to apply them to.

But note the drawbacks of NOLOCK - in that (a) it's misnamed - locks are still taken out - and (b) you may well see dirty data - not the right results.

Thomas Rushton
blog: https://thelonedba.wordpress.com

drew.allen SSC Guru Points: 76984 More actions · Answer 2

Unless you are okay with dirty reads, e.g., skipped records or repeated records, NOWHERE. WITH (NOLOCK) is not a magic "go faster" hint. It comes at a cost, and that cost is usually too steep.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246941 More actions · Answer 3

Further to Thomas' answer, the flippant answer given by many people here would be 'in the garbage'.

That's because if your table is being updated while a (NOLOCK) query is running on it, there is a chance that you will encounter

a) 'Ghost data', which does not exist in the table and never will (because a transaction was rolled back)

b) Double counting of data whose position in an index used by the query changes while the query is executing.

If you use the NOLOCK hint, you are accepting the above risks.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You also have a TOP 1 without ORDER BY which might return inconsistent results.

You have a correlated subquery that might return more than one row and would eventually return the following error:

Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I understand that you might be new to SQL, so avoid following those practices and make your code safe and reliable.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

BOR15K SSCertifiable Points: 5894 More actions · Answer 5

Thank you for the answers.

I am aware of the dirty data and in this case there will be no update of the data within the selected range, hence I want to use it

With regards to the TOP 1 I appreciate it might sound weird, but this what the customer require - to fetch the first value, disregarding the order, therefore there is no ORDER BY.

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 6

BOR15K (12/30/2016)
With regards to the TOP 1 I appreciate it might sound weird, but this what the customer require - to fetch the first value, disregarding the order, therefore there is no ORDER BY.

Advise the customer that the requirement won't be met by selecting TOP 1 because it won't necessary select the "first" value. Advise the customer that it could return "ANY" value.

BOR15K (12/30/2016)
I am aware of the dirty data and in this case [font="Arial Black"]there will be no update of the data within the selected range[/font], hence I want to use it.

There's absolutely no way that's guaranteed to be true because the TOP 1 doesn't have an ORDER BY associated with it.

--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)

BOR15K SSCertifiable Points: 5894 More actions · Answer 7

Jeff Moden (12/30/2016)
BOR15K (12/30/2016)
With regards to the TOP 1 I appreciate it might sound weird, but this what the customer require - to fetch the first value, disregarding the order, therefore there is no ORDER BY.
Advise the customer that the requirement won't be met by selecting TOP 1 because it won't necessary select the "first" value. Advise the customer that it could return "ANY" value.
BOR15K (12/30/2016)
I am aware of the dirty data and in this case [font="Arial Black"]there will be no update of the data within the selected range[/font], hence I want to use it.
There's absolutely no way that's guaranteed to be true because the TOP 1 doesn't have an ORDER BY associated with it.

I see everyone focuses on TOP 1, but in this case it is really unnecessary: the reason any value will suffice is because the customer has a 3rd party validation tool, which requires values for those columns, but in this specific case it is absolutely irrelevant for the customer. To make it more clearer, let's say the customer uses that validation tool to check the validity of the invoice's finance and it is irrelevant for them which itinerary has been used.

Hope it makes clearer a bit.

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 8

BOR15K (12/30/2016)
Jeff Moden (12/30/2016)
BOR15K (12/30/2016)
With regards to the TOP 1 I appreciate it might sound weird, but this what the customer require - to fetch the first value, disregarding the order, therefore there is no ORDER BY.
Advise the customer that the requirement won't be met by selecting TOP 1 because it won't necessary select the "first" value. Advise the customer that it could return "ANY" value.
BOR15K (12/30/2016)
I am aware of the dirty data and in this case [font="Arial Black"]there will be no update of the data within the selected range[/font], hence I want to use it.
There's absolutely no way that's guaranteed to be true because the TOP 1 doesn't have an ORDER BY associated with it.
I see everyone focuses on TOP 1, but in this case it is really unnecessary: the reason any value will suffice is because the customer has a 3rd party validation tool, which requires values for those columns, but in this specific case it is absolutely irrelevant for the customer. To make it more clearer, let's say the customer uses that validation tool to check the validity of the invoice's finance and it is irrelevant for them which itinerary has been used.
Hope it makes clearer a bit.

The other thing that you're missing is that it might always select the same row. That means that the validation tool would have a sample of 1, which is hardly a valid method of validation.

Since this is for validation, it's an even bigger reason to not take the chance on WITH(NOLOCK) returning the wrong data.

--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)

BOR15K SSCertifiable Points: 5894 More actions · Answer 9

Jeff Moden (12/30/2016)
BOR15K (12/30/2016)
Jeff Moden (12/30/2016)
BOR15K (12/30/2016)
With regards to the TOP 1 I appreciate it might sound weird, but this what the customer require - to fetch the first value, disregarding the order, therefore there is no ORDER BY.
Advise the customer that the requirement won't be met by selecting TOP 1 because it won't necessary select the "first" value. Advise the customer that it could return "ANY" value.
BOR15K (12/30/2016)
I am aware of the dirty data and in this case [font="Arial Black"]there will be no update of the data within the selected range[/font], hence I want to use it.
There's absolutely no way that's guaranteed to be true because the TOP 1 doesn't have an ORDER BY associated with it.
I see everyone focuses on TOP 1, but in this case it is really unnecessary: the reason any value will suffice is because the customer has a 3rd party validation tool, which requires values for those columns, but in this specific case it is absolutely irrelevant for the customer. To make it more clearer, let's say the customer uses that validation tool to check the validity of the invoice's finance and it is irrelevant for them which itinerary has been used.
Hope it makes clearer a bit.
The other thing that you're missing is that it might always select the same row. That means that the validation tool would have a sample of 1, which is hardly a valid method of validation.
Since this is for validation, it's an even bigger reason to not take the chance on WITH(NOLOCK) returning the wrong data.

Thank you Jeff,

But I think we have drifted way beyond my original question of where to place WITH (NOLOCK).

With regards to your concern it might always select a same row, I am sure this won't be a case,

as I have only used the above SELECT to simplify the matter and focus on my core question.

In real scenario there is an additional JOIN statement between myMainTable and a list of selected invoices, written into a temporary table, so myMainTable will only return

the data for required, processed invoices, which can be amended no more.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 10

BOR15K (12/30/2016)
...I am aware of the dirty data and in this case there will be no update of the data within the selected range, hence I want to use it...

Sorry to pile on here, but if there will be no updates of the data you're reading, then NOLOCK would seem moot. NOLOCK doesn't prevent this query from taking out any locks, it actually just tells the database engine to ignore other sessions locks on the data this session is querying. What problem do you think NOLOCK will fix in your environment?

https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

BOR15K SSCertifiable Points: 5894 More actions · Answer 11

Chris Harshman (12/30/2016)
BOR15K (12/30/2016)
...I am aware of the dirty data and in this case there will be no update of the data within the selected range, hence I want to use it...
Sorry to pile on here, but if there will be no updates of the data you're reading, then NOLOCK would seem moot. NOLOCK doesn't prevent this query from taking out any locks, it actually just tells the database engine to ignore other sessions locks on the data this session is querying. What problem do you think NOLOCK will fix in your environment?
https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

There is 4GL application, querying and writing (mostly) into myMainTable frequently and I do not want to lock it with my SELECT statement.

It doesn't update the columns I will be using though, but it has its own logic we cannot amend, when and how to lock the tables, so I want to reduce to a minimum

situations my current process in any way will prevent that 4GL product to place a lock on myMainTable when required. Would you use a different approach?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

Chris Harshman (12/30/2016)
BOR15K (12/30/2016)
...I am aware of the dirty data and in this case there will be no update of the data within the selected range, hence I want to use it...
Sorry to pile on here, but if there will be no updates of the data you're reading, then NOLOCK would seem moot. NOLOCK doesn't prevent this query from taking out any locks, it actually just tells the database engine to ignore other sessions locks on the data this session is querying. What problem do you think NOLOCK will fix in your environment?
https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

Curiosly enough, most people that advocate on the use of NOLOCK hints on every table, and confirm that they're aware of the problems of the hint, won't use the following instruction to save keystrokes.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

For me, that's an indication that the hint is not well understood.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 13

Phil Parkin (12/30/2016)
Further to Thomas' answer, the flippant answer given by many people here would be 'in the garbage'.

y'know, that was my first answer... but I censored myself before clicking on "Post"... 😉

Thomas Rushton
blog: https://thelonedba.wordpress.com

BOR15K SSCertifiable Points: 5894 More actions · Answer 14

ThomasRushton (12/30/2016)
Phil Parkin (12/30/2016)
Further to Thomas' answer, the flippant answer given by many people here would be 'in the garbage'.
y'know, that was my first answer... but I censored myself before clicking on "Post"... 😉

I appreciate lot's of people may have lots of ideas, hence my post here. What would you advise then to use, please? I haven't mentioned, but the select is a part of a stored procedure