A lot of people use NOLOCK table hint in their SELECT queries, some of them are expert and well aware of its pros and cons, but some are not. So, In this post we are going to focus on this table hint and its impact.
What is NOLOCK
NOLOCK and READUNCOMMITTED hints used with SELECT queries to get the data without issuing shared lock (which prevents modification of data by other transactions during read operation) on the data, and also allows to read the data acquired with exclusive lock by other transactions. NOLOCK hint is equivalent to READUNCOMMITTED isolation level which is least restrictive in SQL Server. It does not restrict the dirty reads and increases the concurrency by reducing blocking.
It holds a Sch-S (Schema stability) lock during its execution scope which blocks the transactions requesting for Sch-M (Schema modification) lock, required by DDL operations. Reverse is also true that any Sch-M lock will block the Sch-S lock request until it hold the resources.
Also remember, it can be used only with SELECT statements and not with tables being modified by DELETE, UPDATE or INSERT statements.
Syntax
SELECT * FROM TABLENAME WITH(NOLOCK) SELECT * FROM TABLENAME WITH(READUNCOMMITTED)
Pros and cons of NOLOCK
Pros
1. Reduces blocking:
Least restrictive isolation level of SQL Server READ UNCOMMITTED used during compilation and execution which increases the concurrency with cost of dirty reads. Readers can read and writers can write at the same time.
2. Can change Isolation level for individual tables:
NOLOCK hint reads uncommitted data from specified tables by changing the isolation level for those tables only during execution of the query. Isolation level can be handled at table level instead of query level using NOLOCK table hint.
Cons
1. Dirty Reads:
Reading data which is not committed yet is a “dirty” data, which may or may not be committed. READ UNCOMMITTED isolation level allows to read uncommitted data from other concurrent transactions which may lead to these dirty reads.
2. Non Repeatable Reads
In case, you read the data repeatedly and its changes during this process, may leads to different data during each read for same row.
3. Phantom Reads
Phantom read occurs when a delete or insert is performed on a row being read by a transaction. So delete will cause less record whereas insert will cause more records,
Conclusion
We can achieve the same thing by setting the isolation level to READ UNCOMMITTED. But it changes the isolation level for entire query and NOLOCK hint works at table level. In case you have many tables and some of them are read only, you can set the NOLOCK hint (in turn READ UNCOMMITTED isolation level) on those tables only.
Yup! We can use any of them. Thanks 😉
Also, be aware that, “Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.”
https://msdn.microsoft.com/en-us/library/ms187373.aspx
As I said before, just don’t ever use NOLOCK. If you do, in spite of all warnings against, you will have to remove it eventually.
“In case you have many tables and some of them are read only, you can set the NOLOCK hint”
That is terrible advice. Just because you are “only reading” a table, does not mean no one else is writing to them. Even if you have stopped all write activity (you think!) through constraints, triggers, permissions, setting the database readonly etc, etc., there is no guarantee that these things may not change in the future. A future DBA may remove all these restraints for perfectly valid business reasons. After that, your NOLOCK tables will eventually give you dirty reads.
I spend part of each day ripping NOLOCK out of queries. I even see them in queries related to financial or regulatory matters, where NOLOCK is, always and forever, a very, very bad idea and in some jurisdictions, can lead to civil and even criminal charges due to improper reporting.
I forbid the use of NOLOCK in my shop. For some DBAs who think they need/want to use it and can show a valid business case for it, I allow WITH (READUNCOMMITTED) (the synonym that shows the true nature of the table hint) and then only for ad hoc one-off queries — never for production code. For everyone else, I stop at WITH (READPAST) and then only with a solid business argument.
Hi Gerald Britton,
Thanks for your comment.
Here “In case you have many tables and some of them are read only, you can set the NOLOCK hint”, I am not promoting the use of NOLOCK and just shared a way of enabling READUNCOMMITTED isolation level on individual table. I have also shared the cons of NOLOCK in this post too. Thanks for reading and please keep posting 🙂
You can enable READUNCOMMITTED on a single table with the table hint WITH (READUNCOMMITTED). More descriptive than NOLOCK. but don’t use it anyway!
Hi Gopal
Just one correction, the no lock hint will still place shared locks on the database and the tables.
What nolock does is that it will instruct SQL Server to read all data and ignore existing locks.
Regards
Holger
Hi @Holger,
Thanks for your comment and please don’t take my wordings wrong, but what you have corrected is partially true. NOLOCK or READUNCOMMITTED does not issue any shared lock on table or data which prevents other transactions to modify the data.
NOLOCK issues “Sch-S” lock on the Table and Shared lock on the database during its execution. In case of Heap tables it will put a “Sch-S” lock on table and “S” lock on database and on hobbit. But if table accommodates a clustered index, it will put a “S” lock on database and “Sch-S” lock on table. You can refer this link for more details on this topic “msdn.microsoft.com/…/ms187373.aspx”;.
Again i am saying “please don’t take it wrong”. I am thankful to you for your suggestion / comment and expect a lot more from your side. Keep it up and have a good day ahead. Thanks to all for your readings and supports.
I think you should also talk about the execution plans and the differences between the io and cpu for each of the situations. For many applications, and particuarly low memory sql instances this can make a decision point for using it or not. I also believe you should be much more specific about fast transaction database tables vrs low tranaction tables, your discussion tells about it on a medium level that most dba’s would be able to translate, but in all honesty, most dba’s are junior level at best and wouldn’t make this connection. And don’t get me wrong, you have a great bit of good data here, I just think you should write it for a lower level audience as medium to high level dba’s should already know these things.
Hi @Cathan, Thanks for your comment and suggestion too. Please keep posting your suggestions which can guide me to express myself in better way.