You didn’t give any example of the code that is causing this behavior. SQL server can choose to escalate locks from row to page or even table locking, depending on the queries and transactions. (So will any database system.)
An update statement that doesn’t use the primary key, for example, will usually cause lock escalation. Also, if you include “Select” statements in a transaction and those do not use the “(nolock)” hint, you will frequently see locks escalate up to the table level.
To minimize lock escalations, try the following:
1. Ensure all Update (or Delete) statements only use the primary key to specify the records to be affected.
2. Keep transactions as short as possible. Avoid loops or multi-row updates or deletes within a single transaction. If your number-one concern is locking and not performance, it is better to do updates and deletes as single-row transactions, with a “commit” after each row.
3. Where possible, add the (nolock) hint to your select statements. Note that you need to understand the possible side effects before doing this to lots of statements, and you may want to specify a different transaction isolation level in different transactions. (Account ledger updates probably need to be serializable, for example.) Don’t go too crazy on this, but there are usually a lot of places where (nolock) can be used safely, and these will definitely reduce lock contention.