Is SQL NOLOCK bad practice?

In this post we’ll read about the use of [Nolock] in SQL Queries and stored procedures. Is this good or bad practice to use this in query. Let’s check out below.

• If you don’t care about dirty reads (i.e., application has no sensitive data like no payment details), then NOLOCK is fine.
• When querying single values/rows it’s always bad practice to use NOLOCK — you probably never want to display and update incorrect information.
• It is bad practice to use this in payments or banking applications or in transactions. It can be used only in large report generation which means for read only data.
• When displaying rough statistical information, NOLOCK can be very useful.
• If you have static data in table and it is used to select and display, then no harm of using nolock.
• If table data is getting updated very frequently then nolock should not be used.

Additionally, as an alternative to nolock, consider “read committed snapshot”, which is meant for databases with heavy read and less write activity. You can turn it on with:

ALTER DATABASE codeConfigDB SET READ_COMMITTED_SNAPSHOT ON;

WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Both of these are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the present connection, so if you only want a [nolock] on one or two tables use that in your query with table otherwise can use TRANSACTION ISOLATION LEVEL.

Hence, both will give you dirty reads. If one is okay with that, then can use this. If you don’t want dirty reads, then you can consider snapshot or serializable hints too.

• NOLOCK is local to the table or views etc.
• READ UNCOMMITTED is per session/connection.

Conclusion:

So, depending upon the above parameters use of nolock in SQL query can vary from individual to individual and their needs. You can see which option is fit as per your needs and can use that.