For SQL Server 2008 and above, should (NOLOCK) be used on each table of a SELECT statement even if the connection is configured as READONLY?


Locking is mainly used to help writes and reads write and retrieve data that is ACID compliant. Thus a 'read only' query can have several layers of locking. By default, MS SQL Server uses pessimistic locking where as Oracle uses optimistic locking. That means in SQL Server reads by default will always block writes and vice versa. In Oracle's default isolation level, reads do not block writes. Thus unless you are familiar with your isolation level and if you're OK with dirty reads, then technically could. However that doesn't mean you should.

Read up on some of the issues you'll face with NoLock and alternatives.


NOLOCK Effects

What are the effects you need to worry about? Well, others, like Andrew Kelly, have described this before, Paul White digs quite deep into it, these search results are quite telling, and Kendra Little even has a video about it… but I'll point them out anyway:

"Dirty read" – this is the one most people are aware of; you can read data that has not been committed, and could be rolled back some time after you've read it – meaning you've read data that never technically existed.

Missing rows – because of the way an allocation scan works, other transactions could move data you haven't read yet to an earlier location in the chain that you've already read, or add a new page behind the scan, meaning you won't see it at all.

Reading rows twice – similarly, data that you've already read could be moved to a later location in the chain, meaning you will read it twice.

Reading multiple versions of the same row – when using READ UNCOMMITTED, you can get a version of a row that never existed; for example, where you see some columns that have been changed by concurrent users, but you don't see their changes reflected in all columns. This can even happen within a single column (see a great example from Paul White).

Index corruption – surely you are not using NOLOCK in INSERT/UPDATE/DELETE statements, but if you are, you should be aware that this syntax is deprecated and that it can cause corruption, even in SQL Server 2014 RTM – see this tip for more information. Note that you should check for the hint in any views that you are trying to update, too.

Read error – because the underlying data could be moved or deleted during your read.

| improve this answer | |

Most of us here would say that WITH (NOLOCK) should be used sparingly, if at all.

I'll assume that you're asking if WITH (NOLOCK) serves a purpose on a database in READONLY mode. WITH (NOLOCK) is most commonly used to ignore exclusive locks on tables during a query. With no updates, inserts, or deletes being possible, one would presume that a READONLY database should have no exclusive locks being taken (unless some SELECT query does so explicitly). So, practically speaking, using WITH (NOLOCK) in queries against a READONLY database should produce no useful benefits.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.