Remove some SQL Server blocking issues with the NOLOCK hint

By: Greg Robidoux   |   Updated: 2007-04-27   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Performance Tuning

Problem

Often many applications utilize the same database and it may be quite difficult to manage locking issues that occur which may lead to significant blocking issues and therefore potential performance issues. In a perfect world this would never be the case and everything would run without issue. Unfortunately locking and blocking are real things that occur regardless of how well you architect your database application, so the name of the game is to minimize this as much as possible. As mentioned already, there may be multiple applications hitting the same database such as a transaction based application and a reporting based application. So what can be done to minimize the blocking issues?

Solution

SQL Server offers many hints that can be used to determine how a query executes and therefore the impact of the query on other processes. One such hint that is offered in SQL Server is the NOLOCK hint. This query hint allows SQL Server to ignore the normal locks that are placed and held for a transaction and allows the query to complete without having to wait for the first transaction to finish and therefore release the locks.

Here is an example. First we create a new table called testTable and insert some sample data.

CREATE TABLE testTable 
(
id int,
firstName varchar(10)
)

INSERT INTO testTable VALUES (1, 'Jim')
INSERT INTO testTable VALUES (2, 'Jane')
INSERT INTO testTable VALUES (3, 'Steve')

After the data has been inserted we begin a transaction and update one of the records.

BEGIN TRAN
UPDATE testTable SET firstName = 'George' WHERE id = 3

If we open up a new query window and try to run the query below, the query will not complete because it is being blocked by the first query above which we have neither committed or rolled back and therefore the locks are still being held and this second query below can not complete until the first transaction is totally complete.

SELECT * FROM testTable

At this point we can either cancel the second query that does the SELECT or we can commit or rollback the UPDATE query for everything to complete. Another option is to run the query with the NOLOCK hint as written below.

SELECT * FROM testTable (nolock)

When this query runs you will see that the query completes, but the problem we have is that the data for ID =3 shows "George" where it should still be "Steve".

Since we never committed the UPDATE transaction the data should still have the value of "Steve", but since we are using the NOLOCK hint this overrides all locks that are currently being held and therefore gives you the result set of what the data currently looks like in the database. This is known as a dirty read. The advantage of NOLOCK is that it does not have to wait for the other transaction to complete, but the disadvantage is that is gives you incorrect data. If the UPDATE statement later gets committed, then you get lucky and have the correct data, but if the UPDATE gets rolled back the result set is incorrect.

Summary

So the use of the NOLOCK hint allows you to get a result set without having to deal with the locking and blocking issues, but the result may be incorrect. This is something you need to weigh to determine if eliminating the blocking issue is more important then returning some data that may not be 100% correct. The best solution would be to determine how to remove the blocking problems that you are experiencing, but in some cases this may not be possible. Also, when running reports having some dirty data in the result set may not be a big issue compared to the alternative of not being able to do anything.

Next Steps

• Now that you know this query hint, NOLOCK, exists check to see if you have blocking issues that may benefit from using this.
• The best solution is to eliminate the blocking problems, but this is not always so simple. If you can not get rid of the severe blocking issues, see if using this option can help.
  

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips