Hi All,
I am using DB2 V 8.1 on linux. The "Memory Visualizer" alarmed 99.65% of "Lock Manager Heap" is in use. And snapshot shows:

After I doubled locklist cfg parm, Memory Visualizer still showed almost 100% and snapshot showed locklist-in-use the same.
My question is: Do I need to care about this alarm? How can I control it, reset the alarm? Thank you in advance.

Yes, You should absolutely increase the size of your locklist.

According to the normal practice, locklist should be increased when hwm is > 50% of the maximum allowed.

Implementing FREQUENT COMMITs in your batch programs and long running transactions, should bring down the usage of this heap.

What's the value of your MAXLOCKS?

Sathyaram

Thanks to ereryone.
What I don't understandard is after I doubled and doubled again locklist, snapshot showed no increase in locklist-in-use, but the use of Lock Manager Heap was quadrupled - still 100% in use. What's the major part of Lock Manger Heap if it's not locklist? Why the use of Lock Manager Heap quadrupled while locklist in use remained the same?

Maxlocks =40%

Although no one answered my question, I still want to share my story here. It seems the velues in "locklist" and "Lock Manager Heap" are inconsintent. If I double locklist cfg parm, the snaphot shows me "Lock Manager Heap" gets doubled, but locklist in use remains the same. Actually "Lock Manager Heap" is always about 100% in use no matter what values I set for locklist.

The above snapshot was taken after I restart my DBM. There was only on lock held in the entire DB, but "Lock Manager Heap" was still 100%.
Am I misunderstanding something, or a bug in DB2? What is does your snapshot show?

What is the value of your locklist in the db config?

My understanding is lockmgrheap=locklist*pagesize + some overhead; it is typically a static value and that much memory is allocated when database is activated;as locks are needed, locks are created; each lock uses some 40 or so bytes; One application cannot use more than MAXLOCKS percentage of locks availble. If you issue "select somerecord for update where somecondition" and then check 'Lock list memory in use' you should see different values depending on how many records you selected; If MAXLOCKS percentage is exceeded, db2 will escalate the lock; instead of using many update locks in our example, db2 will replace many update locks with one table lock.

The db cfg locklist = 80,000. It's apparently used up by the Lock Manager. This value was 20,000 before I increased it two days ago. Lock Manager always use up all my configured locklist value. But, there was only one or few locks.

80.000 is an outrageously large number for locklist. That is the number of 4K pages allocated to hold locks.

On 32-bit platforms in V8.2, each lock requires 40 or 80 bytes of the lock list, depending on whether other locks are held on the object:

- 80 bytes are required to hold a lock on an object that has no other locks held on it
- 40 bytes are required to record a lock on an object that has an existing lock held on it.

If you have an OLTP system, it is hard to imagine that you need that much storage for locks at any given time since transactions are short and commit frequently.

If you have Data Warehouse application with lots of table scans, you would usually be much better off with a small locklist and letting row locks escalate to table locks, since multiple share (select) locks can co-exist without a problem. It is fairly expensive for DB2 to lock and unlock each row when doing a scan of a large table.

I checked one of my Linux systems that has 4096 4K pages for the locklist, and since it is Friday night, there is no one using the system but me. Here is what my snapshot shows:


Locks held currently = 0

Memory Pool Type = Lock Manager Heap
Current size (bytes) = 17383424
High water mark (bytes) = 17383424
Configured size (bytes) = 17383424

7383424 bytes is slightly more (4244 4K Pages) than the 4096 4K pages allocated for the locklist (as mentioned in a post above, the heap is the locklist plus some memory needed for overhead). So it appears that the memory heap is fully allocated even if the entire locklist is empty.

So I would conclude that any kind of monitoring software that warns you about the Lock Manager Heap being at or near 100% is causing a false alarm, since it should always be at 100% unless you don't have enough real memory.

That might be good advice for an OLTP application, but poor advice for a Data Warehouse application.

Usually "normal practice" is for people who don't understand how a product really works, and are looking for a cookbook approach. Sometimes the cookbook approach (normal practice) works OK, but since there a often exceptions to the "rule" then it can also cause problems.

Of course, as we have determined, the lock memory heap usuage is not the same thing as the percent of locklist used to hold locks, but that is another subject.

I fully agree with Marcus on this. Even if there is very few applications are active and there may be only a single lock existing, then too you can see the "LOCK Manager Heap" has attained the High Water Mark, may be 50 MB or whatever size configured for the same.
So the best practice to avoid memory issues in locking is to:
1> Using COMMITCOUNT is your import statement. Ideally 1500 rows or less.
2> In case of bulk delete of any table, use multimple UOW using WHERE clause with frequent COMMIT statement.
3> If you are sure that the number of locks attained will be more and the memory required will be significant then increase the LOCKLIST parameter to a reasonable value, take care of the MAXLOCKS parameter too. Ideally it should be between 20% to 40%. Depending on application it can vary too.

Thanks,
Jay

This is a very good point I complete agree with! Your comments also assure me to ignore this kind of alarm.
The reason that my locklist is that huge is I keep increasing it when I see the alarm above.

A lot Thanks to everyone for helping me out.

Gary