Share via

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

  • Article

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:
https://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

Comments

  • Anonymous
    December 29, 2005
    I disagree.
    1. SQL uses row locking by default. If it escalates to pages locks, or worse table lock, it is because it is less expensive to do so. Forcing a row lock may decrease the server's scalability by consuming more memory than necessary for that query.
    2. If you have locking problems, look at your query AND your indexes. "Let's also say that individual users who are accessing the data in this table are almost exclusively reading their own data" ... using MyID as the first column of your clustered index perhaps;)
    3. SQL 2005 has row versionning. Enable it and forget your locking hints :-D

    ps: I am not againts locking hints, but I consider them a last resort and a general bad practice. A hint that helps today will cause problems in a few months/years because data distribution has changed.
  • Anonymous
    December 29, 2005
    Interesting thoughts. I guess I've never found a time when using locking hints caused problems down the road. Don't get me wrong - I'm all for indexes. They're MUCH more important than locking hints (I'm not arguing that they aren't)... I think what I'm trying to get across more than anything is that I find tons of folks who never even consider locking hints (and don't undesrtand them) and I've found them to be a big help.

    Like I said in the last sentence - blanket rules for locking hints is not recommended. But rather understanding them, understanding your data, and using them when necessary.

    Aaron
  • Anonymous
    May 09, 2008
    LOL - someone actually promoting bad practices or maybe this is sarcasm?