Using NOLOCK in UPDATE or DELETE

Question

Using NOLOCK in UPDATE or DELETE

Viewing 15 posts - 31 through 45 (of 47 total)

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

MMartin1 One Orange Chip Points: 27782 More actions · Answer 1

ScottPletcher wrote:

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.

I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard shared lock? If getting into the habit of just doing what someone else suggests without analysing , lead to bad coding in other areas?

I would think to use NOLOCK on a table that IS highly volatile such as if I am just testing if I can connect and pull data (security related) from a table and don't want to hinder any processes there.

----------------------------------------------------

MMartin1 One Orange Chip Points: 27782 More actions · Answer 2

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" :-)).

From BOL >

"No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data."

So it appears both are true.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 3

frederico_fonseca wrote:

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"

+ 1 million... best advice I've seen on the subject for a long time.

Like Granny used to say... "Never put your lips on the town pump". 😀

Heh... or like Dwain Camps (rest in peace, old friend and fellow SQL warrior) once said... "... is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat 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)

ScottPletcher SSC Guru Points: 100950 More actions · Answer 4

MMartin1 wrote:

I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard shared lock?

Yes, it can be. NOLOCK can prevent other tasks from deadlocking.

The jihad against NOLOCK is somewhat overdone. The only major difference between NOLOCK and READ COMMITTED (RC) is that modified data can be read vs not read (you can mitigate an additional scan issue with a server setting). RC still does allow non-repeatable reads and phantom reads, just like NOLOCK does.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

x SSC-Insane Points: 23660 More actions · Answer 5

ScottPletcher wrote:

MMartin1 wrote:
I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard shared lock?
Yes, it can be. NOLOCK can prevent other tasks from deadlocking.
The jihad against NOLOCK is somewhat overdone. The only major difference between NOLOCK and READ COMMITTED (RC) is that modified can be read vs not read (you can mitigate an additional scan issue with a server setting). RC still does allow non-repeatable reads and phantom reads, just like NOLOCK does.

So whats the server setting? Apologies if its already been posted. I have stacks of nolock code (not my idea) and would be open to any improvements.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 6

x wrote:

ScottPletcher wrote:
MMartin1 wrote:
I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard shared lock?
Yes, it can be. NOLOCK can prevent other tasks from deadlocking.
The jihad against NOLOCK is somewhat overdone. The only major difference between NOLOCK and READ COMMITTED (RC) is that modified can be read vs not read (you can mitigate an additional scan issue with a server setting). RC still does allow non-repeatable reads and phantom reads, just like NOLOCK does.
So whats the server setting? Apologies if its already been posted. I have stacks of nolock code (not my idea) and would be open to any improvements.

I wouldn't necessarily recommend that you leave this in place -- then again, you might want to review that possibility for yourself, depending on how much you want to use NOLOCK -- but for specific cases when you really want to use NOLOCK but not have to deal with allocation scans (which can indeed cause bad-data issues with NOLOCK not seen with RC), then you can run:

EXEC sys.sp_configure 'cursor threshold', 5000; --<<-- anything other than -1 will stop all allocation scans across the instance:

-- MS states that you need to leave "small" sets to be sync; as a rough base, I consider 5,000 rows or less a "small" set.

RECONFIGURE;

Once you've processed your critical NOLOCK query(ies), you can reset the threshold:

EXEC sys.sp_configure 'cursor threshold', -1;

RECONFIGURE;

Ref (Paul White): https://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

x SSC-Insane Points: 23660 More actions · Answer 7

ScottPletcher wrote:

x wrote:

ScottPletcher wrote:
MMartin1 wrote:
I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard shared lock?
Yes, it can be. NOLOCK can prevent other tasks from deadlocking.
The jihad against NOLOCK is somewhat overdone. The only major difference between NOLOCK and READ COMMITTED (RC) is that modified can be read vs not read (you can mitigate an additional scan issue with a server setting). RC still does allow non-repeatable reads and phantom reads, just like NOLOCK does.
So whats the server setting? Apologies if its already been posted. I have stacks of nolock code (not my idea) and would be open to any improvements.

I wouldn't necessarily recommend that you leave this in place -- then again, you might want to review that possibility for yourself, depending on how much you want to use NOLOCK -- but for specific cases when you really want to use NOLOCK but not have to deal with allocation scans (which can indeed cause bad-data issues with NOLOCK not seen with RC), then you can run:

EXEC sys.sp_configure 'cursor threshold', 5000; --<<-- anything other than -1 will stop all allocation scans across the instance: -- MS states that you need to leave "small" sets to be sync; as a rough base, I consider 5,000 rows or less a "small" set. RECONFIGURE;

