The lock escalation can also be forced by the 

MAXLOCKS

 database parameter, 

which defines a limit for the maximum percentage of the lock storage held by one 

application. The default value for Linux and UNIX is 10 (22 for Windows), which 

means that if one application requests more that 10% of total locks space 

(

LOCKLIST

), an escalation will occur for the locks held by that application. As an 

example, inserting 1137 rows on Linux with one transaction will result in lock 

escalation, because the transaction requests 1138 locks (one per each inserted 

row plus one internal lock), which requires at least 1138*36 = 40968 bytes   more 

than 10% of global lock memory defined by the default 

LOCKLIST

 parameter.

Initial values for 

LOCKLIST

 and 

MAXLOCKS

 should be based on the maximum 

number of applications and average number of locks requested by the 

transaction (for OLTP systems start with 512 locks for every application). When 

setting 

MAXLOCKS

, you should take into account lock consuming batch processes 

that run during daytime hours. To check current usage of locks use snapshots 

such as in Example 9 34.

Example 9 34   Invoking snapshot for locks on database sample

db2 get snapshot for locks on sample

The snapshot will collect the requested information at the time the command was 

issued. On Figure 9 9 you can find a sample lock snapshot output. For the time 

the snapshot was run there were two applications connected to the database 

SAMPLE

, and in total 1151 locks were acquired on the database. Issuing the 

GET 

SNAPSHOT 

command later can produce different results because in the meantime 

the applications may commit the transaction and release the locks.

 Chapter 9. Testing and tuning 

305