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

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

John Ingres — Fri, 30 Dec 2005 01:11:56 GMT

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.

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

dditweb — Fri, 30 Dec 2005 01:54:23 GMT

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

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

tom — Fri, 09 May 2008 18:12:00 GMT

LOL - someone actually promoting bad practices or maybe this is sarcasm?