Initial Locking Level
In evaluating the query on which the lock is being requested, the optimizer determines the level of lock as follows:
• If a query involves a single table with only a primary key, page level locks are requested.
• If the optimizer estimates that no more than maxlocks pages are needed, the page level locks are requested.
• If the optimizer estimates that a query is touching more than maxlocks pages, the query is executed with a table level lock.
• If the optimizer estimates that a query is touching all the pages in the table, the query is executed with a table level lock.
This strategy saves the overhead of accumulating multiple page-level locks and prevents the contention caused by lock escalation. For example, on a query that is not restrictive or does not use a key to locate affected records, the locking system grants a table-level lock at the beginning of query execution.