Locks Held

 
Snapshot Level
Database
Application
Lock
 

 
Logical Data Grouping
dbase
appl
dbase_lock
appl_lock

 
Monitor Switch
Basic
Basic
Basic
Basic

Resettable

No

Element Name
Element Type

locks_held
gauge

Description:  The number of locks currently held.

Usage:  If the monitor information is at the database level, this is the total number of locks currently held by all applications in the database.

If it is at the application level, this is the total number of locks currently held by all agents for the application. How you use this element depends on the level of information being returned from the database system monitor.

• At the database level, you can use it in one of two ways:

  • This element can provide summary information about locking. For example, you can calculate the average number of locks per application by dividing the value of this element by "Applications Connected Currently". If the resulting number is high, it may indicate that you can tune one of your applications to improve performance.

  • You can also compare the value of this element against the results of the following formula to determine the number of additional locks that may be requested. This comparison can help you determine if the configuration parameters need adjusting or your applications need tuning.

     
      (locklist * 4096 / 36 ) - locks held = # remaining
    

    where:

    • locklist is the configuration parameter as described in the Administration Guide
    • 4096 is the number of bytes in one 4K page
    • 36 is the number of bytes required for each lock.

    Note:

    You may also use Total Lock List Memory In Use in a similar fashion.

• At the application level, you can use this counter to determine if the application is approaching the maximum number of locks available to it, as defined by the maxlocks configuration parameter. This parameter indicates the percentage of the lock list that each application can use before lock escalations occur. Lock escalations can result in a decrease in concurrency between applications connected to a database. (See the Administration Guide for more information about this parameter.)

  Since the maxlocks parameter is specified as a percentage and this element is a counter, you can compare the count provided by this element against the total number of locks that can be held by an application, as calculated using the following formula:

   
    (locklist * 4096 / 36 ) * (maxlocks / 100)
  

  If you have a large number of locks, you may need to perform more commits within your application so that some of the locks can be released.