By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,230 Members | 2,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,230 IT Pros & Developers. It's quick & easy.

Lock Escalation - Share Lock

P: n/a
Our db2diag.log is full of messages like this:

2004-05-31-17.15.10.383766 Instance:tminst1 Node:000
PID:394948(db2agent (TMDB1) 0) TID:1 Appid:GA140956.EF26.03A4B1202647
data management sqldEscalateLocks Probe:3 Database:TMDB1

ADM5502W The escalation of "4759" locks on table "I2TM .SHPM_T" to lock
intent "S" was successful.

The message is always about Shared locks. What can I do to eliminate these errors?

Do we need to changes these parameters?
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1

Thanks,
-Jane
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"Jane" <ja**********@i2.com> wrote in message
news:75**************************@posting.google.c om...
Our db2diag.log is full of messages like this:

2004-05-31-17.15.10.383766 Instance:tminst1 Node:000
PID:394948(db2agent (TMDB1) 0) TID:1 Appid:GA140956.EF26.03A4B1202647
data management sqldEscalateLocks Probe:3 Database:TMDB1

ADM5502W The escalation of "4759" locks on table "I2TM .SHPM_T" to lock intent "S" was successful.

The message is always about Shared locks. What can I do to eliminate these errors?
Do we need to changes these parameters?
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1

Thanks,
-Jane


That is a warning message, not an error message. It indicates that row
locking has been escalated to table locking. Multiple S (share) table locks
can exist without any contention among different applications, unless
another application needs to update the table at the same time.

You can discourage lock escalation (from row to table) by increasing the
LOCKLIST memory size (DB2 will start escalation when the LOCKLIST is full
with other locks) and increasing MAXLOCKS (the percent of the LOCKLIST that
any one application can use before escalation occurs). If you increase
LOCKLIST memory, you should also increase DBHEAP by the same amount
(LOCKLIST memory comes out of DBHEAP).

Lock escalation to table level can improve performance (because DB2 does not
need to spend time locking and unlocking each row), but can create
contention with other applications if they need to do updates. Lock
escalation to table level is usually a benefit in an ad-hoc query
environment (so long as loads are done when others are not querying). In an
OLTP environment, lock escalation can be a real problem.

LOCKTIMEOUT -1 means that no application waiting for a lock will ever time
out with a -911 reason code 68, it will wait indefinitely. Most people set
this to somewhere between 10 and 60 seconds.

DLCHKTIME indicates how often DB2 checks for deadlocks, which is a slightly
different occurrence than a locktimeout. That looks OK.

Nov 12 '05 #2

P: n/a
Mark, are you sure locklist comes out of dbheap?
I think it is part of Global Shared Memory and thus "fights" for same
segments as dbheap, buffer pools, package and catalog caches.
BTW, if those caches run out of defined memory allocation, they will (in
V8) "steal" their needed pages from each other, locklist and/or dbheap.
HTH, Pierre.

Mark A wrote:
"Jane" <ja**********@i2.com> wrote in message
news:75**************************@posting.google.c om...
Our db2diag.log is full of messages like this:

2004-05-31-17.15.10.383766 Instance:tminst1 Node:000
PID:394948(db2agent (TMDB1) 0) TID:1 Appid:GA140956.EF26.03A4B1202647
data management sqldEscalateLocks Probe:3 Database:TMDB1

ADM5502W The escalation of "4759" locks on table "I2TM .SHPM_T" to


lock
intent "S" was successful.

The message is always about Shared locks. What can I do to eliminate these


errors?
Do we need to changes these parameters?
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1

Thanks,
-Jane

That is a warning message, not an error message. It indicates that row
locking has been escalated to table locking. Multiple S (share) table locks
can exist without any contention among different applications, unless
another application needs to update the table at the same time.

You can discourage lock escalation (from row to table) by increasing the
LOCKLIST memory size (DB2 will start escalation when the LOCKLIST is full
with other locks) and increasing MAXLOCKS (the percent of the LOCKLIST that
any one application can use before escalation occurs). If you increase
LOCKLIST memory, you should also increase DBHEAP by the same amount
(LOCKLIST memory comes out of DBHEAP).

Lock escalation to table level can improve performance (because DB2 does not
need to spend time locking and unlocking each row), but can create
contention with other applications if they need to do updates. Lock
escalation to table level is usually a benefit in an ad-hoc query
environment (so long as loads are done when others are not querying). In an
OLTP environment, lock escalation can be a real problem.

