troubleshooting Question

Stopping Lock Escaltion in SQL Server 2005

Avatar of SWS001
SWS001 asked on
DatabasesMicrosoft SQL Server 2005
1 Comment1 Solution258 ViewsLast Modified:
I have been experiencing intermittent deadlocking on one of our dbs.  I have been researching around the issue and ways of getting around it and I have decided to work on an incident by incident basis and try and rewrite sps etc to avoid the problem - perhaps start using the WITH clauses in order to specify the granularity and locking to be used on a per transaction basis and maybe adjusting the table options to disallow certain more coarse locks.

Whilst looking into this I have found that a lot of people suggest that even if you specify a particular lock type to use, SQL Server may well choose to escalate the lock anyway.  Even if for example you have turned off PAGE and Table locking for a particular table.  Can anyone clarify this please?

Also, If it was possible to specify that the whole db should only ever use row level locking what would be the likely side effects apart from a huge memory usage?

Thank you in advance
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros