13. Understanding the Locking System : User-Controlled Locking--SET LOCKMODE : When to Change the Locking Level
 
Share this page                  
When to Change the Locking Level
There are several situations where the page-level locking default is not appropriate:
If a query is not restrictive or does not make use of the key for a table, scanning the entire table is required. In that case, the locking system automatically starts with a table-level lock; you do not need to specify it.
If there are a number of unavoidable overflow pages, for reasons of efficiency, it is preferable to set table-level locking.
If, during execution of a query, more than maxlocks pages on a table are locked (maybe caused by overflow chains), the locking system escalates to a table level lock releasing the page locks that has been accumulated. Accumulating page locks when a table lock was needed is a waste of resources.
If multiple users are concurrently running queries to change data, deadlock can occur. Deadlocks (see page Deadlock) occur when multiple transactions are waiting for each other to release locks that the other requires.
If page locking causes unnecessary contention, row-level, or MVCC-level locking can be used.