Using NOLOCK in UPDATE or DELETE

Question

Using NOLOCK in UPDATE or DELETE

Viewing 15 posts - 16 through 30 (of 47 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Grant Fritchey wrote:

ScottPletcher wrote:
Thus, the only real issue is dirty reads. So, again, if dirty reads would cause you a problem, DON'T use NOLOCK. If not, you can consider it.
But...
The biggest issue with dirty reads is how they work. Lots and lots of people think about the classic 'cat' or 'dog' example. "Well, we're updating 'cat' to 'dog' but while that update occurs, some people might see 'cat'"
If that was all a dirty read was, cool. No big deal. However, dirty reads can lead to missing rows and duplicate rows. Not simply 'cat' or 'dog', but literally variable amounts of data, some possibly duplicate, some possibly missing.
Now, how bad are dirty reads? A lot worse. A whole lot worse.
Add to the fact that most people who are painting their code with NOLOCK already have very bad code and very bad structures, both of which lead to lots of scans, the main place where dirty reads are going to miss, duplicate, or otherwise muck with entire rows of data, and suddenly the NOLOCK problem is much worse. Telling people who have horrific code and structure problems that there's a magic bullet that fixes everything is where we get companies that require NOLOCK in every query (and yeah, run across this one many times now). Education on NOLOCK must be brutally clear that, more often than not, it's being abused.
Are there uses for NOLOCK?
Yes, absolutely. However, like any query hint, they should be used rarely, with a lot of planning, knowledge, and testing.

g

Quite true, I agree. Although the vast majority of the dirty reads that remove/duplicate rows are, as I understand it, from allocation scans, which you can prevent. Certainly don't use NOLOCK on highly volatile data, that's just asking for trouble.

As to really bad structures, horrible table (non)designs are far too common, what with developers often "designing" their own (so-called) "tables", with NO normalization, just slapping identity keys on every table. That's a bigger problem than NOLOCK, and it's getting worse. All developers assume they are data DBAs too -- better than the actual DBAs, in fact -- and they just aren't qualified for that, not even remotely close.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

Grant Fritchey wrote:

Jonathan AC Roberts wrote:
Grant Fritchey wrote:
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
So NOLOCK would take effect in the following example:
DELETE spqh
  FROM Sales.SalesPersonQuotaHistory AS spqh
 INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
         ON spqh.BusinessEntityID = sp.BusinessEntityID
 WHERE sp.SalesYTD > 2500000.00;
As the table Sales.SalesPerson is not the target table.
I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.
There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.
Actually no, according to the documentation, testing, and only excepted by a few odd bugs here & there, NOLOCK is just ignored in an UPDATE query. That's what the documentation says up front. Further, it even warns that since this is non-standard coding practices, at some point in the future they'll be removed. Now, I know that's unlikely (go and look at the deprecation list, nothing ever gets removed any more). However, you're writing code that won't do anything (unless you hit one of the bugs). Why bother?

This is the text from the BOL:

READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

There is nothing about it ignoring nolock on all tables in a UPDATE (only the target table).

ScottPletcher wrote:

But back now to the original reason to use NOLOCK in the first place:
NOLOCK avoids taking locks, which IS more efficient than taking locks. That's kinda self-explanatory, really, right?

I've always looked on NOLOCK as meaning "IGNORE LOCKS" i.e. it ignores locks on the table that are from other processes, not that it doesn't create any locks.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

Jonathan AC Roberts wrote:

Grant Fritchey wrote:
Jonathan AC Roberts wrote:
Grant Fritchey wrote:
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
So NOLOCK would take effect in the following example:
DELETE spqh
  FROM Sales.SalesPersonQuotaHistory AS spqh
 INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
         ON spqh.BusinessEntityID = sp.BusinessEntityID
 WHERE sp.SalesYTD > 2500000.00;
As the table Sales.SalesPerson is not the target table.
I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.
There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.
Actually no, according to the documentation, testing, and only excepted by a few odd bugs here & there, NOLOCK is just ignored in an UPDATE query. That's what the documentation says up front. Further, it even warns that since this is non-standard coding practices, at some point in the future they'll be removed. Now, I know that's unlikely (go and look at the deprecation list, nothing ever gets removed any more). However, you're writing code that won't do anything (unless you hit one of the bugs). Why bother?
This is the text from the BOL:
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
There is nothing about it ignoring nolock on all tables in a UPDATE (only the target table).
ScottPletcher wrote:
But back now to the original reason to use NOLOCK in the first place:
NOLOCK avoids taking locks, which IS more efficient than taking locks. That's kinda self-explanatory, really, right?
I've always looked on NOLOCK as meaning "IGNORE LOCKS" i.e. it ignores locks on the table that are from other processes, not that it doesn't create any locks.

I think it ignores other locks by not taking locks itself (hence the term "NOLOCK" :-)).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

