Lock Escalation

[cburnett]

Have a peculiar problem regarding lock escalation. Originally lock escalation was occurring because of an application changing most of the rows in a large table. After setting locksize to table to overcome this, still seeing periodic lock escalations (every hour or so) but only of abour 20 rows and for one application at a time. Given a 100MB lock list, why is escalation kicking in and only escalation about 2K of locks? MAXLOCKS would also suggest that lock escalation woudl only be triggered if an application was using more than 60MB of locks (at least 500K locks). I would have expected at least one signficant escalation to be present.

This is having serious repercussions on other applications (escalating single row locks to exclusive table locks).

Thx for any help in this matter.

N.B. There are other problems related to insufficient memory and agents but these appear to be symptoms of this same issue - table locks => less concurrency => more agents waiting => more memory used => out of memory and out of agent conditions.

Initial problem:
2007-08-01-08.02.59.704239 +600 E18567A471 LEVEL: Warning
PID : 1372284 TID : 1 PROC : db2agent (LDSPRD01)
INSTANCE: db2inst1 NODE : 000 DB : LDSPRD01
APPHDL : 0-1194 APPID: O7655004.D011.0 70731202903
FUNCTION: DB2 UDB, data management, sqldEscalateLoc ks, probe:3
MESSAGE : ADM5502W The escalation of "766457" locks on table "PHAWLS2
.CARRIER_RUN_JO B" to lock intent "X" was successful.

Current bevaviour:
2007-08-01-13.22.10.902906 +600 E191147A457 LEVEL: Warning
PID : 299040 TID : 1 PROC : db2agntp (LDSPRD01)
INSTANCE: db2inst1 NODE : 000 DB : LDSPRD01
APPHDL : 0-858 APPID: O7655004.O10D.0 70801031920
FUNCTION: DB2 UDB, data management, sqldEscalateLoc ks, probe:3
MESSAGE : ADM5502W The escalation of "1" locks on table "PHAWLS2 .BRANCH" to
lock intent "S" was successful.

2007-08-01-13.22.11.215434 +600 E191605A464 LEVEL: Warning
PID : 299040 TID : 1 PROC : db2agntp (LDSPRD01)
INSTANCE: db2inst1 NODE : 000 DB : LDSPRD01
APPHDL : 0-858 APPID: O7655004.O10D.0 70801031920
FUNCTION: DB2 UDB, data management, sqldEscalateLoc ks, probe:3
MESSAGE : ADM5502W The escalation of "2" locks on table "PHAWLS2
.PAYABLE_TRANS" to lock intent "S" was successful.

Configuration information:
[/home/db2inst1] : db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL08023"
with level identifier "03040106".
Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak
"10".

[/home/db2inst1] : db2 get db cfg

Database Configuration for Database
...
Max storage for lock list (4KB) (LOCKLIST) = 24000
...
Percent. of lock lists per application (MAXLOCKS) = 60