Monitoring the lock list memory

About this task

If the memory that DB2® allocates for its lock list begins to be fully used, DB2® can be forced into a "lock escalation", where it starts to lock whole tables instead of just individual table rows, and increasing the risk of getting into a deadlock.

This happens especially when there are long transactions, such as the creation or extension of a plan (production, trial, or forecast).

To avoid this problem occurring, set the automatic notification in the DB2® Health Center, so that you can be advised of any lock list problems building up.

However, if you think that deadlock situations have been occurring, follow this procedure to verify:
  1. With the WebSphere Application Server Liberty Base active, log on as DB2® administrator to the DB2® server, for example,

    su - db2inst1

  2. Run the following command to determine where the HCL Workload Automation database is located:

    db2 list active databases

    The output might be as follows:
    Database name                    = TWS 		
    Applications connected currently = 2 		
    Database path                    = /home/db2inst1/db2inst1/NODE0000/SQL00002/
  3. Run:

    cd <Database_path>/db2event/db2detaildeadlock

  4. Connect to the HCL Workload Automation database, for example:

    db2 connect to TWS

  5. Flush the event monitor that watches over deadlocks (active by default) with the following:

    db2 flush event monitor db2detaildeadlock

  6. Disconnect from the database with:

    db2 terminate

  7. Obtain the event monitor output with:

    db2evmon -path . > deadlock.out

    The file deadlock.out now contains the complete deadlock history since the previous flush operation.

  8. To find out if there have been deadlocks and when they occurred, run:

    grep "Deadlock detection time" deadlock.out

    The output might be as follows:
    Deadlock detection time: 11/07/2008 13:02:10.494600 		
    Deadlock detection time: 11/07/2008 14:55:52.369623
  9. But the fact that a deadlock occurred does not necessarily mean that the lock list memory is inadequate. For that you need to establish a relationship with lock escalation. To find out if there have been lock escalation incidents prior to deadlocks, run:

    grep "Requesting lock as part of escalation: TRUE" deadlock.out

    The output might be as follows:
    Requesting lock as part of escalation: TRUE 		
    Requesting lock as part of escalation: TRUE
    If there has been lock escalation related to deadlocks, it is a good idea to modify the values of the following parameters.
    LOCKLIST
    This configures, in 4KB pages, the amount of memory allocated to locking management
    MAXLOCKS
    This configures the percentage of the memory that a single transaction can use, above which DB2® escalates, even though the memory might not be full
  10. To determine the values currently being applied to the HCL Workload Automation database, do the following:

    db2 get db cfg for TWS | grep LOCK

    The output might be as follows:
    Max storage for lock list (4KB)              (LOCKLIST) = 8192
    Percent. of lock lists per application       (MAXLOCKS) = 60
    Lock timeout (sec)                        (LOCKTIMEOUT) = 180
    The example shows the typical output for the HCL Workload Automation database if no modification has taken place to these values:
    • "8192" = 4KB x 8192 pages = 32 MB of memory
    • "60" = 60% – the percentage of memory that a single transaction can occupy before triggering an escalation
    • "180" = 3 minutes of timeout for the period a transaction can wait to obtain a lock
  11. The most straightforward action to take is to double the amount of memory to 64MB, which you do with the command:

    db2 update db cfg for TWS using LOCKLIST 16384 immediate

  12. Alternatively, you can set DB2® to automatically modify the LOCKLIST and MAXLOCKS parameters according to the amount of escalation being experienced and the available system memory. This self-tuning is a slow process, but adapts the database to the needs of the data and the available system configuration. It is done by setting the values of these parameters to AUTOMATIC, as follows:

    db2 update db cfg for TWS using LOCKLIST AUTOMATIC immediate

    DB2® responds with messages telling you that MAXLOCKS has also been set to AUTOMATIC:

    SQL5146W "MAXLOCKS" must be set to "AUTOMATIC" when "LOCKLIST" is "AUTOMATIC".

    "MAXLOCKS" has been set to "AUTOMATIC"

    Note: The self-tuning facility is only available from V9.1 of DB2®.