January 30, 2012 at 11:09 am
GSquared (1/30/2012)
As another potential drawback for NoLock, I've actually seen it crash a server, requiring database repair.A shared connection (from a web server, of course) ran into two concurrent missing data page errors, tried to close the same connection twice, ran into a problem on that, and the database server cratered and the database ended up having to be restored from backups.
But I've never heard of anyone else running into this, so it's probably rare enough to ignore as a real risk. After all, your server room and your DR co-location could be simultaneously hit by meteors, but the risk is low enough to ignore. Same probably applies here.
So is this documented anywhere that an isolation mode setting can corrupt a database?
January 30, 2012 at 11:12 am
Any time some action within SQL causes corruption it's a bug.
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
January 30, 2012 at 11:15 am
GSquared (1/30/2012)
As another potential drawback for NoLock, I've actually seen it crash a server, requiring database repair.A shared connection (from a web server, of course) ran into two concurrent missing data page errors, tried to close the same connection twice, ran into a problem on that, and the database server cratered and the database ended up having to be restored from backups.
But I've never heard of anyone else running into this, so it's probably rare enough to ignore as a real risk. After all, your server room and your DR co-location could be simultaneously hit by meteors, but the risk is low enough to ignore. Same probably applies here.
I don't know if it's documented anywhere. I didn't read about it, I saw it happen. The stack dump told us what caused it. Haven't looked to see if it's documented officially anywhere.
Was SQL 2000, in 2002, so even if it was, it may not be applicable in present time. Or it might be. It's not like I can duplicate the situation intentionally, so I can't test it.
And it wasn't directly the isolation level that caused the corruption, it was the server doing a hard crash that was the proximate cause. It was NoLock hitting pages that had moved that caused the crash, so it was indirectly the cause.
- 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
January 30, 2012 at 11:31 am
GSquared (1/30/2012)
GSquared (1/30/2012)
As another potential drawback for NoLock, I've actually seen it crash a server, requiring database repair.A shared connection (from a web server, of course) ran into two concurrent missing data page errors, tried to close the same connection twice, ran into a problem on that, and the database server cratered and the database ended up having to be restored from backups.
But I've never heard of anyone else running into this, so it's probably rare enough to ignore as a real risk. After all, your server room and your DR co-location could be simultaneously hit by meteors, but the risk is low enough to ignore. Same probably applies here.
I don't know if it's documented anywhere. I didn't read about it, I saw it happen. The stack dump told us what caused it. Haven't looked to see if it's documented officially anywhere.
Was SQL 2000, in 2002, so even if it was, it may not be applicable in present time. Or it might be. It's not like I can duplicate the situation intentionally, so I can't test it.
And it wasn't directly the isolation level that caused the corruption, it was the server doing a hard crash that was the proximate cause. It was NoLock hitting pages that had moved that caused the crash, so it was indirectly the cause.
Ok, just trying to get clarification, I wouldn't expect nolock to be supported if the server couldn't recover from this situation.
I'm interested in the page movment, from the discussion here, I've just learned that a 'nolocked' query can bail circumstance is due to a page being deleted, its behavior anticipated by Microsoft by virtue of it having an error numberr 601 (thanks to GilaMonster for pointing this out). I'm still pretty fresh to SQL so I feel compelled to dig, especially as I have 2000 level servers and am required to support nolock here.
January 30, 2012 at 12:55 pm
I remember when i learned abou NOLOCK. I used it for each table in each query.
It solved many problens and created some more...
But it was with SQL 7 a long time ago.
NOLOCK is a valid tool, like drop database or kill spid.
But like any tool use it wisely.
For example it can be used for domain tables, since these tables dont change daily (or even monthly) the dirty reads ill no to be a issue.
There are (very few) scenarios where a duplicate or missing row ill not to be a problem, again use it wisely.
A see nolock a lot, in fact my actual job is refactoring a app and I dont found a single select without nolock for every table and I'm pretty sure we can get rid of most.
I disagree with Kiwi about SET TRANSACTION ISOLATION LEVEL is prefearable since you can put the table hint just at the table needing it
and for removing nolock I can just replace "(nolock)" by "" or "/*(nolock)*/" <-just kidding 😉
January 30, 2012 at 12:59 pm
jcb (1/30/2012)
For example it can be used for domain tables, since these tables dont change daily (or even monthly) the dirty reads ill no to be a issue.
Why use NOLOCK then? There are no locks anyway. It will server no benefit. It is not a magic go fast switch. It just ignores locks, and if there aren't any, nothing changes.
Jared
CE - Microsoft
January 30, 2012 at 2:05 pm
SQLKnowItAll (1/30/2012)
jcb (1/30/2012)
For example it can be used for domain tables, since these tables dont change daily (or even monthly) the dirty reads ill no to be a issue.
Why use NOLOCK then? There are no locks anyway. It will server no benefit. It is not a magic go fast switch. It just ignores locks, and if there aren't any, nothing changes.
It will potentially save the CPU overhead of establishing a shared lock. On a seriously overloaded system, that could be beneficial. If I remember correctly, putting static tables on a read-only fileset will give the same benefit. Snapshot or read committed snapshot isolation will also give that benefit, and avoid the whole dirty reads thing at the same time.
- 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
January 30, 2012 at 7:26 pm
Rob-350472 (1/30/2012)
What are the implications of setting "READ_COMMITTED_SNAPSHOT" to ON rather than OFF (the default)? I seem to recall looking into this before (ages ago) because it sounded attractive but I discovered some some drawbacks...
The one that can be most difficult to deal with is the change in blocking behaviour. An application might rely on blocking (explicitly or implicitly) for correct results; this can lead to unexpected behaviours when writers no longer block readers. Sometimes the transition from RC to RCSI simply means adding READCOMMITTEDLOCK in referential-integrity trigger code. This is the most common example, but there are certainly other analogous cases.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 30, 2012 at 7:29 pm
jcb (1/30/2012)
I disagree with Kiwi about SET TRANSACTION ISOLATION LEVEL is prefearable since you can put the table hint just at the table needing it...
As I said (in the same sentence), "It is pretty rare to genuinely need to override the isolation level for just one table in a query". My experience is that people looking to do this generally aren't as skilled in the art as they think they are. YMMV.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 30, 2012 at 7:43 pm
GSquared (1/30/2012)
It will potentially save the CPU overhead of establishing a shared lock. On a seriously overloaded system, that could be beneficial.
The Read Committed isolation level guarantees two things: you will not experience dirty writes or dirty reads. It does not say anything about how those guarantees should be implemented. SQL Server happens to use shared locks as part of its implementation of the default isolation level – but you cannot rely on those locks always being present. The storage engine is smart enough to avoid taking many shared row locks when there is no danger of reading uncommitted data, see The Case of the Missing Shared Locks
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 1:22 am
Michael Valentine Jones (1/30/2012)
SQL Kiwi (1/30/2012)
...Code that has NOLOCK on every single table in every single query is an infallible indicator of poor coding, in my experience.
I mostly agree with this, except that it is also often an indicator of poor physical database design leading to massive blocking and/or deadlocking.
I felt this exercise because,
Scenario #1: Past Events:
After the sales process, i am seeing some factors which does not change, but more SELECTs on them, like
For a
SaleID,
its corresponding
receiptid,
customer id,
refundID,
productid,
soldLocationInfo, whose events are already done in past.
Scenario #2: Static / Hardly-changeable Information (Master tables)
Like, Company, Product infos.
moreover, it is not an 24x7 application, so any releases happens in off-time.
January 31, 2012 at 1:47 am
DBA Rafi (1/31/2012)
After the sales process, i am seeing some factors which does not change, but more SELECTs on them, likeScenario #2: Static / Hardly-changeable Information (Master tables)
If the data in question doesn't change much or at all, then why do you need nolock? Unless your system is completely loaded, the small CPU saving from not taking locks will be minuscule.
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
January 31, 2012 at 1:49 am
Are there any implications for database storage and or disk use of going down the snapshot route? That's what seems to be ringing some bells?
January 31, 2012 at 1:55 am
Rob-350472 (1/31/2012)
Are there any implications for database storage and or disk use of going down the snapshot route? That's what seems to be ringing some bells?
Yes you might need to allocate more tempdb space if your system makes heavy use of the version store. There is also the question of the extra 14 bytes per row needed to record version information. Both these issues are documented and discussed in Books Online:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 2:07 am
Just wondering what you'd make of using nolock in this situation...
You want to obtain the most recent communication and a count of communications for a particular customer.
Running that query against a large set of customers appears to prevent others from adding a communication until the query has completed, which if there's a bunch of other fields can take a while.
Adding a nolock gets around this issue, people can continue to add communications.
In this instance the only problems I can see (aside from that odd one about taking out an entire database!) are that we'd potentially get an incorrect count of communications or the most recent date would be inaccurate, however, if you were doing this against a set of say 50,000 customers and the query took 1 min to run the chances of this happening are fairly slim, if it does happen it's not a huge issue as most of the data will be fine.
Clearly it's best to get all of the data 100% accurate all of the time, however, if the only way of (currently) doing this is locking up the table preventing others from writing to it then that's not an ideal solution.
It does seem that READ_COMMITTED_SNAPSHOT is worth looking into more, however, I just wonder what people's take is on the above usage?
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply