Hi Vijay / Pierre,
Thanks for the suggestions. I will try this out and get back with the result.
Cheers
Siva
---------------------------------------- Message History
----------------------------------------
From: [EMAIL PROTECTED]@lists1.ba.best.com on 07/26/2001 10:42 AM EST
Please respond to [EMAIL PROTECTED]
DELEGATED - Sent by: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
cc:
Subject: Re: DB2EUG: DB2 lock timeout / deadlock problem
Hi Siva:
1. n times the average # of applications means that: (for example. If you
wish to support on an average 100 applications, you can design the lock
parameters to support n times 100. If n=2, you will design lock parameters
to support 200 applications connected on an average to the database)
2. The default for DLCHKTIME is 10000ms = 10 seconds. You can make this
5000ms = 5 seconds. This will check for deadlocks every 5 seconds, thus
releasing deadlocks more frequently ( every 5 seconds ) rather than doing it
every 10 seconds. But checking every 5 seconds Vs. 10 seconds will have some
impact on the performance ==> " More frequent checks, more work for db2,
more resources used!"
3. The LOCKTIMEOUT in my case is 10 seconds because I have a big LOCKLIST to
support many locks. So even if there was an application waiting in the lock
queue to secure a lock from the LOCK buffer, it would wait for only 10
seconds,
if it can't obtain the lock, it will timeout! To fix this:
.a. Check for applications holding locks for a long time
.b. issue commits in applications more often.
.c. If both .a. and .b. does not fit your system, increase the
LOCKTIMEOUT from 10 seconds to a higher value ,
step by step and see how it responds.
If you have good amount of real memory to support the database, increase the
LOCKLIST step by step till you find ZERO escalations, waits and timeouts.
DB2To summarize,
To avoid escalations ==> increase MAXLOCKS, so that you have more locks per
applications. Also increase LOCKLIST accordingly. Use the formula for this.
For starting purposes, I would give n=1 for "N TIMES THE AVERAGES".
To avoid waits & subsequent timeout for locks by applications ==> decrease
LOCKWAIT (or) fine-tune the applications that's holding the locks for a long
time (or) issue more often COMMITs inside applications. This is just to
release locks acquired by an application so that other applications can use
them.
For more information on the formula & application/lock parameters, refer to
administration guide.
Tks!
Regards,
Vijay
----- Original Message -----
From: "KG Sivaramakrishnan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 26, 2001 1:10 AM
Subject: RE: DB2EUG: DB2 lock timeout / deadlock problem
>
>
> Vijay,
>
> Thanx for your suggestion. Still we have the same timeout problem. I
have couple of queries from your suggestion.
> Today I will change the values as you suggested and test it again.
>
> 1) In the formula which you have given has ( n times the average). Can
you explain this ? What value should be
> given here ?
> 2) he inerval for checking deadlock is 5000 ms. Currently we have 10000
ms. Still we get deadlock/timeout.
> Will the frequency of deadlock/timeout increase if I change it to
5000 ms. ? Is it related to the other parameter
> values like locklist, maxlocks etc ?
> 3) Lock timeout (sec) is 10. Currently we have set it to 180 seconds.
I have the same doubt as mentioned in
> point 2.
>
> 4) Any other suggestion ?????
>
> Thanks in Advance.
> Siva
>
>
> ---------------------------------------- Message
History ----------------------------------------
>
>
> From: [EMAIL PROTECTED]@lists1.ba.best.com on 07/18/2001 08:49 AM MST
>
> Please respond to [EMAIL PROTECTED]
>
> DELEGATED - Sent by: [EMAIL PROTECTED]
>
>
> To: [EMAIL PROTECTED]
> cc:
> Subject: RE: DB2EUG: DB2 lock timeout / deadlock problem
>
>
> Hi:
>
> Try:
> Max storage for lock list (4KB) (LOCKLIST) = 1000
> Interval for checking deadlock (ms) (DLCHKTIME) = 5000
> Percent. of lock lists per appl. (%) (MAXLOCKS) = 16
> Lock timeout (sec) (LOCKTIMEOUT) = 10
>
> This formula can help you relate all the lock
> parameters & no. of applications that can be supported
> with the list of parameters at threshold.
> maxlocks = 100 *
> (n locks per application
> * 32 bytes per lock
> * n times the average ) / (locklist *
> 4096 bytes)
>
> Also do a REBIND PACKAGE & applications to update
> access plans.
>
> Then run snapshot monitor on tables and locks to see
> how it performs. After a few iterations of changing
> the above set of parameters you should be able to get
> rid of waits, escalations and deadlocks.
>
> Increase LOCKLIST if you have waits. Increase MAXLOCKS
> if you see escalations (Also will need to increase
> LOCKLIST accordingly). Try keeping the Timeout small
> if you have good amount of memory (4 Kb Pages)
> allocated for LOCKLIST. Aviod LOCKTIMEOUTS by
> increasing your LOCKLIST.
>
> Also, MAXLOCKS indicates the soft-limit for the % of
> LOCKLIST. Once this limit is reached, the application
> will be unable to obtain locks resulting in lock
> ESCALATION. With respect to the above suggested
> values, when 16% of 1000 4KB pages is reached,
> ESCALATION will occur. These values might not be a
> perfect fit, so you many change them to suit your
> system.
>
> Tks!
>
> Vijay
>
>
> --- Steve Levy <[EMAIL PROTECTED]> wrote:
> > With a MAXLOCKS parameter of 100 only 100 rows may
> > be locked at one time.
> > You cursors (selects statements) are hitting this
> > wall then escalating to
> > table locks on your "3 main tables." First LOCKLIST
> > is a %-age so 100 is
> > not a great choice unless you really WANT one use to
> > be able to "use up" all
> > of your databases locks (only for REAL heavy
> > batch-based apps would this be
> > the case). If you increase your MAXLOCKS you will
> > find you are able to
> > support more users. Try MAXLOCKS 600 and LOCKLIST
> > 20. This will give each user 20% of 600 possible
> > locks or everyone will get
> > about 100 rows. If these numbers seem too high
> > decrease as you need to.
> > Good Luck
> >
> > ----Original Message-----
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf
> > Of KG
> > Sivaramakrishnan
> > Sent: Wednesday, July 18, 2001 7:17 AM
> > To: [EMAIL PROTECTED]
> > Subject: DB2EUG: DB2 lock timeout / deadlock problem
> >
> >
> >
> > Hi all,
> >
> > We have a serious problem( deladlock / lock timeout
> > error) in our
> > application when 6+ users are connected to the
> > database and working on
> > different modules. We have 3 main tables which
> > are accessed accross all
> > modules and the same gets read/updated/inserted
> > using cursors / normal SQL
> > statements. We have checked the commit points in
> > the code and the it seems
> > to be fine. We also tried with changing the
> > isolation level from RR to
> > CS/CR and increase the locklist size. Please find
> > the following details
> > about H/W, Software and the DB parameters.
> >
> > H/W P3 with 128 MB RAM
> > Software UDB 5.2 on NT with FP 2, MF Cobol
> > Database Parameters:
> > Isolation level CS
> > maxappls 60
> > avg_appls 1
> > maxfilop 64
> > dlchktime 10000 milliseconds
> > Locktimeout 180 Seconds
> > MaxLocks 100
> > Locklist 100
> >
> > All other DB parameters and DBM parameters, we have
> > retained the default
> > value. Now 50+ users wanted to work on this
> > application. Kindly suggest
> > some solution to overcome this problem since it is
> > very urgent.
> >
> > Please find the following snapshot taken during the
> > problem.
> >
> >
> >
> > Database Lock Snapshot
> >
> > Database name = CAWR1
> > Database path =
> > E:\DB2\NODE0000\SQL00006\
> > Input database alias = CAWR1
> > Locks held = 142
> > Applications currently connected = 10
> > Agents currently waiting on locks = 4
> > Snapshot timestamp =
> > 07-12-2001 12:32:33.351065
> > Application handle = 45
> > Application ID =
> > 0AAD72B5.1706.010712064120
> > Sequence number = 0001
> > Application name =
> > CA61.EXE
> > Authorization ID = USERID
> > Application status =
> > Lock-wait
> > Status change time =
> > 07-12-2001 12:32:33.246818
> > Application code page = 1252
> > Locks held = 43
> > Total wait time (ms) = 0
> >
> >
> >
> > Subsection waiting for lock = 0
> > ID of agent holding lock = 56
> > Application ID holding lock =
> > 0AAD727D.2305.010712065417
> > Lock object type = Row
> > Lock mode =
> > Exclusive Lock (X)
> > Name of tablespace holding lock = TSTEMP
> > Schema of table holding lock = CP
> > Name of table holding lock = COLL
> > Lock wait start timestamp =
> > 07-12-2001 12:32:33.246823
> > Lock is a result of escalation = NO
> >
> >
> > Object Name Object Type Tablespace Name
> > Table Schema Table
> > Name Mode Status Lock Escalation
> > ----------- --------------- --------------------
> > -------------------- ------
> > -------------- ---- ---------- ---------------
> >
> > 1570050 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 973825 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569819 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 973058 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569817 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 973056 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569814 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 1569809 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 972800 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569807 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 972546 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569813 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 1569804 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 972291 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569812 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 1569801 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 972288 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569811 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 1569796 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 971779 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569810 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 1569792 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 971523 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 972801 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569583 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 971520 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 1569576 Row TSCAMS CP
> > COLL
> > X Granted NO
> > 971009 Row TSCAMS CP
> > DEP_DESCRIPTION X Granted NO
> > 260 Table TSCAMS CP
> > DEP_DESCRIPTION IX Granted NO
> > 263 Table TSCAMS CP
> > COLL
> > IX Granted NO
> > 2126082 Row TSCAMS CP
> > FOL_DESCRIPTION X Granted NO
> >
> === message truncated ===
>
>
> __________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail
> http://personal.mail.yahoo.com/
>
> =====
> To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
> For other info (and scripts), see
http://people.mn.mediaone.net/scottrmcleod
>
>
>
>
> --
>
> This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.
>
>
>
> =====
> To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
> For other info (and scripts), see
http://people.mn.mediaone.net/scottrmcleod
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
--
This e-mail may contain confidential and/or privileged information. If you are not the
intended recipient (or have received this e-mail in error) please notify the sender
immediately and destroy this e-mail. Any unauthorised copying, disclosure or
distribution of the material in this e-mail is strictly forbidden.
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
