November 16, 2011 at 8:14 am
hmm alright but my use of WITH(NOLOCK) in delete block is correct or not?
November 16, 2011 at 8:16 am
ashuthinks (11/16/2011)
hmm alright but my use of WITH(NOLOCK) in delete block is correct or not?
The engine takes a lock for the delete operation, period. End of story. No way around that.
If you convert that table to temp table you won't get any deadlock.
You can look at snapshot isolation to get less blocking on the base tables.
November 16, 2011 at 8:16 am
ashuthinks (11/16/2011)
hmm alright but my use of WITH(NOLOCK) in delete block is correct or not?
Syntactically it's correct. It's almost certainly the complete wrong way to go about this, but I'm tired of talking to myself about things like tuning code and snapshot isolation.
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 8:17 am
ashuthinks (11/16/2011)
@gila yes sir...i knew that if i don't use NOLOCK i will definitely get a deadlock so for successful
execution i need to use NOLOCK
You are a victim of Best Practice Implementation (blind implementations rather) where justification part is missing.
The result is use of hints like NOLOCK everywhere whether it's required or not.
I wouldn’t say you are doing something bad (however it is). I would say cross-question any best practice (or DEV Lead) before you follow it.
November 16, 2011 at 8:18 am
Ninja's_RGR'us (11/16/2011)
ashuthinks (11/16/2011)
@gila yes sir...i knew that if i don't use NOLOCK i will definitely get a deadlock so for successful
execution i need to use NOLOCK
It's a mam actually..
...backs away slowly 😉
Backs away a little more :-D.
November 16, 2011 at 9:30 am
Dev (11/16/2011)
ashuthinks (11/16/2011)
@gila yes sir...i knew that if i don't use NOLOCK i will definitely get a deadlock so for successful
execution i need to use NOLOCK
You are a victim of Best Practice Implementation (blind implementations rather) where justification part is missing.
Now I'm curious. Since when is the use of NOLOCK a best practice?
November 16, 2011 at 9:33 am
Do you have the authority and control to completely rewrite this process?
If so, I urge you to to move the updates, inserts and deletes to a completely different ETL process and leave the reporting part as a SELECT only. Reporting should never change the data, it should only present it as a pretty picture for the end users.
I do work in an environment where data needs to get massaged before the reports can be created. We call it "the month end processes". Only after all the massaging is done are the reports ready and no report stored procedure changes data. Anyone who tries to create a report that changes data gets lynched.
Separate out the work. That way, if there's a bug in the report or bad things happen to the data, you have a smaller chunk to troubleshoot and know where the issue is hiding.
November 16, 2011 at 9:34 am
Brandie Tarvin (11/16/2011)
Dev (11/16/2011)
ashuthinks (11/16/2011)
@gila yes sir...i knew that if i don't use NOLOCK i will definitely get a deadlock so for successful
execution i need to use NOLOCK
You are a victim of Best Practice Implementation (blind implementations rather) where justification part is missing.
Now I'm curious. Since when is the use of NOLOCK a best practice?
Best practice is to AVOID nolock unless you have no other choice and agree to <very likely> bad data.
It's in there, somewhere!
November 16, 2011 at 9:36 am
Brandie Tarvin (11/16/2011)
Do you have the authority and control to completely rewrite this process?If so, I urge you to to move the updates, inserts and deletes to a completely different ETL process and leave the reporting part as a SELECT only. Reporting should never change the data, it should only present it as a pretty picture for the end users.
I do work in an environment where data needs to get massaged before the reports can be created. We call it "the month end processes". Only after all the massaging is done are the reports ready and no report stored procedure changes data. Anyone who tries to create a report that changes data gets lynched.
Separate out the work. That way, if there's a bug in the report or bad things happen to the data, you have a smaller chunk to troubleshoot and know where the issue is hiding.
More importantly it's an all or nothing proposition. All reports are good or all reports are wrong & it's all fixable in 1 spot.
There's little worse than having the users doubt the IT dept and the tools you give them.
Been there, still there, don't come here :-D.
November 16, 2011 at 9:38 am
I think what DEV meant was he's a victim of inheriting the practice from others. I dont' think any one on this forum would say its usage is considered a best practice and mean it 🙂
At our company, the locking hint is scattered everywhere throughout the code because of the way the 3rd party application works...it's always a lengthy uphill battle when attepting to remove it... :crazy:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 16, 2011 at 9:40 am
Maybe that could become a an new db or server option.
SET BAN_READUNCOMMITTED = ON.
And then it just ignores it with a printed warning or something. :hehe:
November 16, 2011 at 9:40 am
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.
November 16, 2011 at 9:41 am
That's why I only agree to come in solo or team lead :-D.
November 16, 2011 at 9:42 am
MyDoggieJessie (11/16/2011)
I think what DEV meant was he's a victim of inheriting the practice from others. I dont' think any one on this forum would say its usage is considered a best practice and mean it 🙂At our company, the locking hint is scattered everywhere throughout the code because of the way the 3rd party application works...it's always a lengthy uphill battle when attepting to remove it... :crazy:
Nice Guess 😉
November 16, 2011 at 9:44 am
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.
Viewing 15 posts - 16 through 30 (of 76 total)
You must be logged in to reply to this topic. Login to reply