May 7, 2008 at 10:05 am
Hi Friends,
Is it recommended to follow the locking hints in the Sql query or not?
If the database is highly transactional, should I follow locking hints or not?
And also I need some guide line for locking hints in Sql Server 2005.
Cheers!
Sandy.
--
May 7, 2008 at 10:12 am
Locking hints are typically not recommended unless you are a very advanced SQL guru. There are instances where they can help. I have tended to use nolock on many selects for reporting as most reports I have written are not adversely affected by the occassionaly dirty read. For updates and deletes I prefer to let SQL Server take care of the locking as it does a pretty good job, especially if indexed correctly.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 7, 2008 at 11:08 am
Generally, don't use them. Ever. Yes, there are exceptions to that rule, but you need to be extremely careful when deciding you have an exception. You may not actually have an exception.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2008 at 12:14 pm
Hints in general should be avoided unless you really know what you're doing and have a very, very good reason to use them.
Badly applied locking hints can cause major problems. I'm busy cleaning (NOLOCK) out of a system where the original architect thought that read uncommitted behaved like snapshot isolation
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply