| Author |
Topic |
|
ElenaSTL
Starting Member
10 Posts |
 Posted - 2014-10-07 : 14:26:31
|
| I’m an SQL/Report Developer. In one company I worked before we had a policy to use WITH (NOLOCK) for every SQL for reports. Stored Procedures for reports have only SELECT statement for database tables. UPDATE and INSERT used only for table variables or temp tables.I thought that if I don’t use WITH (NOLOCK) and query runs for 1 minute, it blocks tables from my SQL for 1 minute and user cannot make an order in Internet shopping for that time. Is that true?Now I’m working in another company and they have hundreds of views which I should use in my SPs, but none of the view has WITH (NOLOCK).I see a lot of information on Internet about WITH (NOLOCK) – pro and cons. Can somebody tell me If I should use it for Reports (Stored Procedures with SELECT statements for user tables)? SQL Server 2008 and 2012. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-07 : 14:51:16
|
| Don't use NOLOCK for any reports where accuracy matters. Don't *ever* use it for financial reporting where accuracy is required by law! NOLOCK can cause dirty reads and erroneous or unrepeatable results. It is true that under normal circumstances a SELECT will put a shared lock on the tables involved. That means many can read, but none can write until the locks are released. If you use NOLOCK, there will be no blocking of updates, but you may get dirty reads and questionable report results. An alternative is WITH (READPAST), which skips rows marked as dirty (currently subject to an INSERT/UPDATE/DELETE query). That way you will never get dirty data, though you may not get all the data either. Bottom line: If reporting accuracy matters, let SQL do default locking. If accuracy is not as important, use WITH (READPAST). |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-07 : 15:40:18
|
| I wonder if your previous company is my current company (Internet shopping type business). I've only been at this new company for 6 months, and they've had this NOLOCK requirement since forever. I am trying to break them of it and recommending that we implement RCSI instead.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-07 : 16:03:38
|
| yes Tara, a good suggestion if you care about accuracy and concurrency, though you have to watch tempdb |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-07 : 16:29:30
|
| Yes tempdb is why they've been hesitant so far. I just want them to start making a plan to get RCSI implemented. We can figure out the tempdb needs in the performance environment.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-10-07 : 17:44:44
|
| I know some people are hyper on this issue, but as a DBA, I'm concerned about over-all performance too.Thus, to me, one easy rule does not fit every situation, either to always use NOLOCK or to never use it. You need to decide on a case by case basis.If the data being SELECTed is limited to only historical data, which will never be modified -- such as last month's sales or last year's sales -- "WITH (NOLOCK)" can generally be used safely. For large amounts of data, that can save significant overhead.If developers go into production to take quick looks at data, they should always use "WITH (NOLOCK)" when they can.Btw, RCSI is *huge* overhead. In fact, you generally need to rebuild the table (clustered index) immediately after implementing it, because of the overhead bytes. I do use RCSI, but only in limited situations where it's truly required. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-08 : 09:00:14
|
| true, Scott. though I've converted to READPAST lately. Basically there's no downside. Doesn't lock the table AND will not return dirty data even if there's only a remote possibility of the data being updated at the same time |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-10-08 : 11:03:27
|
| There are serious potential downsides:1) READPAST still takes lock, so it can still cause a deadlock;2) you don't report some valid dataEven with UPDATEs occurring, NOLOCK generally works ok. Yes, you can get dirty/ghost reads. But on data that's (almost) never modified, it's not nearly as common as the complaints from some people would make you think. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-08 : 14:33:34
|
| Scott, I cannot find a reference that says READPAST takes a lock. I did find this: "blocking does not occur when issuing queries" from http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492, so I'm not sure if your number 1 is correct or not. your number 2 is probably incorrect, since the guarantee with READPAST is that it skips rows locked for update/insert/delete which -- at least by my definition -- is NOT valid data. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-10-09 : 11:40:04
|
quote:
Originally posted by gbritton
Scott, I cannot find a reference that says READPAST takes a lock. I did find this: "blocking does not occur when issuing queries" from http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492, so I'm not sure if your number 1 is correct or not. your number 2 is probably incorrect, since the guarantee with READPAST is that it skips rows locked for update/insert/delete which -- at least by my definition -- is NOT valid data.
1) The READPAST itself won't ever be blocked, because it just skips row and page locks. READPAST works in READCOMMITTED mode, in which SELECTs normally take shared locks, so I thought those might block others. But that does not appear to be the case. READPAST may also skip taking shared locks. (I wish they'd have the documentation explicitly state that though  .)2) I would consider a row being updated as valid data. To me it's bizarre to think otherwise. Is it valid that when, say, changing comments on an order, the order disappears from the results? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-09 : 12:02:08
|
| I've implemented RCSI on hundreds of databases, some are large, mission critical databases with availability requirements of 5 9s. I've never had to rebuild the indexes immediately after implementing it. Yes there is overhead to RCSI, but you plan for that. You run load tests in a performance environment to understand tempdb and storage requirements. You plan for any extra hardware that's needed. Never implement RCSI blindly. Plan for it where it makes sense. IMO, it makes sense in environments that are currently using NOLOCK as a turbo button in OLTP databases. At least with RCSI, your data is more accurate and can be exactly accurate. NOLOCK is just dangerous in OLTP databases where there are writes and rollbacks occurring.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-10-09 : 14:37:41
|
| You've never rebuilt indexes immediately afterward. But is that just because you didn't notice all the page splits caused the RCSI row overhead? Technically, no, you don't "have" to rebuild if you're willing to use an extremely fragmented table due to the page splits caused by adding RCSI bytes. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-09 : 14:45:03
|
| It's because we never had performance issues after implementing it, even on the most critical system that has high performance requirements. We later disabled our rebuild indexes job on that server at the recommendation of a MS engineer (PFE/DSE team). It's been disabled for over a year. All tables are heavily fragmented, and yet there is still no performance issue.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-09 : 20:34:22
|
quote:
Originally posted by ScottPletcher
quote:
Originally posted by gbritton
Scott, I cannot find a reference that says READPAST takes a lock. I did find this: "blocking does not occur when issuing queries" from http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492, so I'm not sure if your number 1 is correct or not. your number 2 is probably incorrect, since the guarantee with READPAST is that it skips rows locked for update/insert/delete which -- at least by my definition -- is NOT valid data.
1) The READPAST itself won't ever be blocked, because it just skips row and page locks. READPAST works in READCOMMITTED mode, in which SELECTs normally take shared locks, so I thought those might block others. But that does not appear to be the case. READPAST may also skip taking shared locks. (I wish they'd have the documentation explicitly state that though .)2) I would consider a row being updated as valid data. To me it's bizarre to think otherwise. Is it valid that when, say, changing comments on an order, the order disappears from the results?
I'd say so, yes. You've no idea what might be changing (items, quantities, dates, etc). Best to exclude it. |
|
|
|
|
WITH (NOLOCK) for Reports