my take on this is " if you need to ask if NOLOCK should/can be used on a particular case then you do not know about it's issues and therefore you should not use it at all"

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

ScottPletcher wrote:

Jonathan AC Roberts wrote:
Grant Fritchey wrote:
Jonathan AC Roberts wrote:
Grant Fritchey wrote:
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
So NOLOCK would take effect in the following example:
DELETE spqh
  FROM Sales.SalesPersonQuotaHistory AS spqh
 INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
         ON spqh.BusinessEntityID = sp.BusinessEntityID
 WHERE sp.SalesYTD > 2500000.00;
As the table Sales.SalesPerson is not the target table.
I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.
There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.
Actually no, according to the documentation, testing, and only excepted by a few odd bugs here & there, NOLOCK is just ignored in an UPDATE query. That's what the documentation says up front. Further, it even warns that since this is non-standard coding practices, at some point in the future they'll be removed. Now, I know that's unlikely (go and look at the deprecation list, nothing ever gets removed any more). However, you're writing code that won't do anything (unless you hit one of the bugs). Why bother?
This is the text from the BOL:
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
There is nothing about it ignoring nolock on all tables in a UPDATE (only the target table).
ScottPletcher wrote:
But back now to the original reason to use NOLOCK in the first place:
NOLOCK avoids taking locks, which IS more efficient than taking locks. That's kinda self-explanatory, really, right?
I've always looked on NOLOCK as meaning "IGNORE LOCKS" i.e. it ignores locks on the table that are from other processes, not that it doesn't create any locks.
I think it ignores other locks by not taking locks itself (hence the term "NOLOCK" :-)).

Yes, I found the documentation. It it does both (no take shared locks and so ignore locks from other processes):

Exclusive Locks

Exclusive (X) locks prevent access to a resource by concurrent transactions. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.

Data modification statements, such as INSERT, UPDATE, and DELETE combine both modification and read operations. The statement first performs read operations to acquire data before performing the required modification operations. Data modification statements, therefore, typically request both shared locks and exclusive locks. For example, an UPDATE statement might modify rows in one table based on a join with another table. In this case, the UPDATE statement requests shared locks on the rows read in the join table in addition to requesting exclusive locks on the updated rows.

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#exclusive

Locking Hints

The SQL Server Database Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for share locks when reading data. For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. This means it is possible for a SELECT statement using NOLOCK to be blocked.

https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#locking-hints

I think the reason it is often faster though is because it's ignoring locks from other processes rather than the time it takes to lock the table/rows.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

Jonathan AC Roberts wrote:

ScottPletcher wrote:
Jonathan AC Roberts wrote:
Grant Fritchey wrote:
Jonathan AC Roberts wrote:
Grant Fritchey wrote:
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
So NOLOCK would take effect in the following example:
DELETE spqh
  FROM Sales.SalesPersonQuotaHistory AS spqh
 INNER JOIN Sales.SalesPerson WITH (NOLOCK) AS sp
         ON spqh.BusinessEntityID = sp.BusinessEntityID
 WHERE sp.SalesYTD > 2500000.00;
As the table Sales.SalesPerson is not the target table.
I'm not sure why you would want to do this though (I wouldn't). Maybe the table Sales.SalesPerson is locked by another session preventing read and you don't really care if the value is taken before or after the other process has locked it.
There are also other issues that could lead to errors, for example you could query the Sales.SalesPerson table midway though a page split and get 2 rows back when there is really only 1, this wouldn't affect the query above but it could cause serious errors on other queries.
Actually no, according to the documentation, testing, and only excepted by a few odd bugs here & there, NOLOCK is just ignored in an UPDATE query. That's what the documentation says up front. Further, it even warns that since this is non-standard coding practices, at some point in the future they'll be removed. Now, I know that's unlikely (go and look at the deprecation list, nothing ever gets removed any more). However, you're writing code that won't do anything (unless you hit one of the bugs). Why bother?
This is the text from the BOL:
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
There is nothing about it ignoring nolock on all tables in a UPDATE (only the target table).
ScottPletcher wrote:
But back now to the original reason to use NOLOCK in the first place:
NOLOCK avoids taking locks, which IS more efficient than taking locks. That's kinda self-explanatory, really, right?
I've always looked on NOLOCK as meaning "IGNORE LOCKS" i.e. it ignores locks on the table that are from other processes, not that it doesn't create any locks.
I think it ignores other locks by not taking locks itself (hence the term "NOLOCK" :-)).
Yes, I found the documentation. It it does both (no take shared locks and so ignore locks from other processes):
Exclusive Locks
Exclusive (X) locks prevent access to a resource by concurrent transactions. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.
Data modification statements, such as INSERT, UPDATE, and DELETE combine both modification and read operations. The statement first performs read operations to acquire data before performing the required modification operations. Data modification statements, therefore, typically request both shared locks and exclusive locks. For example, an UPDATE statement might modify rows in one table based on a join with another table. In this case, the UPDATE statement requests shared locks on the rows read in the join table in addition to requesting exclusive locks on the updated rows.
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#exclusive
Locking Hints
The SQL Server Database Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for share locks when reading data. For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. This means it is possible for a SELECT statement using NOLOCK to be blocked.
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#locking-hints
I think the reason it is often faster though is because it's ignoring locks from other processes rather than the time it takes to lock the table/rows.

