WITH(NOLOCK) : where & when to use

Question

I have 15 tables which are normalized to contain records and many user may be inserting/updating data in these tables( No two user can update the same record(s) simultaneously, this is restricted by client implementation). these 15 tables are updated 1 by 1 in a stored procedure. WITH(NOLOCK) is used where select statements are to avoid read locks as some user may be viewing the data at same time. But sometimes there is locking in these select statements in these procedures. As my past experience I put these nolock to avoid blocking.

I am not sure if there is WITH(NOLOCK) is missing somewhere or this is some other problem. Should I search for missing WITH(NOLOCK)?

or what other ways are there to avoid this locking?

What are the restrictions with NOLOCK? I'm just reading the data and I don't care if I read committed or uncommitted data. What else steps can be taken to remove this blocking?

Answer 1

WITH(NOLOCK) is often exploited as a magic way to speed up database reads.

Only use WITH (NOLOCK) in SELECT statement on tables that have a clustered index.

Hint: The easiest way to ensure that a table has a clustered index is to add an auto-incrementing Id primary key column.

The result set can contain rows that have not yet been committed, that are often later rolled back.

If WITH(NOLOCK) is applied to a table that has a non-clustered index then row-indexes can be changed by other transactions as the row data is being streamed into the result-table. This means that the result-set can be missing rows or include the same row multiple times.

READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.

Locks will still be applied when INSERT, UPDATE or DELETE are used, and these could be the places where you are getting deadlocks.

Answer 2

I think what you want to do based on the comments that you left on your original post is set the isolation level to READ UNCOMMITED. This is the same as NO LOCK except that it sets it on the entire transaction and not on the individual select statement.

Code Example:

USE DatabaseName;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED;
GO
BEGIN TRANSACTION;
GO
...
COMMIT TRANSACTION;
GO

This should prevent any locks to the database for the reading. However this will also then apply to inserts, which could present a possible concurrency issue I would imagine. Data could change while being inserted.

I would reconsider this if you plan to do updates to the database under this isolation level.

Here is more detail of the ms sql isolation levels: https://msdn.microsoft.com/en-za/library/ms173763.aspx

Answer 3

Use WITH(NOLOCK), when you are fine using uncommitted data.

It gives you severe performance gain, because your query wont wait for any uncommitted transaction to complete. Its a great tool but has to be used with care otherwise you will end up with uncommitted data in scenarios you cannot afford.

Answer 4

WITH(NOLOCK) :

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level.

Basically it gives the dirty read. i.e. if any transaction hold some data and then we try to get the data it will give the output without wait of transaction commit. so that we get the dirty read without wait.

So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a3b1d05b-5479-485b-94c5-4f271adf5802/where-to-and-where-not-to-use-with-nolock?forum=transactsql