473,236 Members | 1,348 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,236 software developers and data experts.

Lock Escalation - Share Lock

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
9 7323
"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
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
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
"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
"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
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
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: xixi | last post by:
hi, we are using db2 udb v8.1 on 64 bit windows, this message is in the db2diag.log file ADM5502W The escalation of "38200" locks on table "xxx.xxx" to lock intent "X" was successful. ...
3
by: Amy Woodward | last post by:
Does DB2 have a setting to turn off lock escalation? I've just done several hours of newsgroup googling, and have found only a couple references to such a setting, for DB2 v4.1 back in the late...
0
by: Bruce Pullen | last post by:
DB2 v7.2 (FP7 - DB2 v7.1.0.68) on AIX 5.2.0.0. We're seeing unexpected single row (then commit) insert locking behaviour. We're seeing Applications that already hold row-level W locks in...
3
by: db2group88 | last post by:
we are using db2 udb v8.1 on windows, the configure parameter for locks is locklist 1000, maxlocks 60, but somehow i still have the error message ADM5502W The escalation of "1" locks on table...
2
by: Trent | last post by:
Hello, all. I have the following production DB2 environment. DB2 8.1.4 (fp4) WG edition with 2 production databases on Windows 2003 standard edition. My first question is regard with...
9
by: kavallin | last post by:
I receives the following in the db2diag.log file many times / day : 2007-03-05-14.55.24.836553+060 E12415C457 LEVEL: Warning PID : 2785 TID : 1 PROC :...
3
by: stefan.albert | last post by:
Hi folks, we have a little discussion about lock escalation... What is better for performance: To have an escalation "early" (smaller locklist) or aviod the escalation with a big lock list? ...
1
by: clilush | last post by:
I'm getting entries in the windows event log saying "DB2 is performing lock escalation" which of course is followed by another entrie saying "The escalation of 'xxxx' locks on table 'SYSIBM ...
18
by: dunleav1 | last post by:
I have db2 9 installation and I think our application isn't handling locking optimally. I have Maxlocks(96) and locklist (104832) setup to be auto tuned. The value of these parameters in the...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.