November 16, 2011 at 9:50 am
As far as how to deal with it, find an example where that dirty read will cost the company money. Document thoroughly and show the boss. Then you've done your CYA and maybe convinced the boss that NOLOCK is a bad idea.
It's a pain. Sometimes ignorance is bliss. :hehe:
November 16, 2011 at 9:56 am
My personal preference is to use the statement:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
at the beginning of predominantly read-only stored procs. That way, you don't need the dumb query hints anyway.
November 16, 2011 at 10:01 am
Steve Malley (11/16/2011)
My personal preference is to use the statement:SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
at the beginning of predominantly read-only stored procs. That way, you don't need the dumb query hints anyway.
How do you say Potato, pôtato on a forum?
I agree it's easier to code and fix, but if you don't break it first, then it doesn't need fixin!
November 16, 2011 at 10:04 am
SET BAN_READUNCOMMITTED = ON
= Awesome
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 16, 2011 at 10:09 am
Using SNAPSHOT isolation or READ_COMMITTED_SNAPSHOT completely avoids any need to use NOLOCK and avoids its bad side effects.
Since this has been available since the release of SQL 2005, there is really no justification for using NOLOCK as a standard (if there ever was). It’s very sad to see such a bad practice raised to an actual standard.
Just to address the OP's question, SQL Server Books Online specifically states that NOLOCK cannot be used on the target of the DELETE:
"SQL Server 2008 Books Online (August 2008)
Table Hints (Transact-SQL)
...
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.
...
"
November 16, 2011 at 10:13 am
Michael Valentine Jones (11/16/2011)
Using SNAPSHOT isolation or READ_COMMITTED_SNAPSHOT completely avoids any need to use NOLOCK and avoids its bad side effects.Since this has been available since the release of SQL 2005, there is really no justification for using NOLOCK as a standard (if there ever was). It’s very sad to see such a bad practice raised to an actual standard.
Just to address the OP's question, SQL Server Books Online specifically states that NOLOCK cannot be used on the target of the DELETE:
"SQL Server 2008 Books Online (August 2008)
Table Hints (Transact-SQL)
...
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.
...
"
Can you imagine the implications if it didn't ignore it?
Qry1 => Select WITH REPEATABLE READ, locks part of the table
Qry2 => Delete WITH NOLOCK, ignore all the locks and delete anyways
Qry1 = BOOM (data movement error), & / or certainly not repeatable reads!
November 16, 2011 at 10:14 am
Steve Malley (11/16/2011)
My personal preference is to use the statement:SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
at the beginning of predominantly read-only stored procs. That way, you don't need the dumb query hints anyway.
My preference is to set the database to read_committed_snapshot and never have to worry about any of this again.
use master;
alter database [MyDB] set allow_snapshot_isolation on;
alter database [MyDB] set read_committed_snapshot on;
November 16, 2011 at 10:17 am
Dev (11/16/2011)
But what I am saying is a fact (Live Example). My customer is forcing us to write NOLOCK hint in all queries. It's primary QA check (per their Best Practice Document). SPs will not move to higher environments if they don't have such hints. What to do in such case except accepting the foolish practices.
I had one of those. I sat down with the architect and explained to him what NoLock actually did and what effects it could have. The standards document was updated the next day. Most places that 'require' this simply don't know what it actually does and will be horrified when they realise what it actually does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 10:25 am
I had one of those. I sat down with the architect and explained to him what NoLock actually did and what effects it could have. The standards document was updated the next day. Most places that 'require' this simply don't know what it actually does and will be horrified when they realise what it actually does.
@Gail: We tried it but didn't work out. Their statement is firm "We are fine with Dirty Reads". The worst part is it’s a Financial Domain project. :unsure:
November 16, 2011 at 10:29 am
Dev (11/16/2011)
I had one of those. I sat down with the architect and explained to him what NoLock actually did and what effects it could have. The standards document was updated the next day. Most places that 'require' this simply don't know what it actually does and will be horrified when they realise what it actually does.
@Gail: We tried it but didn't work out. Their statement is firm "We are fine with Dirty Reads". The worst part is it’s a Financial Domain project. :unsure:
Wow, this might be a case where I'd go to the CIO, or above his head if this was my contact.
Our primary responsibility is to the data and the people who use it...
In any cases I'd make him sign something that says I know this can return the wrong data and this is my decision. Takes you off the hook.
Make sure to save 10 copies of that and then back those up!
November 16, 2011 at 10:34 am
In any cases I'd make him sign something that says I know this can return the wrong data and this is my decision. Takes you off the hook.
Make sure to save 10 copies of that and then back those up!
Yup... I do have it... Checked it into VSS as well...
November 16, 2011 at 10:40 am
Dev (11/16/2011)
In any cases I'd make him sign something that says I know this can return the wrong data and this is my decision. Takes you off the hook.
Make sure to save 10 copies of that and then back those up!
Yup... I do have it... Checked it into VSS as well...
That comment & file would so be in the application init file, and again in the data layer ddl & whatnot :Whistling:
November 16, 2011 at 11:08 am
Dev (11/16/2011)
I had one of those. I sat down with the architect and explained to him what NoLock actually did and what effects it could have. The standards document was updated the next day. Most places that 'require' this simply don't know what it actually does and will be horrified when they realise what it actually does.
@Gail: We tried it but didn't work out. Their statement is firm "We are fine with Dirty Reads". The worst part is it’s a Financial Domain project. :unsure:
People say that a lot. Thing is, do they know what 'dirty reads' are, do they know what effects they can have? I certainly didn't go to the architect and say 'Stop using NoLock! It can cause Dirty Reads!!!'. His reply would have been something along the lines of 'And so?'
I explained to him the effects that it could have on the data. Not in deep technical terms. In examples. Somehow seeing a $10 million transfer from a rather large client listed twice in the daily summary caught his attention.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 11:09 am
Michael Valentine Jones (11/16/2011)
My preference is to set the database to read_committed_snapshot and never have to worry about any of this again.
use master;
alter database [MyDB] set allow_snapshot_isolation on;
alter database [MyDB] set read_committed_snapshot on;
Why both? Or do you have some processes that you want using snapshot isolation and the others using read committed snapshot?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2011 at 12:07 pm
Brandie Tarvin (11/16/2011)
Dev (11/16/2011)
Now I'm curious. Since when is the use of NOLOCK a best practice?
This is cross-question. This is what I suggested to Ashu. 😀
But what I am saying is a fact (Live Example). My customer is forcing us to write NOLOCK hint in all queries. It's primary QA check (per their Best Practice Document). SPs will not move to higher environments if they don't have such hints. What to do in such case except accepting the foolish practices. That’s why I didn’t criticize Ashu.
Oh, I've seen it. In fact, there are people who swear by it in my environment, even though I've tried to get them not to use it. It was just the fact that you mentioned Best Practices and I've never seen this listed in a Best Practice list before.
As far as how to deal with it, find an example where that dirty read will cost the company money. Document thoroughly and show the boss. Then you've done your CYA and maybe convinced the boss that NOLOCK is a bad idea.
The place I worked four years ago, it was listed as a "Required Practice", which took precedence over "Best Practices" there. Fortunately, I was able to get them to change their minds on it, but they'd been using it for years. So, there are definitely people who think it's a best practice. And some of them are in positions of authority and can enforce it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 31 through 45 (of 76 total)
You must be logged in to reply to this topic. Login to reply