Skip to content

SQL 201 – Locking Hints

2007 January 15
tags: , , ,
by Shannon Lowder

If you’ve spent any time looking at another programmers code, I’m sure you’ve see something like this:

SELECT columnName FROM tableName (NOLOCK)

I’m sure you asked yourself what the (NOLOCK) was all about, right?  Well, it’s time I explained.  The keywords in the parenthesis are referred to as table or locking hints.  A lock is exactly what it sounds like, a device for preventing access.  In SQL terms, a lock can be created when you INSERT, UPDATE, or DELETE data from a table.  If one user does one of these three actions to a table, and you run a SELECT against that same table, you will be prevented from retrieving your data until the other user’s request has completed.

You can control how the server will respond to your request.  As long as you don’t see SET TRANSACTION ISOLATION LEVEL <option>in your code, then the SQL server will respond to your request as soon as no other query has a lock on the data you’ve requested.  This is the same as writing


This locking hint basically tells you that your command is willing to wait on all other locks and transactions to complete before attempting to return results.  If you need special access to your data, you can use one of any of the  following hints in your queries.


If you have a query that’s running a little slow, and you’ve created an index specifically to speed up this query, you can tell the SQL server to use this one index, and ignore all others.  To do that, simply use the hint INDEX(myIndex)  and replace myIndex with the name of the index you choose.

SELECT columnName FROM tableName (INDEX(myindex) )


Any time you’re doing a large load or change, you’re bound to need to “take a peek” at what’s going on.  This means you’ll need to look at the data as it’s changing.  Ordinarily this would be impossible, due to locking.  But, you can tell SQL server, I don’t care that this data is changing, just give me a view into the changes.  To make that happen, just use the NOLOCK or READUNCOMMITTED hint!

SELECT columnName FROM tableName (NOLOCK)


If you are checking for the existence of a certain case in a table, and you want it to run as quickly as possible, you will need to be introduced to READPAST.  This hint tells the SQL Server to not attempt reading any row where a lock exists.  If a row or page is locked, this hint says, don’t even attempt to read it, just keep on going.  There are some special rules to how you use this hint.  Check out the reference for a complete list of these rules.

  t1.columnName = t2.columnName
FROM tableName t1 
  ON t1.columnName = t2.columnName

There are several other locks available, but to be honest, they are used far less than these four.  Check out the reference for a complete guide to these hints.  If you have any questions, please send them in!


No comments yet

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS