Lock Escalation - Share Lock

[Jane]

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

[Mark 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.

[Pierre Saint-Jacques]

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.

[PM \(pm3iinc-nospam\) CGO]

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

[Mark 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.

[Mark 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.

[Mark Yudkin]

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

[Jane]

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

[Mark 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.

[Mark Yudkin]

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