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
- Parameter type
- Configurable Online
- 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.)
- 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.
- 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.
a lower bound for the size of your lock list, using one of
the following calculations, depending on your environment:
(512 * 128 * maxappls) / 4096
- with Concentrator enabled:
(512 * 128 * max_coordagents) / 4096
- 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
256is 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.