468,242 Members | 1,537 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Lock Escalation

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.070731202903
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:3
MESSAGE : ADM5502W The escalation of "766457" locks on table "PHAWLS2
.CARRIER_RUN_JOB" 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.070801031920
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, 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.070801031920
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, 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
Aug 1 '07 #1
0 2696

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by xixi | last post: by
9 posts views Thread by Jane | last post: by
reply views Thread by Bruce Pullen | last post: by
3 posts views Thread by db2group88 | last post: by
9 posts views Thread by kavallin | last post: by
3 posts views Thread by stefan.albert | last post: by
1 post views Thread by clilush | last post: by
18 posts views Thread by dunleav1 | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.