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 may also acquire locks for internal use.
Each lock requires 36 or 72 bytes of the lock list, depending on whether other locks are held on the object:
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 (described below). Although the escalation process itself does not take much time, locking entire tables (versus individual rows) decreases concurrency, and overall database performance may decrease for subsequent accesses against the affected tables. Suggestions of how to control the size of the lock list are:
You can also use the LOCKSIZE parameter of the ALTER TABLE statement to control how locking is done for a specific table. For details, refer to the SQL Reference.
Use of the Repeatable Read isolation level may 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 may 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 may need to increase the value of this parameter or the maxlocks parameter. You may use the database system monitor to determine if lock escalations are occurring.
For more information see Number of Lock Escalations.
The following steps may help in determining the number of pages required for your lock list:
(512 * 36 * maxappls) / 4096
where 512 is an estimate of the average number of locks per application and 36 is the number of bytes required for each lock against an object that has an existing lock.
(512 * 72 * maxappls) / 4096
where 72 is the number of bytes required for the first lock against an object.
You may use the database system monitor to determine the maximum number of locks held by a given transaction.
For more information see Maximum Number of Locks Held.
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 may 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 PACKAGE command) after changing this parameter.