The MAXLOCKS Value
By default, the locking system escalates to a table-level lock after locking 50 pages or rows during a query.
Note: Lock escalation can lead to deadlock.
By changing the value for MAXLOCKS to a number greater than 50, you can increase the number of locks that are requested before escalation occurs.
Increasing this value requires more locking system resources, so the installation configuration for the maximum number of locks must be raised; but this can provide better concurrency in a table.
Change MAXLOCKS Value with SET LOCKMODE
The following statement changes the number of pages in the EMP table that can be locked during a transaction to 70:
set lockmode on emp where maxlocks = 70;
With the new MAXLOCKS value, the locking system escalates to a table-level lock only after more than twenty pages or rows have been locked in table EMP during a query.
Last modified date: 01/30/2023