SQL 201 – Locking Hints

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

SELECT columnName FROM tableName (READCOMMITTED)

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.

INDEX

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) )

NOLOCK or READUNCOMMITTED

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)
SELECT columnName FROM tableName (READUNCOMMITTED)

READPAST

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.

UPDATE t1 SET
  t1.columnName = t2.columnName
FROM tableName t1
INNER JOIN tableName t2 (READPAST)
  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!

References

http://msdn.microsoft.com/en-us/library/ms187373.aspx

Tags: , , ,

No comments yet.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.