Parameters related to disk I/O | Parameters related to agents management


DB2 Parameters related to locking


+

Search Tips   |   Advanced Search


Reducing locking contention is key to performance. Several parameters exist to influence locking behavior. The total amount of memory available to the database for locks is defined by the LOCKLIST parameter. The MAXLOCKS parameter defines the maximum amount of memory available for each connection to the database. It is represented as a percentage of the LOCKLIST.

Both of these parameters need to be sized appropriately in order to avoid lock escalations. A lock escalation occurs when all of the memory available to a connection is used, and multiple row locks on a table are exchanged for a single table lock. The amount of memory used for the first lock on an object is 72 bytes, and each additional lock on the same object is 36 bytes.

A good starting value for LOCKLIST can be approximated by assuming that a connection requires about 512 locks at any given time. The following formula can be used:

LOCKLIST = (512 locks/conn * 72 bytes/lock * # of database connections) / 4096 bytes/page

MAXLOCKS can be set to between 10 and 20 to start. Further monitoring will be necessary to adjust both of these values. In the database snapshot output, look for the following lines:

Lock list memory in use (Bytes)            = 432
Lock escalations                           = 0
Exclusive lock escalations                 = 0

If lock escalations occur (value higher than 0), increase the locklist to minimize the escalations or increase the MAXLOCKS value to increase the limit of how much of the LOCKLIST a connection can use.