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:

With the WebSphere Application Server Liberty active, log on as DB2® administrator to the DB2® server, for example,
su - db2inst1

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/

Run:
cd <Database_path>/db2event/db2detaildeadlock
Connect to the HCL Workload Automation database, for example:
db2 connect to TWS
Flush the event monitor that watches over deadlocks (active by default) with the following:
db2 flush event monitor db2detaildeadlock
Disconnect from the database with:
db2 terminate
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.
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
```
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
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
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
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®.