DB2 - Problembeschreibung
Problem IT05593 | Status: Geschlossen |
HIGH USER CPU OR SYSTEM HANG MAY BE OBSERVED WHEN LARGE AMOUNT OF LOCK RESOURCES BEING USED BY MANY CONCURRENT TRANSACTIONS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
When there are many concurrent transactions which are requesting a large amount of lock resources at the same time, you may observe high USER CPU or the server appeared to be hanging or be performed very slowly. When this issue happens, you may see a lot of agents looping at sqlpRefillAgentLRBCache function at the top of their stacks while holding the latch modifyGroupListLatch as below example: 0x0900000005072744 sqlpRefillAgentLRBCache__FCP9sqeBsuEduCP9SQLP_DBCBCP8SQLP_LCBCP9 SQLP_LTRNPUlCb + 0xB60 0x0900000004FACC5C sqlplrq__FP9sqeBsuEduP14SQLP_LOCK_INFO + 0x460 0x0900000004F678A4 @161@sqliScanLeaf2__FP7SQLI_CBP11SQLI_SFGLOB + 0x5CC 0x0900000004F69844 sqlifnxt__FP7SQLI_CBP11SQLI_SFGLOB + 0x26C 0x0900000004F62F80 sqlirdk__FP8sqeAgentP10SQLI_IXPCRP9SQLD_IXCBUlP8SQLD_KEYP12SQLI_ KEYDATAP9sqli_scanPi + 0x2EC 0x0900000004F64AB0 sqldIndexFetch__FP8SQLD_CCBP8SQLZ_RID + 0x67C 0x0900000004F51EA4 sqldRowFetch__FP8sqeAgentP8SQLD_CCBUlT3PP10SQLD_VALUEP8SQLZ_RIDT 3P12SQLD_ID_LISTP9SQLP_LSN8 + 0x133C 0x0900000004F4D338 sqlriFetch__FP8sqlrr_cbP9sqlri_taol + 0x2D4 0x0900000004F4C8EC sqlriNljnNonPiped__FP8sqlrr_cb + 0x328 0x0900000004F49A58 sqlrihsjn__FP8sqlrr_cb + 0x59C 0x0900000004F4DDC8 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0x24 0x0900000004D67868 sqlrr_process_fetch_request__FP14db2UCinterface + 0xF60 0x090000000579F9F8 sqlrr_open__FP14db2UCinterfaceP15db2UCCursorInfo + 0x17F0 <LatchInformation> Holding Latch type: (SQLO_LT_SQLP_LCB__modifyGroupListLatch) - Address: (0xa00060000db0e68), Line: 842, File: sqlplrq.C HoldCount: 1 </LatchInformation> The issue is more likely to occurs at an environment where the db parameters LOCKLIST and MAXLOCKS are configured with very high values or are set to AUTOMATIC but has grown to a very large size in STMM case. For example, below are the output showing the LOCKLIST is set to AUTOMATIC in a STMM environment, but the size has grown to almost 10G bytes. LOCKLIST (4KB) AUTOMATIC(2527424) AUTOMATIC(2527424) | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to the latest fix pack. * **************************************************************** | |
Local-Fix: | |
-Reduce the requirement of locking resources and commit the transactions as soon as possible. -Use a lower isolation level such as CS/UR if possible. -Set LOCKLIST to a fixed size but with a smaller value so lock escalation can happen earlier. | |
Lösung | |
Problem is first fixed in DB2 UDB version 9.7 fix pack 11 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 16.11.2014 07.10.2015 07.10.2015 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP11 | |
Problem behoben lt. FixList in der Version | |
9.7.0.11 |