SQL Server: The truth about TABLE WITH (NOLOCK)

 Today morning, I got one question from one of my Facebook followers and he raised one question about the use of the TABLE WITH (NOLOCK) of the SQL Server.

Even in our team, what I have found is that some of junior developer every time use WITH (NOLOCK) for all the tables, which is wrong and force me to share the truth about TABLE WITH (NOLOCK) of SQL Server.

TABLE WITH (NOLOCK):

When you are selecting data with this option, that means you are selecting uncommitted data which are yet not permanent and there are still chances to roll back that data.

READ UNCOMMITED isolation level is equivalent to WITH (NOLOCK).

It is a common misconception that WITH (NOLOCK) always makes the queries run faster. If there are no write locks on a table, it does not make any difference. If there are locks on the table, it may make the query faster, but there is a reason locks were invented in the first place.

WITH (NOLOCK) not only returns wrong values, it also returns phantom records and duplicates.

We should consider INSERT, UPDATE and DELETE as a high priority transaction and SELECT as a low priority.

You should decide whether you want a DEAD LOCK or you want a Wrong Value information.

To SELECT the data WITH (NOLOCK) never requires any lock on the table and it never generates a DEAD LOCK.

But like big banking or finance transaction system, we should not use WITH (NOLOCK) otherwise you may get invalid data which are yet not committed.

You can use WITH (NOLOCK) for reporting database where data are already written and committed.

You can use WITH (NOLOCK) when you are very sure that it’s okay to read uncommitted data otherwise never use WITH (NOLOCK).