Once you've processed your critical NOLOCK query(ies), you can reset the threshold:

EXEC sys.sp_configure 'cursor threshold', -1; RECONFIGURE;

Ref (Paul White): https://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans%5B/quote%5D

Gotcha! This won't help me but interesting in any case!

x SSC-Insane Points: 23660 More actions · Answer 8

x wrote:

ScottPletcher wrote:

x wrote:
ScottPletcher wrote:
MMartin1 wrote:
I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard shared lock?
Yes, it can be. NOLOCK can prevent other tasks from deadlocking.
The jihad against NOLOCK is somewhat overdone. The only major difference between NOLOCK and READ COMMITTED (RC) is that modified can be read vs not read (you can mitigate an additional scan issue with a server setting). RC still does allow non-repeatable reads and phantom reads, just like NOLOCK does.
So whats the server setting? Apologies if its already been posted. I have stacks of nolock code (not my idea) and would be open to any improvements.
I wouldn't necessarily recommend that you leave this in place -- then again, you might want to review that possibility for yourself, depending on how much you want to use NOLOCK -- but for specific cases when you really want to use NOLOCK but not have to deal with allocation scans (which can indeed cause bad-data issues with NOLOCK not seen with RC), then you can run:
EXEC sys.sp_configure 'cursor threshold', 5000; --<<-- anything other than -1 will stop all allocation scans across the instance: -- MS states that you need to leave "small" sets to be sync; as a rough base, I consider 5,000 rows or less a "small" set. RECONFIGURE;
Once you've processed your critical NOLOCK query(ies), you can reset the threshold:
EXEC sys.sp_configure 'cursor threshold', -1; RECONFIGURE;
Ref (Paul White): https://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans%5B/quote%5D

Gotcha! This won't help me but interesting in any case!

ScottPletcher SSC Guru Points: 100950 More actions · Answer 9

x wrote:

ScottPletcher wrote:
x wrote:
ScottPletcher wrote:
MMartin1 wrote:
I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard shared lock?
Yes, it can be. NOLOCK can prevent other tasks from deadlocking.
The jihad against NOLOCK is somewhat overdone. The only major difference between NOLOCK and READ COMMITTED (RC) is that modified can be read vs not read (you can mitigate an additional scan issue with a server setting). RC still does allow non-repeatable reads and phantom reads, just like NOLOCK does.
So whats the server setting? Apologies if its already been posted. I have stacks of nolock code (not my idea) and would be open to any improvements.
I wouldn't necessarily recommend that you leave this in place -- then again, you might want to review that possibility for yourself, depending on how much you want to use NOLOCK -- but for specific cases when you really want to use NOLOCK but not have to deal with allocation scans (which can indeed cause bad-data issues with NOLOCK not seen with RC), then you can run:
EXEC sys.sp_configure 'cursor threshold', 5000; --<<-- anything other than -1 will stop all allocation scans across the instance: -- MS states that you need to leave "small" sets to be sync; as a rough base, I consider 5,000 rows or less a "small" set. RECONFIGURE;
Once you've processed your critical NOLOCK query(ies), you can reset the threshold:
EXEC sys.sp_configure 'cursor threshold', -1; RECONFIGURE;
Ref (Paul White): https://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans%5B/quote%5D
Gotcha! This won't help me but interesting in any case!

You said you use NOLOCK a lot, so it could help. How many triggers do you use? We use virtually none, and then only for small record sets, so we can set this option rather freely to reduce issues with NOLOCK. Your situation, of course, could be very different.

Yeah, it is a rather interesting quirk of SQL Server.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

x SSC-Insane Points: 23660 More actions · Answer 10

ScottPletcher wrote:

x wrote:

x wrote:
ScottPletcher wrote:
x wrote:
ScottPletcher wrote:
MMartin1 wrote:
I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard shared lock?
Yes, it can be. NOLOCK can prevent other tasks from deadlocking.
The jihad against NOLOCK is somewhat overdone. The only major difference between NOLOCK and READ COMMITTED (RC) is that modified can be read vs not read (you can mitigate an additional scan issue with a server setting). RC still does allow non-repeatable reads and phantom reads, just like NOLOCK does.
So whats the server setting? Apologies if its already been posted. I have stacks of nolock code (not my idea) and would be open to any improvements.
I wouldn't necessarily recommend that you leave this in place -- then again, you might want to review that possibility for yourself, depending on how much you want to use NOLOCK -- but for specific cases when you really want to use NOLOCK but not have to deal with allocation scans (which can indeed cause bad-data issues with NOLOCK not seen with RC), then you can run:
EXEC sys.sp_configure 'cursor threshold', 5000; --<<-- anything other than -1 will stop all allocation scans across the instance: -- MS states that you need to leave "small" sets to be sync; as a rough base, I consider 5,000 rows or less a "small" set. RECONFIGURE;
Once you've processed your critical NOLOCK query(ies), you can reset the threshold:
EXEC sys.sp_configure 'cursor threshold', -1; RECONFIGURE;
Ref (Paul White): https://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans%5B/quote%5D
Gotcha! This won't help me but interesting in any case!
You said you use NOLOCK a lot, so it could help. How many triggers do you use? We use virtually none, and then only for small record sets, so we can set this option rather freely to reduce issues with NOLOCK. Your situation, of course, could be very different.
Yeah, it is a rather interesting quirk of SQL Server.

