How and When to use NOLOCK in web queries/stored procedures

When I upload race results periodically throughout a large running or biking race, people who have already finished are often accessing those same results.  What often happens is that they get a transaction error.  I have recently been directed to the NOLOCK hint as a way to avoid those errors.  I assume that I would put that in the query or stored procedure that generates the results display for the participant who is trying to view results.

It is my understanding that this may generate an incomplete results display but that the upload of new data from my end will not be affected.  Is that correct?  If not, what else could happen?  If it is correct, would that mean no 500 error would be generated?  Just trying to get a grip on how to best upload results data without encountering errors as people are trying to access that data.

BTW, our local timers have sql server 2012 express on their local machines when they time the race, but our web server is running slq ser 2012 web version.

Thanks in advance for any assistamnce!
Bob SchneiderCo-OwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I would add the NOLOCK hint to all SELECT...FROM and in the JOIN clauses or alternatively in stored procedures you could use:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

in order to minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications as described here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
Scott PletcherSenior DBACommented:
If the 2012 Web version of SQL allows it, turn on snapshot isolation for that db.  That will eliminate that problem completely, allowing anyone to view results even while data is continuously being loaded, but without dirty read issues.

Issue this command from SSMS:

ALTER DATABASE db_name_containing_race_results SET READ_COMMITTED_SNAPSHOT ON;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob SchneiderCo-OwnerAuthor Commented:
At the risk of exposing my true level of knowledge (or lack thereof), where do I run that statement in SSMS?  And does that make a permanent change to the db?
Scott PletcherSenior DBACommented:
Just click on "New Query" and run it in the query window.

Yes, your suspicions are very right, it definitely does make a permanent change to the db, a moderately significant one (although of course you can later explicitly turn it off, undoing the change).  

Assuming you have some extra disk available, though, it shouldn't be noticeable to users.

The big advantage is, that once it's in place, db writers (inserts, updates of race results) will never block or interfere with db readers (lookups, reports of race results), and vice versa.
Bob SchneiderCo-OwnerAuthor Commented:
That is so awesome!  Thank you!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.