| Author |
Topic |
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-25 : 14:11:33
|
| I went on a job interview once where they didn't hire me because I didn’t “know” that you should always NOLOCK.I believe I also mentioned that the need to use NOLOCK in a production application was a sure sign of a badly done application.CODO ERGO SUM |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-25 : 14:36:38
|
| Since I started to use NOLOCK exclusively, I have not locked my keys in my house or car once! Huge improvement. Now if I could just figure out where all my "stuff" has gone...Be One with the OptimizerTG |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-25 : 15:01:06
|
| One reason that people say that they never see any problems with NOLOCK is that there is little chance that people will notice that the data is wrong, but they will notice when they get a deadlock message.CODO ERGO SUM |
|
|
|
DavidChel
Constraint Violating Yak Guru
474 Posts |
Posted - 2008-08-25 : 16:08:14
|
| At the risk of sounding like an idiot, where is the author wrong? As a DBA, what would you recommend he do? I'm talking about Jeff, no the people who comment. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-25 : 16:13:56
|
| who said the author was an idiot?_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-08-25 : 16:31:25
|
author states this, which is wrong: "With nolock / read uncommitted / dirty reads, data may be out of date at the time you read it, but it's never wrong or garbled or corrupted in a way that will crash you."reading NOLOCK *can* crash you if an update forces a page split or delete on a page your NOLOCK read was reading. basically if the data you are reading suddenly disappears your select query will simply fail. See: http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
elsasoft.org |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-25 : 16:50:34
|
well since you haven't told him that in his comments Jesse i have  _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
DavidChel
Constraint Violating Yak Guru
474 Posts |
Posted - 2008-08-25 : 16:53:28
|
quote:
Originally posted by spirit1
who said the author was an idiot?_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
I was referring to me as the idiot. I read that blog often and found the article interesting. I was hoping to learn why you felt the author was wrong in his statements. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-25 : 17:04:09
|
| my bad. read it wrong. sorry._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-08-26 : 02:59:07
|
| Wow, there are some scary (wrong) comments there.--Gail ShawSQL Server MVP |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-08-26 : 08:57:14
|
| Funny, I also had a job interview once where they asked me "what is the best way to optimize SQL statements" or something like that. I answered indexes, writing clean, efficient SQL code, ensuring you have a good database design, avoiding cursors, ensuring you filter as much as you can to return only data you need to your clients, etc. As I gave each answer, the interviewer kinda nodded and said "and ...?" after each one, like he was looking for an answer I was not giving. After I gave all of my answers, I could tell that he pretty much completely brushed aside everything I said and was looking for something else. Finally, he casually, and kind of condescendingly, "helped" me out by asking "what about locking? Is there a way you know to solve that ...?" Finally, I realized what he was after and said "Wait ..are you talking about adding the NOLOCK hint? Is that the answer you were looking for?" and he was taken aback a little by my response and said "Well, yeah, of course" and I mentioned dirty reads and overall (potential) issues with using that approach and that it should be pretty much a last resort after doing the other things I mentioned. Not sure if I did a good job or not of hiding my frustration/amazement at his expected answer to this question and how he ignored everything else I tried to discuss (i.e., indexes!) It got kinda awkward for a few moments after that and the interview more or less fizzled. They did actually end up offering me a job but the company was way too small and I did not get good vibes from them....- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-08-26 : 10:05:09
|
| I know someone who recommended nolock everywhere. When I discussed it with him, it turned out he didn't understand what nolock did and thought it worked like Snapshot isolation. This was back on SQL 2000.The recomendations changed shortly after that discussion.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-08-26 : 10:09:25
|
quote:
Originally posted by DavidChel
I was hoping to learn why you felt the author was wrong in his statements.
Adding Nolock to every query is hiding the symptoms that lead to the deadlock, it's not solving the root cause (probably missing indexes, inefficient code or referecing objects in different orders)I've never seen a deadlock where I *had* to add nolock to solve. There was always another solution.--Gail ShawSQL Server MVP |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-26 : 10:10:26
|
| i think that the main beef with nolock is that the select makes a shared lock on the read data. while oracle and mySql apparently don't do that because of their architecture._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-08-26 : 11:00:59
|
| I wouldn't know about MySQL, but Oracle uses a locking mode almost identical to SQL 2005's Snapshot isolation, where selects don't need to lock, but can look through the version store/rollback segment to find the appropriate rows. Writes take locks.There's nothing inherently wrong with either approach, they both have advantages and disadvantages. Oracle, as one of the comments noted, has no option to allow dirty reads of any form--Gail ShawSQL Server MVP |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-26 : 11:57:11
|
NOLOCK is not that all bad.Not that long ago I recommended implementing NOLOCK hint to a system.All upserts were made during night with batches when no user were online.During all day, no upsert were made at all. So basically the system was read-only during day.Adding NOLOCK query hint made a huge impact on that system with regards to performance.The average query time went down by 50%.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-26 : 12:21:43
|
| If the database or just certain filegroups are really static, you can just set them to read-only, and SQL Server will not attempt to set locks.You get the same benefit without having to put NOLOCK hints all through your code, and you really know that you are not getting dirty reads.You would have to set them back to read/write at the start of your load process, and back to read only at the end.If there are tables that do need to be updated during the day, you would have to move those to a separate file group, and leave that set to read/write.CODO ERGO SUM |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
pootle_flump
1064 Posts |
Posted - 2008-08-28 : 10:36:20
|
| There's an Itzik article\ blog post out there too if anyone is interested in me digging it up. I think the point is made though now, yah? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-28 : 10:41:22
|
| do dig _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Next Page
|
Deadlocked! OMG!!! Use NOLOCK!!