The Never Escalate Approach
The "never-escalate" approach is appropriate when users are working in different parts of the table and they are running simple queries and updates that make full use of table structures and indexes.
The goal is to have users coexist in the same tables, where no one impedes another’s performance by acquiring table locks.
Considerations include:
• A single-table keyed query starts with page locking, unless the SET LOCKMODE statement has been issued. Page locks are acquired until MAXLOCKS is reached, at which point lock escalation occurs. It is better to overestimate the number of pages that will be locked in a specific table than underestimate.
• If the query optimizer thinks that MAXLOCKS pages are used, a table level lock may be taken.
• For each table, check how many pages are returned from a keyed lookups to ensure that it is less than MAXLOCKS .
• Optimizedb must be run to help the Query Optimizer make sensible estimates.
• When choosing storage structures, ISAM or HASH structures are better than small B-trees. Growing B-trees involve more locking when index pages are split.
• Monitor overflow levels in ISAM and HASH structures and indexes. As overflow increases in ISAM or HASH tables, they become inferior to B-trees, due to locks being held down overflow chains. If any overflow chain length is greater than MAXLOCKS, escalation to table locks may occur. At what point the trade-off occurs depends on the circumstances, such as how frequently MODIFY statements can be performed.
• Reduce fillfactors on tables with ISAM or HASH structures, as this provides more free space per page in the table after modify.
• Make sure MAXLOCKS is set to an appropriate figure, such as ten percent of table size.