June 3, 2011 at 11:40 am
I have 2 applications running against same table:
Application A uses SSIS to read from the table ( about 165,000 rows & 175 MB size) by using statement
SELECT * FROM theTable (NOLOCK)
Applicaiton B constantly update, delete, insert to the table one row at a time
Interestingly, we found blocking happened between the sessions from Applicaiotion A and Applicaiton B. There are other sessions opened in the same database that has 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' but I think the setting should not affect other sessions in the database, right?
I am wondering why the the blocking happens on theTable even I used (Nolock)?
P.S. there is no schema changes happens on the table when the blocking happened.
June 3, 2011 at 12:03 pm
You'll need to check to see what's blocking what. NOLOCK won't block other data reads/writes/etc. That's one of the reasons it's usually a really bad idea to use it (99% of the time, anyway).
Are you familiar with tools like sp_who2?
- 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
June 3, 2011 at 12:07 pm
Thank you for the reply! I see the blocking from Activity Monitor, it says, the select session is 'Blocking' and the 'Insert/update/detelet' session is blocked by the session Id from select.
June 3, 2011 at 12:12 pm
Check the blocking code, see if it's got something in it that would cause it to either ignore the NoLock hint, or some part of it that is supposed to have that and doesn't.
- 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
June 3, 2011 at 12:13 pm
No to sound rude, but are you kidding me by saying Nolock is a bad idea 99% of the time....? Everyone I know has clearly shown it has huge benefits and inhibit locking, every DBA i've worked with, and myself included personally tested it over and over showing it helps with contention each time.
Now back to the question. The nolock is not 100% guaranteed, if there are inserts updates affecting that exact row, or other memory resource issues, and sometimes a clustered index you could have some issues. I would also check to see if you have enough free memory on the server, in laments terms nolock says give me what pages you have in memory for this record. If there is not enough FREE memory then SQL cannot page(store) it in memory and in that case Nolock will not help you at all because it must read from Disk and thus fight with the records being affected.
Remember just becuase a server had 8 gigs, doesnt mean SQL has enough to store what it needs.
June 3, 2011 at 12:24 pm
digdave7 (6/3/2011)
No to sound rude, but are you kidding me by saying Nolock is a bad idea 99% of the time....? Everyone I know has clearly shown it has huge benefits and inhibit locking, every DBA i've worked with, and myself included personally tested it over and over showing it helps with contention each time.Now back to the question. The nolock is not 100% guaranteed, if there are inserts updates affecting that exact row, or other memory resource issues, and sometimes a clustered index you could have some issues. I would also check to see if you have enough free memory on the server, in laments terms nolock says give me what pages you have in memory for this record. If there is not enough FREE memory then SQL cannot page(store) it in memory and in that case Nolock will not help you at all because it must read from Disk and thus fight with the records being affected.
Remember just becuase a server had 8 gigs, doesnt mean SQL has enough to store what it needs.
Helps with contention? Yes. But there are better solutions that don't result in dirty reads, thread crashes, connection kills, and other unpredictable and unfun events.
Is NoLock good in your system? The answer is yes, if the accuracy of your data doesn't matter to anyone. Yes, if missing data is perfectly okay. Yes, if duplicate data is a good thing. No if any of those are false. The answer is also yes if you never have to worry about a table scan running into empty space where it expected to find pages of data, and has to kill a connection (these days, probably a shared connection), thus resulting in end users, possibly even customers, getting error messages instead of data. Don't want that to happen? Use a solution that actually works correctly, instead of NoLock.
I know exactly what you'll say next, so don't bother. "It's never happened to me or to anyone I know." Yep. I believe that. It's never happened to you that you know of. But how will know if dirty reads are giving your company's managers incorrect data, resulting in poor decisions, resulting in lost revenue? You won't. You'll never know it happened. All you'll know is that you are deliberately creating that condition, and hoping that it doesn't result in your employer going out of business.
Personally, I take being a DBA more seriously than that. I like to think that the data matters. That management decisions and customer requests actually have an impact on the well-being of the company I work for. And thus, that they matter to me.
So, I'm assuming that, about 1% of the time, dirty reads, killed connections, et al, are acceptable, and 99% of the time, they aren't. You're assuming they are acceptable 100% of the time, if they give you a slight speed improvement. A speed improvement that better code, and better management of lock escalation events, et al, will blow away without any effort whatsoever. And without the risks outlined above.
We disagree on the importance of correct data. So be 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
June 3, 2011 at 12:33 pm
I will sound rude, but anyone who calls themselves a DBA and actually recommends NOLOCK is not really a DBA.
It helps with contention? How about designing good schema and writing efficient code to deal with contention.
As for testing, you must only be testing for performance. Becasue if you did real testing, you would have certainly uncovered issues within the data.
There have been a significant number of discussions on this site about nolock, and nearly all of them are against it, and have provided facts to prove it.
Oh, and keep using NOLOCK for me, will ya? Because I make a TON of money cleaning up the messes created by "the last DBA..."
As for the blocking, is it LOCKING or BLOCKING you are observing? What you see through the interface may not be what is actually happening.
What KIND of block are you seeing?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 3, 2011 at 12:38 pm
Sorry, I'm with G on this, NOLOCK should be used sparingly and carefully. Yes it does reduce contention, but it doesn't do it for free. The cost is that is can and does read "dirty" pages. For data that is not likely to be changing such as historical data, static lookup data, or even old data, NOLOCK *may* be ok. But just slapping it on there without understanding what is happening is dangerous to say the least.
Any DBA worth his salt will be very careful with using NOLOCK. Not that they won't, ever but it won't be the first suggestion. And yes it can help, but understanding the ramifications of that decision are paramount.
CEWII
June 3, 2011 at 12:48 pm
GSquared (6/3/2011)
digdave7 (6/3/2011)
No to sound rude, but are you kidding me by saying Nolock is a bad idea 99% of the time....? Everyone I know has clearly shown it has huge benefits and inhibit locking, every DBA i've worked with, and myself included personally tested it over and over showing it helps with contention each time.Now back to the question. The nolock is not 100% guaranteed, if there are inserts updates affecting that exact row, or other memory resource issues, and sometimes a clustered index you could have some issues. I would also check to see if you have enough free memory on the server, in laments terms nolock says give me what pages you have in memory for this record. If there is not enough FREE memory then SQL cannot page(store) it in memory and in that case Nolock will not help you at all because it must read from Disk and thus fight with the records being affected.
Remember just becuase a server had 8 gigs, doesnt mean SQL has enough to store what it needs.
Helps with contention? Yes. But there are better solutions that don't result in dirty reads, thread crashes, connection kills, and other unpredictable and unfun events.
Is NoLock good in your system? The answer is yes, if the accuracy of your data doesn't matter to anyone. Yes, if missing data is perfectly okay. Yes, if duplicate data is a good thing. No if any of those are false. The answer is also yes if you never have to worry about a table scan running into empty space where it expected to find pages of data, and has to kill a connection (these days, probably a shared connection), thus resulting in end users, possibly even customers, getting error messages instead of data. Don't want that to happen? Use a solution that actually works correctly, instead of NoLock.
I know exactly what you'll say next, so don't bother. "It's never happened to me or to anyone I know." Yep. I believe that. It's never happened to you that you know of. But how will know if dirty reads are giving your company's managers incorrect data, resulting in poor decisions, resulting in lost revenue? You won't. You'll never know it happened. All you'll know is that you are deliberately creating that condition, and hoping that it doesn't result in your employer going out of business.
Personally, I take being a DBA more seriously than that. I like to think that the data matters. That management decisions and customer requests actually have an impact on the well-being of the company I work for. And thus, that they matter to me.
So, I'm assuming that, about 1% of the time, dirty reads, killed connections, et al, are acceptable, and 99% of the time, they aren't. You're assuming they are acceptable 100% of the time, if they give you a slight speed improvement. A speed improvement that better code, and better management of lock escalation events, et al, will blow away without any effort whatsoever. And without the risks outlined above.
We disagree on the importance of correct data. So be it.
Where's the + 1 000 000 bazillion button?
June 3, 2011 at 12:56 pm
digdave7 (6/3/2011)
No to sound rude, but are you kidding me by saying Nolock is a bad idea 99% of the time....? Everyone I know has clearly shown it has huge benefits and inhibit locking, every DBA i've worked with, and myself included personally tested it over and over showing it helps with contention each time...
Locks are a mechanism that SQL Server uses to ensure the integrity of the data. As others have pointed out, NOLOCK defeats these locks to allow you to look at data in a invalid state with changed data that has not been and may never be committed. If you were moving your money from one account to another, would you want your bank to show that you have a zero balance because the money was deleted from one account but not yet added to your other account? Think long and hard before you violate ACID (atomicity, consistency, isolation, durability) principles that guarantee database transactions are processed reliably.
If blocking is a big issue for an application, using row-version isolation with READ_COMMITTED_SNAPSHOT or SNAPSHOT isolation, will eliminate blocking for read-only queries, but will still ensure that your query returns a consistent view of the data.
June 3, 2011 at 1:27 pm
clare.xia (6/3/2011)
Thank you for the reply! I see the blocking from Activity Monitor, it says, the select session is 'Blocking' and the 'Insert/update/detelet' session is blocked by the session Id from select.
Are there any table alterations floating around, alter table, disable trigger, anything like that? If there are, that'll cause blocking even with nolock. Nolock just means that selects don't take shared locks (and hence ignore update or exclusive locks) they still have to take Schema Stability locks (Sch-S) (as a table changing structure while a select is reading is really not fun) and if there's a Schema Modification lock in place (Sch-M) everything else gets blocked, nolock or no nolock.
Oh, and just as one more reason why not...
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
If you want readers not to block writers and vis versa, consider one of the snapshot isolations which will give not lock-less queries without the dirty data problem Nolock has.
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
June 3, 2011 at 1:42 pm
Elliott Whitlow (6/3/2011)
Sorry, I'm with G on this, NOLOCK should be used sparingly and carefully. Yes it does reduce contention, but it doesn't do it for free. The cost is that is can and does read "dirty" pages. For data that is not likely to be changing such as historical data, static lookup data, or even old data, NOLOCK *may* be ok. But just slapping it on there without understanding what is happening is dangerous to say the least.Any DBA worth his salt will be very careful with using NOLOCK. Not that they won't, ever but it won't be the first suggestion. And yes it can help, but understanding the ramifications of that decision are paramount.
CEWII
As for static/historical/lookup data, you can get better results than NOLOCK by placing that in read-only file groups. For data in those, SQL Server doesn't even check for locks, doesn't worry about isolation, etc., so it actually cuts out some processor overhead and gives you a better performance improvement than NOLOCK/READ UNCOMMITTED.
That's one of the "better ways" I was refering to. Not applicable to the original question on this thread (where there are concurrent updates in the table being asked about), but should be mentioned because of later developments in the thread.
- 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
June 3, 2011 at 1:46 pm
GilaMonster (6/3/2011)
clare.xia (6/3/2011)
Thank you for the reply! I see the blocking from Activity Monitor, it says, the select session is 'Blocking' and the 'Insert/update/detelet' session is blocked by the session Id from select.Are there any table alterations floating around, alter table, disable trigger, anything like that? If there are, that'll cause blocking even with nolock. Nolock just means that selects don't take shared locks (and hence ignore update or exclusive locks) they still have to take Schema Stability locks (Sch-S) (as a table changing structure while a select is reading is really not fun) and if there's a Schema Modification lock in place (Sch-M) everything else gets blocked, nolock or no nolock.
Oh, and just as one more reason why not...
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
If you want readers not to block writers and vis versa, consider one of the snapshot isolations which will give not lock-less queries without the dirty data problem Nolock has.
The original post asserted no pending DDL. Worth double-checking, of course.
If I remember correctly, snapshot isolation is Enterprise Edition only, so may not be an option (or might).
- 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
June 3, 2011 at 1:49 pm
GSquared (6/3/2011)
The original post asserted no pending DDL. Worth double-checking, of course.
The Sch-S is the only lock a select with nolock takes, that's only blocked by an Sch-M. Need to check the blocking, check the lock and check the wait type.
If I remember correctly, snapshot isolation is Enterprise Edition only, so may not be an option (or might).
It's available in all editions, both snapshot and 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
June 3, 2011 at 1:53 pm
GilaMonster (6/3/2011)
GSquared (6/3/2011)
The original post asserted no pending DDL. Worth double-checking, of course.The Sch-S is the only lock a select with nolock takes, that's only blocked by an Sch-M. Need to check the blocking, check the lock and check the wait type.
If I remember correctly, snapshot isolation is Enterprise Edition only, so may not be an option (or might).
It's available in all editions, both snapshot and read-committed snapshot.
Yeah, looks like I'm crossing it up with some other feature on that one. Wasn't sure, when I wrote it, and just finished fact-checking myself.
- 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 - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply