Don't forget about locking hints! (posted by Aaron)

I'm a big fan of using locking hints when writing SQL queries. And I'm continually amazed at how many people don't understand (or know) how to use them. 

Locking hints can help to significantly improve performance of your database and with very little work. The key is to understand the different types of locking hints and make sure you're using them properly. I'm not going to try to explain them in detail here (there's plenty of good documentation already out there), but rather encourage those who might not understand them to take a look and put them into practice.

MSDN has them documented here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_1hf7.asp

Here's a quick example… Let's say you have a table that is read from frequently and also updated frequently. Let's also say that individual users who are accessing the data in this table are almost exclusively reading their own data... and not data belonging to someone else. And let’s also assumes that the data is read frequently enough that you're not overly concerned about “dirty reads” - reading data that is being changed or is uncommitted.

Most people would quickly write an update statement that looked something like this:
UPDATE tbl_SomeData
SET MyField = @SomeValue
WHERE MyID = @SomeID

Now, there’s absolutely nothing wrong with this statement. It’s syntactically correct and it works. What most people don’t realize however is that this statement could be issuing broad locks on tbl_SomeData and locking others out of the table for the duration of the update. Now, you might argue that this isn’t a big deal, and in certain environments it might not be. But as the application executing this statement grows this simple little statement could start to create some real problems down the line.

So, how do we fix it? Well, give the assumptions made above we could add the ROWLOCK hint and tell SQL to only lock the affected rows rather than issuing the coarser grained page and table locks. Something like this:

UPDATE tbl_SomeData WITH (ROWLOCK)
SET MyField = @SomeValue
WHERE MyID = @SomeID

Now, having said all this, it’s extremely important that you don’t start issuing blanket rules across all your tables and applications. Most databases are unique in their own way. It’s crucial to carefully consider how your data is being used and apply the appropriate locking hints for each situation. 

Happy locking!
Aaron