October 1, 2009 at 12:53 pm
I just realized that when creating indexes in SQL 2005, the options to use row and page locks when accessing the index are on by default. Is this the best practice? What standard does everyone here use? It seems to me that you would want to avoid using locks to avoid deadlocking.
Thanks in advance for your input!
~Ruprecht
October 2, 2009 at 6:33 am
You have to have locking in order to insure consistency. Locking is part of what makes an RDBMS. Typically you would leave the defaults because ROW and PAGE locks are more granular they allow for greater concurrency. The downside to ROW and PAGE locks is that they cause more locks to be taken which takes more memory, typically this is not an issue.
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
October 2, 2009 at 12:17 pm
But wouldn't you want one or the other, instead of both?
October 2, 2009 at 1:13 pm
No, you want SQL Server to be able to determine what types of locks to take. SQL Server does a pretty good job of determining the best way to take locks. Row locks can escalate to page locks which can escalate to table locks. If you don't allow row or page locks then you limit the choices that sql server can make.
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
October 2, 2009 at 1:18 pm
Gotcha, thanks. I think it was the wording that threw me off. ('Use row locks...' and 'Use page locks...') It made me think it would always use both row and page locks for each operation instead of determining which is the better option.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply