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