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
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.
Change the Locking Level with SET LOCKMODE
To specify table-level locking:
set lockmode session where level = table;
To specify row-level locking:
set lockmode session where level = row;
To specify multiversion concurrency control:
set lockmode session where level = mvcc;
Note: For a discussion on what happens when you change the locking level with SET LOCKMODE=MVCC, see the chapter "Understanding Multiversion Concurrency Control."
Last modified date: 04/03/2024