When LOCKLIST is managed by STMM (LOCKLIST = AUTOMATIC,
SELF_TUNED_MEMORY = ON), it is normal for the locklist to
increase beyond its configured size and grow into overflow
memory. This is indicated by the following db2diag.log entry:
FUNCTION: DB2 UDB, lock manager, sqlpRefillAgentLRBCache,
probe:1000
DATA #1 : <preformatted>
Locklist has synchronously grown into overflow memory to avoid
lock escalation.
Previous locklist size: <value>
Current locklist size: <value>
While the locklist is in this overflow state, db2agents try to
release locklist overflow when they release locks on a commit.
A latch controlling these releases may become hotly contended,
causing commits to take longer than normal to process. Normally
the STMM tuner resolves the overflow state by increasing the
locklist configuration, and the contention is quickly resolved.
In extreme cases, especially when the STMM tuner is delayed or
memory is constrained, the locklist increase may not occur,
latch contention may build, and the database may appear to be
hung for a period of time.
Issuing "db2pd -stack all" may show a large number of db2agents
in function stacks similar to the following:
sqloXlatchConflict
sqlpCheckAndDecreaseLocklistOverflowUsage
sqlplrm
sqlpxcm1
sqlrrcom_dps
sqlrrcom
sqlrr_commit
db2pd -latches will show a large number of waiters on the
"resizeLockListLatch"
Latches:
Address Holder Waiter Filename
LOC LatchType HoldCount
0x0700000032550110 420718 92524 sqlplit.C
2418 SQLO_LT_SQLP_LCB__resizeLocklistLatch 1
0x0700000032550110 420718 117196 sqlplit.C
2418 SQLO_LT_SQLP_LCB__resizeLocklistLatch 1
0x0700000032550110 420718 159859 sqlplit.C
2418 SQLO_LT_SQLP_LCB__resizeLocklistLatch 1
...
If memory is overly constrained, errors such as the following
will appear in the STMM logs (under the stmmlog directory in the
DIAGPATH).
FUNCTION: DB2 UDB, Self tuning memory manager,
stmmEnforceMinSizeConstraints, probe:2364
MESSAGE : Unable to find donor to satisfy minSize constraint
and LOCKLIST will repeatedly fail to reach its minimum size
requirement :
FUNCTION: DB2 UDB, Self tuning memory manager,
stmmLogRecordBeforeResizes, probe:590
...
Type: LOCKLIST
...
Current Size: 4096
Minimum Size: 6112
In this case, configuration issues should be addressed so that
STMM is able to operate normally. |
