October 28, 2019 at 3:49 pm
I've worked with SQL Server since v4.2.1. I have never put any code containing NOLOCK in production. At my present employer we actively remove WITH (NOLOCK) from code as it is being updated.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
October 28, 2019 at 3:57 pm
@bryant-mcclellan, NOLOCK seems to be a bit scary to me, especially when reporting financial data. There's a huge possibility that the reports might be generated with incorrect data, leading to a bad business decision that we never want to happen.
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
October 28, 2019 at 4:04 pm
@aveek22, totally agree - it depends upon context, we often (hourly) run a query to identify errors being fed from another system (don't ask), if we return 1 or 2 fewer than they really are is not important as we will pick them up on the next run and nolock stops the query from waiting whilst the heavily used table is being updated.
Very careful with it's use in production code.
October 28, 2019 at 4:08 pm
Agreed. That is also why having some demonstration of the potential consequences is so valuable. For some applications it is less risky.
The biggest problem is having it peppered throughout a codebase without rationale for the next new person to discover. Suddenly you have cargo cult programming at work and you don't understand how you suddenly get questionable results. I assume any code I deploy will get copied by someone at some point. If I don't have solid rationale about why it will apply in the majority of cases then I better tread lightly.
I cannot count the number of code reviews I've done where someone could not explain why they did something other than copying an existing example. You want the code that your company depends on to be the best because it will get reused where it was not intended to.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
October 28, 2019 at 4:50 pm
I am always surprised that the default isolation level for SSIS package objects is Serializable. Granted, it could be useful or even necessary in some cases. Isolation Level settings other than ReadCommitted should be concious decisions.
I have to admit, serializable is just so darn easy. If I have an update that involves reading multiple tables and / or subqueries, serializable just takes care of some of the artifacts that I would otherwise have to keep an eye out for.
October 28, 2019 at 5:58 pm
You can get phantom reads and repeat reads using the default iso level of READ COMMITTED. The only thing NOLOCK adds is dirty reads. And you can greatly reduce the chance of those by setting cursor threshold to a reasonable, positive value (200 or whatever); naturally you should set it back when you are done with the reads. That setting is not going to cause errors with results, it might just (in theory) cause less performance for your cursor'ed reads. And most people don't use very many cursors at all, for very good and well known reasons.
I'm not saying to use NOLOCK indiscriminately, but it should definitely not be banished. There are certain cases where it can work very well, such as just query a master table for existence of a row, or reading from a lookup table (such as a states table).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 28, 2019 at 6:07 pm
scott
cursor thresholds are a new one on me...help a fellow dba out
MVDBA
October 28, 2019 at 6:20 pm
I guess I've yet to encounter a tuning problem that required NOLOCK to handle.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
October 28, 2019 at 6:22 pm
Ok, for a fellow DBA. I actually "stole" this from a Paul White article. To be honest, I wasn't aware that the cursor setting prevented allocation scan reads until I was doing research and read his article a few years back. Then again, that's why I do this kind of reading, 'cause I'm a nerd-DBA and want to learn this kind of obscure stuff.
https://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply