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.
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
The value of locklist is tuned together with the maxlocks parameter, therefore disabling self tuning of the locklist parameter automatically disables 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.")
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.
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.
(512 * 128 * maxappls) / 4096
(512 * 128 * max_coordagents) / 4096
(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.
(512 * 256 * maxappls) / 4096
where 256 is the number of bytes required for the first lock against an object.
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.