LOCKTIMEOUT -1 means that no application waiting for a lock will ever time
out with a -911 reason code 68, it will wait indefinitely. Most people set
this to somewhere between 10 and 60 seconds.

DLCHKTIME indicates how often DB2 checks for deadlocks, which is a slightly
different occurrence than a locktimeout. That looks OK.



--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #3

P: n/a
Instead of pumping up the locklist, you can also use the LOCK statement at
the table level or change the
lock level table attribute.

Of course, the choice depends on the situation.

PM
Nov 12 '05 #4

P: n/a
"Pierre Saint-Jacques" <se*****@attglobal.net> wrote in message
news:40**************@attglobal.net...
Mark, are you sure locklist comes out of dbheap?
I think it is part of Global Shared Memory and thus "fights" for same
segments as dbheap, buffer pools, package and catalog caches.
BTW, if those caches run out of defined memory allocation, they will (in
V8) "steal" their needed pages from each other, locklist and/or dbheap.
HTH, Pierre.

Pierre you are correct and I was wrong. Log buffers and catalog cache comes
out of the dbheap.
Nov 12 '05 #5

P: n/a
"PM (pm3iinc-nospam) CGO" <PM (pm3iinc-nospam)@cgocable.ca> wrote in message
news:lw*****************@charlie.risq.qc.ca...
Instead of pumping up the locklist, you can also use the LOCK statement at
the table level or change the
lock level table attribute.

Of course, the choice depends on the situation.

PM

Increasing the locklist discourages DB2 from lock escalation from row to
table level.

You force escalation with SQL lock table, but I don't know any way to
prevent it with SQL of bind parameters.
Nov 12 '05 #6

P: n/a
The default locklist is minuscule for data warehousing applications.
Increase it
"Jane" <ja**********@i2.com> wrote in message
news:75**************************@posting.google.c om...
Our db2diag.log is full of messages like this:

2004-05-31-17.15.10.383766 Instance:tminst1 Node:000
PID:394948(db2agent (TMDB1) 0) TID:1 Appid:GA140956.EF26.03A4B1202647
data management sqldEscalateLocks Probe:3 Database:TMDB1

ADM5502W The escalation of "4759" locks on table "I2TM .SHPM_T" to lock intent "S" was successful.

The message is always about Shared locks. What can I do to eliminate these errors?
Do we need to changes these parameters?
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1

Thanks,
-Jane

Nov 12 '05 #7

P: n/a
The Lock list is currently set at the following level:

Max storage for lock list (4KB) (LOCKLIST) = 650

Should I increase it even more? The database is small (1 gig) but
there are large number of 'select' statements.

Thanks for every one's feedback.
-Jane
Nov 12 '05 #8

P: n/a
"Jane" <ja**********@i2.com> wrote in message
news:75**************************@posting.google.c om...
The Lock list is currently set at the following level:

Max storage for lock list (4KB) (LOCKLIST) = 650

Should I increase it even more? The database is small (1 gig) but
there are large number of 'select' statements.

Thanks for every one's feedback.
-Jane


If you want to discourage lock escalation from row to table level, then
increase the locklist (which is currently using about 2MB of memory).
Increase it by a factor of 5-10 times if you have enough real memory.

Lock escalation can cause lock contention problems when one application is
updating and another application is accessing the same table (reading or
updating). This includes utilities. To minimize lock escalation, increase
the size of the locklist and increase maxlocks (to about 60%).

However, if your environment is query only (except during off hours when
utilities are single threaded and no queries are running), then lock
escalation to table level improves performance since DB2 does not have to
spend time locking and unlocking individual rows (the whole table is
locked).

To repeat: multiple S (share) locks at the table level can coexist without
any contention problem, and the lock escalation improves performance.

When lock escalation occurs, it may be logged in the db2dai.log file, but it
is not an error. It is an information message. You may be able to change the
message logging level to prevent this message from appearing in the log.
Nov 12 '05 #9

P: n/a
We run with 30000. 650 is far too small for data warehouse type processing.

"Jane" <ja**********@i2.com> wrote in message
news:75**************************@posting.google.c om...
The Lock list is currently set at the following level:

Max storage for lock list (4KB) (LOCKLIST) = 650

Should I increase it even more? The database is small (1 gig) but
there are large number of 'select' statements.

Thanks for every one's feedback.
-Jane

Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.