DB2 10.5 for Linux, UNIX, and Windows

locklist - Maximum storage for lock list configuration parameter

This parameter indicates the amount of storage that is allocated to the lock list. There is one lock list per database and it contains the locks held by all applications concurrently connected to the database.

Configuration type

Database

Parameter type

Configurable
Configurable by member in a DB2® pureScale® environment

Default [range]

Automatic [4 - 134217728]

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

Unit of measure

Pages (4 KB)

When allocated

When the first application connects to the database

When freed

When last application disconnects from the database

Locking is the mechanism that the database manager uses to control concurrent access to data in the database by multiple applications. Both rows and tables can be locked. The database manager can also acquire locks for internal use.

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.

When the percentage of the lock list used by one application reaches maxlocks, the database manager will perform lock escalation, from row to table, for the locks held by the application. This calculation is an approximation, assuming shared locks only. The percentage of the lock list used is calculated by multiplying the number of locks held by the application by the value required to hold a lock on an object that has other locks held on it. Although the escalation process itself does not take much time, locking entire tables (versus individual rows) decreases concurrency, and overall database performance might decrease for subsequent accesses against the affected tables. Suggestions of how to control the size of the lock list are:

Perform frequent COMMITs to release locks.
When performing many updates, lock the entire table before updating (using the SQL LOCK TABLE statement). This will use only one lock, keeps others from interfering with the updates, but does reduce concurrency of the data.
You can also use the LOCKSIZE option of the ALTER TABLE statement to control how locking is done for a specific table.

Use of the Repeatable Read isolation level might result in an automatic table lock.
Use the Cursor Stability isolation level when possible to decrease the number of share locks held. If application integrity requirements are not compromised use Uncommitted Read instead of Cursor Stability to further decrease the amount of locking.
Set locklist to AUTOMATIC. The lock list will increase synchronously to avoid lock escalation or a lock list full situation.

Once the lock list is full, performance can degrade since lock escalation will generate more table locks and fewer row locks, thus reducing concurrency on shared objects in the database. Additionally there might be more deadlocks between applications (since they are all waiting on a limited number of table locks), which will result in transactions being rolled back. Your application will receive an SQLCODE of -912 when the maximum number of lock requests has been reached for the database.

Recommendation: If lock escalations are causing performance concerns you might need to increase the value of this parameter or the maxlocks parameter. You can use the database system monitor to determine if lock escalations are occurring. Refer to the lock_escals (lock escalations) monitor element.

The following steps might help in determining the number of pages required for your lock list:

Calculate a lower bound for the size of your lock list, using one of the following calculations, depending on your environment:
1. ```
  (512 * 128 * maxappls) / 4096
```
2. with Concentrator enabled:
```
  (512 * 128 * max_coordagents) / 4096
```
3. in a partitioned database with Concentrator enabled:
```
  (512 * 128 * max_coordagents * number of database partitions) / 4096
```
where 512 is an estimate of the average number of locks per application and 128 is the number of bytes required for each lock against an object that has an existing lock.
Calculate an upper bound for the size of your lock list:
```
  (512 * 256 * maxappls) / 4096
```
where 256 is the number of bytes required for the first lock against an object.

Note: While in a DB2 pureScale environment, set the locklist configuration parameter to be equal to the upper bound of the value calculated in this step and 3% of the total number of pages for all of the buffer pools existing in the currently connected database.
Estimate the amount of concurrency you will have against your data and based on your expectations, choose an initial value for locklist that falls between the upper and lower bounds that you have calculated.
Using the database system monitor, as described in the following paragraph, tune the value of this parameter.

If maxappls or max_coordagents are set to AUTOMATIC in your applicable scenario, you should also set locklist to AUTOMATIC.

You can use the database system monitor to determine the maximum number of locks held by a given transaction. Refer to the locks_held_top (maximum number of locks held) monitor element.

This information can help you validate or adjust the estimated number of locks per application. In order to perform this validation, you will have to sample several applications, noting that the monitor information is provided at a transaction level, not an application level.

You might also want to increase locklist if maxappls is increased, or if the applications being run perform infrequent commits.

You should consider rebinding applications (using the REBIND command) after changing this parameter.