Parameter Wednesday – DB CFG – LOCKLIST

DB2 Version This Was Written For

9.7

Parameter Name

LOCKLIST

Where This Parameter Lives

Database Configuration

How To Check Value

$ db2 get db cfg for wc005s01 |grep LOCKLIST
 Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(4096)

OR

$ db2 "select name, substr(value,1,16) value, value_flags, substr(deferred_value,1,16) deferred_value, deferred_value_flags, substr(datatype,1,16) datatype from SYSIBMADM.DBCFG where name='locklist' with ur"

NAME                             VALUE            VALUE_FLAGS DEFERRED_VALUE   DEFERRED_VALUE_FLAGS DATATYPE
-------------------------------- ---------------- ----------- ---------------- -------------------- ----------------
locklist                         4096             AUTOMATIC   4096             AUTOMATIC            BIGINT

  1 record(s) selected.

Description

Specifies the maximum amount of memory to use for a list of locks within the DB2 database. DB2 stores lock information in-memory in this one location. The locklist is not written to disk.

Impact

Can cause performance degradation if the locklist becomes full. Once the locklist becomes full, DB2 will escalate row-level locks to table level locks, which can significantly impact the concurrency of connections to the database. Especially for E-Commerce databases, it is important to have a large enough lock list.

Default

AUTOMATIC

Range/Values

4 – 134217728

Recycle Required To Take Effect?

No – but you should do a db2rbind all if you change this parameter.

Can It Be Set To AUTOMATIC?

Yes, and that is the recommended starting point, assuming you’re using STMM. If you set it to AUTOMATIC, MAXLOCKS should also be set to AUTOMATIC. If MAXAPPLS or MAX_COORDAGENTS are set to AUTOMATIC, LOCKLIST should also be set to AUTOMATIC

How To Change It

 db2 update dbm cfg for dbname using LOCKLIST 4096

Rule of Thumb

Set to AUTOMATIC if you are using STMM. If you are not using STMM, somewhere around 5000 is a good starting point for e-commerce databases.

Tuning Considerations

The db2 configuration advisor may change this parameter.

There are some detailed formulas in the DB2 Info Center that you can use to determine upper and lower bounds of possible values, but they are based on knowing the average number of locks per application. I won’t cover the actual formulas here, but will go into detail on some of the components.

• 256 is the number of bytes used in the locklist for the first lock on an object
• 128 is the number of bytes used in the locklist for locks on objects that already have at least one other lock against them
• The average number of locks per application can be determined in an existing database with load on it by looking at the locks_held_top monitor element. This is an event monitoring element, so there is some work involved with looking at this.

The main time you’re going to increase LOCKLIST (assuming you’re not using STMM and AUTOMATIC) is when you see lock escalations. Every time you look at database performance or review your diag log, you should look for lock escalations. By default, lock escalations are written to the diag log and are also counted in the snapshot monitor. You can look at the number of lock escalations since the database was started using this syntax:

$ db2 "select varchar(workload_name,30) as workload_name, lock_escals FROM TABLE(MON_GET_WORKLOAD('',-2)) AS t"

WORKLOAD_NAME                  LOCK_ESCALS
------------------------------ --------------------
SYSDEFAULTUSERWORKLOAD                            0
SYSDEFAULTADMWORKLOAD                             0

  2 record(s) selected.

If you see ANY lock escalations, especially in an e-commerce database, you need to tune locklist or change your application’s behavior to avoid them. Lock escalations are a very bad thing, and even one lock escalation should be looked into and resolved.

Related Error Messages

You can see performance degradation if locklist is too small without seeing error messages.

If LOCKLIST is drastically under sized, you may see:

SQL0912N  The maximum number of lock requests has been reached for the
      database.

This really means you must increase LOCKLIST.

War Stories From The Real World

Frequently when I run into a locking problem, someone (not a DBA) on a conference call will suggest increasing LOCKLIST. The only locking problem that increasing LOCKLIST will solve is lock escalations. Unless lock escalations are occurring, increasing LOCKLIST will not help with deadlocks, lock timeouts, or excessive lock waits.

The only time I have increased this parameter is on build (the defualt in previous versions was far too low), in response to lock escallations, or in response to SQL0912N.

Link To Info Center

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000267.html

Related Parameters

DB CFG:  MAXLOCKS – http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000268.html

DB CFG: MAXAPPLS – http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000279.html

DB CFG: MAX_COORDAGENTS – http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0000139.html

System Monitor Element: locks_held_top – http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.mon.doc/doc/r0001291.html

Blog Entries Related to this Parameter

Locking Parameters: https://datageek.blog/2012/01/09/locking-parameters/

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home