DB2 10.5 for Linux, UNIX, and Windows

maxlocks - Maximum percent of lock list before escalation configuration parameter

This parameter defines a percentage of the lock list held by an application that must be filled before the database manager performs lock escalation.

Configuration type

Database

Parameter type

Configurable
Configurable by member in a DB2® pureScale® environment

Default [range]

Automatic [1 - 100 ]

Note: The default value is subject to change by the DB2 Configuration Advisor after initial database creation.

Unit of measure

Percentage

Lock escalation is the process of replacing row locks with table locks, reducing the number of locks in the list. When the number of locks held by any one application reaches this percentage of the total lock list size, lock escalation will occur for the locks held by that application. Lock escalation also occurs if the lock list runs out of space.

The database manager determines which locks to escalate by looking through the lock list for the application and finding the table with the most row locks. If after replacing these with a single table lock, the maxlocks value is no longer exceeded, lock escalation will stop. If not, it will continue until the percentage of the lock list held is lower than the value of maxlocks. The maxlocks parameter multiplied by the maxappls parameter cannot be less than 100.

When this parameter is set to AUTOMATIC, it is enabled for self tuning. This allows the memory tuner to dynamically size the memory area controlled by this parameter as the workload requirements change. Because the memory tuner trades memory resources between different memory consumers, there must be at least two memory consumers enabled for self tuning in order for self tuning to be active.

Although the value of locklist can be tuned together with the maxlocks parameter, disabling self tuning of the locklist parameter does not automatically disable self tuning of the maxlocks parameter. Enabling self tuning of the locklist parameter automatically enables self tuning of the maxlocks parameter.

Automatic tuning of this configuration parameter will only occur when self tuning memory is enabled for the database (the self_tuning_mem configuration parameter is set to ON).

On all platforms, each lock requires 128 or 256 bytes of the lock list, depending on whether other locks are held on the object:

256 bytes are required to hold a lock on an object that has no other locks held on it.
128 bytes are required to record a lock on an object that has an existing lock held on it.

Recommendation: The following formula allows you to set maxlocks to allow an application to hold twice the average number of locks:

   maxlocks = 2 * 100 / maxappls

Where 2 is used to achieve twice the average and 100 represents the largest percentage value allowed. If you have only a few applications that run concurrently, you could use the following formula as an alternative to the first formula:

   maxlocks = 2 * 100 / (average number of applications running 
concurrently)

One of the considerations when setting maxlocks is to use it in conjunction with the size of the lock list (locklist). The actual limit of the number of locks held by an application before lock escalation occurs is:

maxlocks * locklist * 4096 /(100 * 128)

Where 4096 is the number of bytes in a page, 100 is the largest percentage value allowed for maxlocks, and 128 is the number of bytes per lock. If you know that one of your applications requires 1000 locks, and you do not want lock escalation to occur, then you should choose values for maxlocks and locklist in this formula so that the result is greater than 1000. (Using 10 for maxlocks and 100 for locklist, this formula results in greater than the 1000 locks needed.)

If maxlocks is set too low, lock escalation happens when there is still enough lock space for other concurrent applications. If maxlocks is set too high, a few applications can consume most of the lock space, and other applications will have to perform lock escalation. The need for lock escalation in this case results in poor concurrency.

You can use the database system monitor to help you track and tune this configuration parameter.