Raoul, The locklist parm indentifies the number of pages available for the locklist in the database heap. Maxlocks defines the percentage of the total locklist used by an application before lock escalation will be triggered. In your case, you have a 1500 page locklist, and maxlocks set to 10%. With this, escalation will be triggered when any one application holds more than 600K of locks. Since each lock takes up either 36 or 72 bytes (72 for first lock on the object, 36 for the rest), this allows for 8533 72 byte locks per application (connection) or 17066 36 byte locks. If you are frequently hitting lock escalation, you may want to modify the applications to either commit more frequently, which will reduce the total number of locks held, or see if there are one or two applications that would be better off using table locks to avoid the time to take all of those page locks before the lock escalation occurs. Does the maxlocks get hit by a small subset of your workload, or is this occuring with all applications? If there are a small number of applications that take the large number of locks, and you are not having much contention due to the gross (ie Table) locks, you may want to consider the second option. If however, you are seeing a lot of contention due to waits for gross locks, I would suggest that the issue is due to the commit frequency. Remember also that the 10% figure for maxlocks is the highest amount of lock space before you get lock escalation. If the locklist is heavily utilized due to a large number of applications taking a lot of locks, it will trigger lock escalation earlier. If you read the sections on locking in the admin guide, you will see that IBM uses 512 as a basis for "average" number of locks per application. As you are allowing for a much higher number of locks, it may be a good idea to revisit the application design to either increase the commit frequency, reduce the use of repeatable read isolation, or add LOCK TABLE statements to the application. Wayne Driscoll Sr. Software Developer Quest Software http://www.quest.com/db2 [EMAIL PROTECTED] NOTE: All opinions are strictly my own. EMail Address in sig must be modified.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, February 04, 2004 5:24 AM To: [EMAIL PROTECTED] Subject: [DB2EUG] lock escalation Hi, We use DB2 v7.2 on AIX. We have a lot of lock escalations on our database. The locking params are: Locklists 1500 Maxlocks 10 I think we have to encrease the Locklists. But how do I know which value to use? Do you have a formula to calculate the correct values? What does this Maxlocks=10 mean? Is it 10% of 1500 so 150 locklists per application? What values do you use for how many users and system memory? Regards Raoul Joemman ***************************************************************** This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message. Martinair Holland N.V., its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt. ***************************************************************** - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
