Parameter Wednesday – DB CFG – LOCKLIST

DB2 Version This Was Written For


Parameter Name


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)


$ 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"

-------------------------------- ---------------- ----------- ---------------- -------------------- ----------------
locklist                         4096             AUTOMATIC   4096             AUTOMATIC            BIGINT

  1 record(s) selected.


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.


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.




4 – 134217728

Recycle Required To Take Effect?

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


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"

------------------------------ --------------------
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

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

Related Parameters




System Monitor Element: locks_held_top –

Blog Entries Related to this Parameter

Locking Parameters:

You may also like...

2 Responses

  1. rajesh pandhare says:

    Hi Ember,
    Can you please help me to get more info regarding “locks_held_top” monitor element, How can we get that value if I want to check it for my DB.

Leave a Reply

Your email address will not be published. Required fields are marked *