I just don't see how this setting could work for us. First off, we have no schedule of when the nolock queries run, so we'd have to leave the setting on. Secondly, I'm not seeing how this setting helps when the tables being sourced with a nolock hint are routinely inserted, updated and deleted.

I'm sort of picky with SQL Server accuracy, heck in the case that you mentioned a 3 hour query with snapshot isolation not reading new data, for me thats EXACTLY what I want to happen. The best result from a 3 hour query would be to report 3 hour old data at the conclusion of that query's execution, I just don't know when I would want any other result honestly.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 11

I manage hundreds of prod dbs (and hundreds of qa ones) and I have roughly a dozen that have RCSI. The overhead is just so high. [And if you want Oracle processing, you should have bought Oracle! At least Oracle (8 and less) used to force the equivalent of RCSI (but it was a feature not an issue!). I'm not sure if Oracle still forces it or not.]

And our users generally prefer the most up-to-date data they can get. So even if report takes 30 mins to run, they typically don't want to see 30 min old data if newer data exists.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

frederico_fonseca SSCoach Points: 16036 More actions · Answer 12

that is where some disagree.

on my 1500 production db's 220 have RCSI enabled - and most new db's are being created with it enabled unless the team requesting it ask for it to be disabled for some reason or if they are a copy from a system that had it disabled as on this case we don't wish raise potential issues.

Majority of cases having it enabled has little impact on the server from our own testing and benefits from a performance point of view outweighs that impact.

Oracle has it by default in any of their versions - don't even think it can be disabled.

x SSC-Insane Points: 23660 More actions · Answer 13

What I'm starting to understand is that many ERP systems effectively split up the results for large reports. So in effect, while the entire reporting job might take hours, the report consists of many batches. If we order the batches by "date settled", then the older batches are less likely to change, and toward the end of the job, the batches that examine more recent business stand a better chance of getting more recent results. The bonus with this is that nolock can be ditched also.

But by nature of the very thing we do, this stuff must be reasoned about. For example, expecting an exact result hoping for up to date data just by tossing an hours long nolocker over the fence just seems contradictory to me, not saying anybody is advocating this at all, but alternatively, for a dead accurate set oriented query that takes more than seconds really needs that consistent snapshot look in my opinion and it'll probably be a waste of time trying to convince me otherwise. Approximate results are a different matter of course, I'm perfectly fine with nolock for troubleshooting for instance.

Not arguing, I just like to ruminate about such things.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 14

frederico_fonseca wrote:

that is where some disagree.
on my 1500 production db's 220 have RCSI enabled - and most new db's are being created with it enabled unless the team requesting it ask for it to be disabled for some reason or if they are a copy from a system that had it disabled as on this case we don't wish raise potential issues.
Majority of cases having it enabled has little impact on the server from our own testing and benefits from a performance point of view outweighs that impact.
Oracle has it by default in any of their versions - don't even think it can be disabled.

You're claiming a performance benefit from RCSI? That makes no sense at all to me, since RCSI requires 14 bytes be added to every row and that old versions of deleted/updated rows be copied to tempdb and, if the same row is modified more than once, scanned sequentially for SELECTs that need a specific one.

RCSI gives you some advantages, but it's a big hit against performance, period. Thus it's generally a very bad idea to enable it by default.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

x SSC-Insane Points: 23660 More actions · Answer 15

frederico_fonseca wrote:

that is where some disagree.
on my 1500 production db's 220 have RCSI enabled - and most new db's are being created with it enabled unless the team requesting it ask for it to be disabled for some reason or if they are a copy from a system that had it disabled as on this case we don't wish raise potential issues.
Majority of cases having it enabled has little impact on the server from our own testing and benefits from a performance point of view outweighs that impact.
Oracle has it by default in any of their versions - don't even think it can be disabled.

RCSI has performance disadvantages UNLESS you're interested in accuracy. You can probably beat the snot out of RCSI with nolock queries LOL