It's both reasons that it's faster. To pretend that the shared locks on rows don't take any time is just not true. It may be small overhead, but it IS still overhead. I'm sorry, but it bothers me when people try to say that "NOLOCK does not realy save anything anyway" is just not true. If you're opposed to ANY use of NOLOCK, fine, just state that, but don't over-claim trying to support it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

ScottPletcher wrote:

It's both reasons that it's faster. To pretend that the shared locks on rows don't take any time is just not true. It may be small overhead, but it IS still overhead. I'm sorry, but it bothers me when people try to say that "NOLOCK does not realy save anything anyway" is just not true. If you're opposed to ANY use of NOLOCK, fine, just state that, but don't over-claim trying to support it.

I hope all that isn't directed at me.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

Jonathan AC Roberts wrote:

ScottPletcher wrote:
It's both reasons that it's faster. To pretend that the shared locks on rows don't take any time is just not true. It may be small overhead, but it IS still overhead. I'm sorry, but it bothers me when people try to say that "NOLOCK does not realy save anything anyway" is just not true. If you're opposed to ANY use of NOLOCK, fine, just state that, but don't over-claim trying to support it.
I hope all that isn't directed at me.

Absolutely not, sorry. It's just that I see different people over and over say "NOLOCK doesn't gain anything at all any way". And that's just not true. Don't get me wrong, you can still be opposed to it for dirty-reads reasons, but that doesn't mean it doesn't reduce overhead some, because it does.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 9

Wow... Thank you so much for such insights.

However, using a hint in the secondary table or in my case on the joining table can still be okay? since all the articles talk about the target table and not secondary ones.

sgmunson SSC Guru Points: 110640 More actions · Answer 10

khushbu wrote:

Wow... Thank you so much for such insights.
However, using a hint in the secondary table or in my case on the joining table can still be okay? since all the articles talk about the target table and not secondary ones.

While allowed, there are still the opportunities for dirty reads, so again, why would you want to from within an UPDATE? If you really and truly fully understand what NOLOCK does, you can certainly use it, but you have to be willing to accept the potential consequences, which in an UPDATE, could be severe. In other words, BUYER BEWARE...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

pdanes SSCrazy Eights Points: 8495 More actions · Answer 11

Grant Fritchey wrote:

... we get companies that require NOLOCK in every query (and yeah, run across this one many times now).

Seriously? An official, company-wide mandate to DB coders to use this? That sounds like right out of a Dilbert cartoon.

This reply was modified 4 years, 2 months ago by pdanes.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

khushbu wrote:

Any reference link I can refer too and make my point?

Frederico provided one in his post... the very first post after your question.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

pdanes wrote:

Grant Fritchey wrote:
... we get companies that require NOLOCK in every query (and yeah, run across this one many times now).
Seriously? An official, company-wide mandate to DB coders to use this? That sounds like right out of a Dilbert cartoon.

The comparison you made is highly appropriate but, yeah, I've worked for companies that had such a ridiculous mandate. In all but one case, some consultant told them to do that. They also told them to use Temp Tables and While loops instead of cursors <headdesk>.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

At this point, it's almost a dead issue but I have to agree with Scott. If you want to say that using WITH (NOLOCK) or setting the transaction isolation level to Read Uncommitted at the beginning of proc is a bad thing, that's fine but don't oversell it by saying it provides no benefit because not only does it provide a performance benefit, especially in the fact of contention with writes, people that have tried it already know that it actually can be a "go faster"button and by claiming it's not, you've just destroyed any credibility you have and people will ignore your other advice and warnings even if they're entirely spot on.

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 15

pdanes wrote:

Grant Fritchey wrote:
... we get companies that require NOLOCK in every query (and yeah, run across this one many times now).
Seriously? An official, company-wide mandate to DB coders to use this? That sounds like right out of a Dilbert cartoon.

No arguments from me, but I'm telling you, it's shockingly common.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning