NoLock on a Read-only database

Question

I have a database which is used just for reporting. Various users access this Database .

all of these users have only Read only access.

So I have only read only access for this database.

Should i use NOLOCK for these ? Is It help full for read only access user ?

Database is read-only or users cannot make changes? What if there is scheduled job or external application? — Lukasz Szozda, Aug 10 '17 at 07:56
Yes. Some user have write/read access . also there are some sheduled job are present. — rohit patil, Aug 10 '17 at 07:59
So with NOLOCK you can end up with dirty reads. You have to decide if it is desirable. — Lukasz Szozda, Aug 10 '17 at 08:00
These users have read only access, but there are other users with write access, so the first ones will still need NOLOCK to not be blocked by the second ones. Remember that NOLOCK means that you will get "dirty reads", you will see records inserted/updated by other user but not yet commited. — Marc Guillot, Aug 10 '17 at 08:07

score 0 · Answer 1 · answered Aug 10 '17 at 08:13

This has been covered many many times, TLDR: I wouldn't use it atall. The only scenarios is if you want for example a really rough row count ballpark figure of a massive table and don't care if it is slightly wrong, but even still - you should prefer creating good indexes, adding RAM or changing the Isolation Level of the query/server.

Resources and suggestions for alternatives:

https://www.brentozar.com/archive/2016/12/nolock-ever-right-choice/

Is the NOLOCK (Sql Server hint) bad practice?

https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

https://dba.stackexchange.com/questions/10655/is-nolock-always-bad

NoLock on a Read-only database

1 Answers1