Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Remove some SQL Server blocking issues with the NOLOCK hint


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


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.
     


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools